Project 4: Jen and Barry's Site Selection in PostGIS

A. Scenario Refresher

If you took GEOG 483, the first project had you finding the best locations for "Jen and Barry" to open an ice cream business. We're going to revisit that scenario for this project. Your task is to import the project shapefiles into a PostGIS schema and then write a series of SQL statements that automate the site selection process. If you need a new copy of the data, you can download and unzip the Project 4 Data file. Here's a reminder of Jen and Barry's original selection criteria:

• Greater than 500 farms for milk production
• A labor pool of at least 25,000 individuals between the ages of 18 and 64 years
• A low crime index (less than or equal to 0.02)
• A population of less than 150 individuals per square mile
• Located near a university or college
• At least one recreation area within 10 miles
• Interstate within 20 miles

You should narrow the cities down to 9, based on the county- and city-level criteria. After evaluating the interstate and recreation area criteria (which are a bit more difficult), that should get you down to 4 cities. 

B. Tips for completing Project 4

• While it may be possible to meet the project requirements using one really long query loaded with embedded subqueries, I suggest you avoid attempting to go that route.
• The most logical workflow I can think of is as follows:
  1. Write a query that selects the suitable counties based on the county-level criteria and save this as a view.
  2. Write a query that selects cities in the suitable counties that also meet the city-level criteria and save this as a view. This would meet 80% of the project requirements (with incorporating the interstate-recreation area criteria and the quality of your write-up accounting for 10% each).
  3. Select from cities in the view saved in #2 above that meet the "near an interstate" criterion and save this as a view.
  4. Then select from cities in the view saved in #3 above that meet the "near a recreation area" criterion.
• It should not be necessary to create any new tables to hold intermediate results.
• This is a scenario that calls for the use of cross joins.
• If you look at the .prj files for the project shapefiles, you'll see that they are in geographic coordinates, NAD27 (SRID = 4267). Be sure to re-project the data into a spatial reference that is appropriate for distance measurement when dealing with the interstates and recreation areas. Pennsylvania State Plane North (NAD27 or NAD83), units = feet, would be a logical choice for that purpose.

C. Deliverables

This project is one week in length. Please refer to the Canvas Calendar for the due date.

1. Submit a write-up that summarizes your approach to this project. Most importantly, include the SQL code that you developed. Don't forget to include the code built into your views if you create any. You are not required to create a map of the candidate cities in QGIS, though that would leave a good impression with the instructor. :-)
   
   SQL code that selects the correct 9 cities based on the county- and city-level criteria: 80 of 100 points
   SQL code that incorporates the interstate and recarea criteria: 10 of 100 points
   Quality of write-up: 10 of 100 points
2. Complete the Lesson 4 quiz.