Make & Postgis Project (Fatima)

[fgomez828]

This is an issue for me to track my progress on a small project using Make and Postgis. The question I will be trying to answer is: how much of a given city is within two miles of a McDonald’s?

[hancush]

Forest to review.

[fgomez828]

here's the link to the repo where I'm working on this project: https://github.com/fatima3558/walk-to-mcd

[hancush]

Some examples of SQL ETL pipelines:

• https://github.com/datamade/justice-divided/tree/master/data
• https://gitlab.com/ChicagoDataCooperative/court-data-standardization

Cc @fgregg. Any other good contemporary models for a pipeline that does much of the work in SQL?

[hancush]

@fatima3558 got additional resources from @fgregg, will review those resources and update this thread with a concrete next step.

[fgomez828]

@fgregg here are the steps I think are next based on your suggestions from the last time we talked about this project:

• [ ] Convert geojson into csv (good ol’ in2csv does that: https://csvkit.readthedocs.io/en/latest/scripts/in2csv.html)
• [ ] Write management command to store csv data in db
  • [ ] Make sure models are set up to store these character fields
  • [ ] Store geographic info as character fields
• [ ] Find an example / docs / a tutorial / a DataMade repo that stores geographic information using postgis.
  • [ ] Hannah linked to some examples: https://github.com/datamade/how-to/issues/201#issuecomment-886918224
• [ ] Make changes to models to store geographical data instead of character fields, and update the mgmt cmd
• [ ] Store info for the geographies I plan to use; at the moment I’m planning on using Chicago
• [ ] Write a script that gets the following pieces of info:
  • [ ] Number of all Starbucks locations (shifted from McDonalds to Starbucks due to a bug in alltheplaces.xyz data)
  • [ ] Calculate the total surface area covered by circles 2miles (roughly walking distance) around each Starbucks (this still feels like a big question mark; didn’t have a chance to dig into this question)
  • [ ] Chicago area based on geo data from db
  • [ ] Calculates the result as a percentage of total Chicago area and prints it to stdout

Am I missing anything?

[fgregg]

I would suggest not doing this with GeoDjango, at least to start with. It's another level of abstraction and hard to follow.

I would recommend your first step is to try to get your GeoJSON file directly into a postgis database with ogr2ogr. (example https://github.com/datamade/thrive-zones/blob/839438cb4bf4d3f3dbf6e7ae85204a12c5f921ed/data/Makefile#L13)

Some good steps would be

• [x] get postgis install locally (probably already done)
• [x] using DBeaver create a new database, and load the postgis extension if that's not installed (Go to SQL and run "use extension postgis"
• [x] Use ogr2ogr to load in your geojson directly https://blog.crunchydata.com/blog/loading-data-into-postgis-an-overview
• [x] get into the database and make some queries

[hancush]

@fgregg is going to add some examples of geographic queries to this issue.

[fgomez828]

@fgregg made headway on the query necessary to calculate the percentage of Chicago within 2 miles of a Starbucks. I was going to start adding the total of the intersections between the Chicago area with the 2mile buffer around each Starbucks location, but I noticed every area value is identical. Here are the query and a few records:

select starbucks."addr:full", starbucks."addr:city", st_area(st_intersection(st_buffer(starbucks.geom, 2 * 1609.344),chicago.geom)) from starbucks inner join chicago on st_intersects(starbucks.geom, chicago.geom);

           addr:full            | addr:city |       st_area
--------------------------------+-----------+---------------------
 6332 Northwest Highway         | Chicago   | 0.06495417436755024
 8500 W Bryn Mawr Ave           | Chicago   | 0.06495417436755024
 Circular Building #6           | Chicago   | 0.06495417436755024
 Circular Building #6           | Chicago   | 0.06495417436755024
 Circular Building #6           | Chicago   | 0.06495417436755024
 10000 West O'Hare Ave          | Chicago   | 0.06495417436755024
 Circular Building #6           | Chicago   | 0.06495417436755024
 3506 S. State St.              | Chicago   | 0.06495417436755024
 3857 S. Dr Martin Luther King  | Chicago   | 0.06495417436755024
 3108 South Halsted             | Chicago   | 0.06495417436755024

Beyond the fact that there are duplicate starbucks locations here, as best I can tell when I scroll through the whole list there are apparently no starbucks near the edges of Chicago's boundary, which is possible but seems suspicious. Any thoughts on how I can double-check this? I did a quick check against google maps, but I'm not sure if there's a more reliable way to verify this.

I'm also a bit confused about units here -- when I run a practice query I did to build up to the above more complex one:

select starbucks."addr:full", starbucks."addr:city", st_area(st_buffer(starbucks.geom, 2 * 1609.344)) from starbucks limit 1;

        addr:full         | addr:city |      st_area
--------------------------+-----------+--------------------
 4-831 Kuhio Highway #208 | Kapaa     | 32338023.325657208

The units are in square meters, and when I convert this to square miles it is about 12.5 square miles (google conversion here) which sounds right to me for a circle with radius 2 miles.

I'm not sure what's happening at the level of units with the more complex query.

[fgomez828]

@fgregg here's the final repo with a complete Makefile & recipes to download files, create & prep the database, and run the central query: https://github.com/fatima3558/walk-to-mcd/pull/7

NOTE: I didn't get a chance to put this together with Docker in mind, so you can run the commands without docker-compose

[fgomez828]

Got map previews! Map of Chicago within 2 miles of a Starbucks

Map of Chicago NOT within 2 miles of a Starbucks