Just a single required problem this week, though I’m including a possible extra credit question as well if you are on the hunt for some last extra homework points. Everything is dealing with PostGIS, so make sure you have a database up and running with that extension installed. Most of the data this week is provided through external files, mostly CSVs, along with a .sql file to help you generate the necessary tables. The extra credit will require importing in a shapefile, so you’ll need to handle that as we discussed in class should you want to complete that problem.

As always, ensure that you include both the code from your queries and your final answers to the question in your submission. Follow the link here to accept the assignment and get access to the repository:

Problem 1: Get a Window Seat

Suppose you managed to get on a non-stop flight directly from Portland, Oregon to Paris, France! You are curious as to what cities you will be able to see from the air over the duration of your flight. The distance you can see from an airplane naturally varies with altitude and seeing conditions, but as a reasonable estimate let’s say that you can see 150 kilometers from the airplane.

Part A

Make sure you have the cities.csv data imported into a created cities table. You are going to need the location of these cities in a GIS format, so add another column named geog_loc which will store a geography point object in SRID 4326. Go ahead and populate this column using the latitude and longitude data already in the table, and then add a GIST index to the column for faster look-ups later.

Part B

The flight path itself can be computed as a line between Portland and Paris, being sure that you cast it to a geography type so that you get the great circle path (that a plane would actually fly around the curved Earth), instead of a straight line path. To get the visible cities, you can use the ST_DWithin function, which will work with a line and a point just the same way it worked for two points. Select from the cities table only those cities that are within 150 kilometers of the flight path. (As a sanity check, you should get 6128.) You do not need to include all this output with your solution, just include the SQL that you used.

Tip

It can be helpful to check yourself on this by visualizing the cities that are being found. Remember that you can use ST_AsText and ST_Collect to collect all the points into a single WKT that you could paste into a website like here to visualize the flight path. This can also serve as a very clear indicator of the difference between the routes if you are using geography types vs geometry types.

Part C

Building on the query you created above, what are the 5 most populous cities that you will see over the duration of your trip?

Part D

Create a breakdown of how many cities you see from each country over the duration of your flight. You table should include full country names as well as city counts (yes, you’ll probably need to bring in the other country_info table…), and should be ordered in descending order by count. Export it as cities_per_country.csv and upload it to GitHub.

Extra Credit Problem! Avoiding People…

The shapefile for the US county is larger than I wanted to include in the repository (about 70MB), but it can be accessed here. Importing this shapefile into your database will get you a table of all the county polygons in the entire US. Use this data in conjunction with the cities table from Problem 1 to return a table of all the counties in Oregon and their corresponding population density (in units of people per square kilometer). Order your table in descending order by population density, export it as or_county_densities.csv and upload it to GitHub.

Tip

While you could look up this information yourself, the fips code for Oregon is 41.