GIS and spatial analysis
UMD Geog473 GIS and Spatial Analysis Instructor: Dr. Naijun Zhou Homework 1
This instruction is solely for GEOG473, do not distribute the material outside the class without written permission from the instructor Dr. Naijun Zhou. -1-
Due Section 0101: Friday, February 17, 2017, 12pm, ELMS Section 0102: Friday, February 17, 2017, 10am, ELMS A word document must be submitted on ELMS. Homework 1 Geodatabases Question 1. (7 points) Suppose you design a geodatabase for a county to store the following information.
Houses are georeferenced with X and Y coordinates (State Plane Coordinate System, unit: meters). Each house has an address recorded with these address elements: house number, house street name, street type, city name, state and zipcode (e.g., 123 Baltimore Ave, College Park, MD 20742). For each house, we also know the household size (i.e., the number of residents) and annual household income. The other information is land parcel, described by a unique ID (a character followed by 6 digits, for example, A123456). Each land parcel also has its land value (US$), property value (US $) and the total value (the sum of land and property value). A house must be within a single land parcel, and there may be multiple houses in a parcel. There are also POIs, each described by its business name, the address (recorded with the same address elements as houses), and the type of service (e.g., bank, gas station, grocery store, theatre, etc.). Note that one address (house or POI) can be geographically within one or multiple parcels. Road network is provided as geographical data, and the network consists of links (lines) which can have one or several road segments (as lines). Each segment is a road segment and has these information: the geometry (a sequence of (X,Y) coordinates), the name (i.e., the road name, e.g., Smith), the type (i.e., road type, e.g., road, avenue), speed limit, direction (one way or two way). Each road segment also includes from and to house number, i.e., multiple houses are geographically located on a same segment, and from indicates the first house number on the segment, and to indicates the last house number. Here we assume one segment includes all house numbers (even and odd) within the from and to range. All the addresses are unique, i.e., no houses and POIs have the same address. Finally, the county boundary needs to be stored together with the name of the county. 1) Design the county geodatabase with E-R diagram. All elements of E-R diagram should be included. Your database design will be evaluated based on how “good” the design is; for example, should avoid or minimize duplicate storage of the same information, should make database queries efficient, etc. If not specified, you may name the diagram elements by yourself. And, your design should be able to answer queries in sub-questions 3), 4) and 5). (2 points) 2) Based on your design in 1), construct an Object-Relational geodatabase, and give the schema of each relation. If not specified in the question, you may decide the relational database elements based upon your E-R diagram, such as attribute data types. Geometries (points, lines, polygons) shall be defined and stored as objects using OR database model. If you have difficulty in completing sub-question 1), you may decide the schema based on your best knowledge of this state park database, and the schema will be needed in sub-question 3), 4) and 5). (2 points) 3) Based on the schemas you defined in sub-question 2), write an SQL statement to find houses that have the same street name and street type (i.e., they are on the same road) of Bank of America and the household income is lower than $45,000. Return the information of house income and household size, and the range of house number (from, to) on the same road. (1 point).
UMD Geog473 GIS and Spatial Analysis Instructor: Dr. Naijun Zhou Homework 1
This instruction is solely for GEOG473, do not distribute the material outside the class without written permission from the instructor Dr. Naijun Zhou. -2-
4) Based on the schemas you defined in sub-question 2), can you find the parcel number and the total parcel value where the house “123 Oak Street, College Park, MD 20742” resides? (1 point) 5) Given an additional map of public schools, which is a point map with only one non-spatial attribute of the school name. Using (geo)database method that you learn from this course, explain: for each school how to identify the land parcel that the school geographically falls in? (1 point)
Question 2. (1 point) 1) Use English to describe the meaning of the following SQL statement in Lab 1.2 Task 3: (0.5 point) select c.population from censustract c, populationcensustract p where p.geodesc = c.geodesc AND p.KINDERGART > 100 2) Given the tables in Lab 1.2, write an SQL statement to find census tracts that have less than 10% population with grad & college degree. Note: you need to modify the data with ArcGIS. (0.5 point)
Question 3. (1 point) Use ArcGIS and available data to make a map showing the total number schools for each census tract. The map should display the tracts and symbolize the total number of schools. You should 1) describe the steps to get the result; and 2) attach a clear map.
Question 4. (1 point) Use ArcGIS and available data to make a map showing the tract that each school belongs to. The map should display schools and label the schools with their tract. You should 1) describe the steps to get the result; and 2) attach a clear map.
