Due Date: Mar 14, 2017
- In this exercise you’ll be working with the gep664 database and the sample data from Chapter 1 in PostGIS in Action.
- This exercise is worth 6 points. Each question is worth 1 point each.
- Be sure to follow the proper conventions for SQL statements and the guidelines for submitting assignments, as posted on the course website. Failure to do so will result in point deductions.
The first part of this assignment echoes what we did in class in the lecture and covers material from PostGIS in Action Chapter 2 and the Boundless PostGIS tutorial (parts 9 and 10).
The second part of this assignment is derived from Chapter 1 in PostGIS in Action. It assumes that you have read the chapter and did all of the exercises using the sample data provided with the book. A link for downloading this data is available on the course website on the Data & Software page; it’s a ZIP file that contains subfolders for every chapter in the book.
For the following, provide: the SQL statement (questions 1-6) and the record count (questions 4-6) that is automatically returned by pgAdmin or psql.
Part I – Geometry Basics
For this exercise, you’ll be working in the course database gep664 in the nyc schema.
For the questions in this part, all of the geometry should be defined using SRID 2263, which is the local state plane zone for NYC (NAD 83 NY State Plane Long Island (ft-us)).
1. Create a point feature to represent the city’s primary train stations. Create an empty table called nyc.train_stations with 3 columns: tid integer, name text, and geom geometry(point,2263). Write an INSERT statement where you insert the VALUES for id, name, and point geometry into the table. Use the manual method for constructing geometry as outlined in section 9.1 in the Boundless tutorial. In your answer, just provide the final statement (the INSERT).
2,Grand Central Station,990607,213615
2. Create a line feature to represent the Times Square Shuttle subway. Create an empty table called nyc.subway_shuttle with 3 columns: sid varchar(1), name text, and geom geometry(linestring,2263). Look in the subway stations table and find the stops for Grand Central (stop_id 901) and Times Square (stop_id 902). Return the geometry from these stations as text so you can see the coordinates (they’re in NY State Plane). Using these coordinates, write an INSERT statement where you insert: an id (S), name (Times Square Shuttle) and linestring geometry into the nyc.subway_shuttle table. Use the manual method for constructing geometry as outlined in section 9.1 in the Boundless tutorial. In your answer, just provide the final statement (the INSERT).
3. Create point features for the NJ PATH stations in Manhattan. Create an empty table called nyc.path_stations with 5 columns: sid integer, name text, xcoord numeric(6), ycoord numeric(6), and geom geometry(point,2263). Write an INSERT statement to insert the data into the table. For this question, do NOT use the manual geometry methods you used above; create the geometry using the ST_Point function. In your answer provide the INSERT statement and the statement used for creating the geometry.
9,World Trade Center,981276,198593
Part II – PostGIS in Action Chapter 1
For this part, you’ll work with the sample data from Chapter 1 of PostGIS in Action. This part assumes that you read and completed the chapter’s exercises. Your answer must include the SQL statement AND the row count.
4. Count all of the restaurants that are within 1/2 mile of a highway within the states of NY, NJ, and CT. Group by franchise, so the result shows the total count of each restaurant within all three states (i.e. do not group by state).
5. Return the coordinates in plain text of all Pizza Hut restaurants in the restaurants table.
6. Calculate and sum the lengths of all the principal highways in miles within the state of NJ by highway name. Since many of the highways exist as multiple features be sure to group them. Sort the results from longest to shortest distance.