Monthly Archives: April 2017

Assignment 10 – Proximity Analysis

Due Date: May 2, 2017


  • In this exercise you’ll be working with the gep664 database in the nyc schema and the sample data from Chapter 10 in PostGIS in Action. It’s assumed that you have already loaded the ZCTA shapefile and census tract population centers into the nyc schema (which we did in class 7).
  • 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.
  • For the following, provide the SQL statement and the row count.


This assignment will give you additional practice in working with the various methods for calculating distances, and with geography data types. In answering these questions, you should refer back to the slides from the lecture for class 10 and to Chapter 10 in PostGIS in Action.

Do NOT use ST_Buffer to answer any of these questions: use the distance functions (ST_DWithin, ST_Distance, etc). You do not have to use the KNN methods or operators unless it’s specifically asked for.

Part I – Measuring Distance with Geometry

In this first part, you’ll be working with data in the nyc schema; this includes the ZCTA feature that we loaded from a shapefile and the census tract population centers we loaded from a text file back in class 7. If you don’t have these files, download them from the course website (data files used for Class 7) and load them into the gep664 database.

Remember the features in the nyc schema are in the NYSP Long Island spatial reference system, which uses feet. 5280 feet = 1 mile.

1. Calculate the distance from every subway station in the Bronx to every census tract population center (nyc.tract_popctr) in the Bronx. The unique id code for the Bronx is 36005; it appears in a dedicated column in the subway table, but is part of the geoid in the tract population center table. Your result should include the tract and station ids and names. Sort the data by stop_id and distance.

2. Select the ids of all census tract population centers that are within a distance of 1 mile from the two Yankee Stadium subway stations. Make sure to remove duplicates (a tract should only appear once in the results).

3. Sum the population for all census tract population centers that are within 1/2 mile of the Lincoln Center subway station. Your result should just show the total population for this area.

4. Using the KNN bounding box centroid operator , find the nearest 10 subway stations to ZCTA 10010. Return station ids and names.

Part II – Measuring Distance With Geography

In this part, you’ll be working the one of the sample data tables that’s included in the data for Chapter 10 of PostGIS in Action. A link for downloading this data is available on the course website; it’s a ZIP file that contains subfolders for every chapter in the book. You will work specifically with the airports table, which has a geography column.

Remember that units returned by the geography data type are in meters. 1000 meters = 1 kilometer.

5. For all of the airports that are of the type ‘large_airport’, measure the distance from each airport in Japan to each airport in Australia. Show the distance in kilometers, and the ids and names of the airports. Remember, to perform operations within a single table, you add the table to the FROM statement twice, and give each a different alias so you can reference them as separate entities (see the lecture slides for an example).

6. For all airports in Australia that are of the type ‘large airport’, return the closest airport of any type. Return distance in kilometers and names and ids of the airports. This question also requires you to perform operations within a single table. To remove results where each airport is being measured to itself, add an item to the WHERE clause to insure the airport id is not equal to the neighbor airport id: != Hint – you can consider using the DISTINCT ON approach for this question.

Assignment 9 – Organizing Spatial Data

Due Date: Apr 20, 2017


  • Please note that this assignment is due on Thu Apr 20 by 11:59pm. Submit it electronically to the Box; you will not be printing out or handing in a paper copy.
  • In this exercise you’ll be referring back to material from Chapter 14 in PostGIS in Action, but most of the assignment is dedicated to exploring what you would like to do for your final project.
  • This exercise is worth 6 points. Each question is worth 1.5 points each.
  • You do not have to use the SQL template for this assignment, as the questions do not involve writing code.


This assignment will review some of the concepts we covered for organizing spatial data, but is primarily designed to get you to start thinking about your final project.

Part I – Spatial Storage Approaches

In this part, refer back to Chapter 14 in PostGIS in Action, section 14.1 on pages 337-346.

1. Write a paragraph in your own words that summarizes the three different methods for storing spatial data in geometry columns. In your explanation, provide some examples of the most important advantages and disadvantages of each approach.

Part II – Final Project Brainstorming

For this part, refer back to the journal articles that I emailed you, and to the description of the final project. You will need to do some background research on what you are thinking about pursuing for your final project. Please allocate sufficient time for doing this: this will be your opportunity to receive feedback from me on your initial ideas.

2. Skim through all of the journal articles I sent you, choose one that interests you, and read it thoroughly. For the article that you chose, in your own words write a paragraph or two that explains what the author’s research question or problem is, and describe how they used a spatial database to answer that question.

3. Write a paragraph or two that describes two or three final project ideas that you are considering pursuing. For each idea, explain what the research question or goal for the different projects would be.

4. For the ideas you have listed above, explore and list some possible data sources that you could use. Be specific: provide names of specific datasets or even variables – don’t just list general websites or repositories. For example: “I could use TIGER Line shapefiles for census tracts and educational attainment data from the latest 5-year American Community Survey from the Census Bureau”. Not: “I would use the census website and some shapefiles”.