OpenStreetMap Data Wrangling: Greater Salt Lake City

This project audits, cleans, and analyzes OpenStreetMap (OSM) data for the Greater Salt Lake City metropolitan area. It was completed for the Data Wrangling project of Udacity's Data Analyst Nanodegree program. The workflow takes a raw OSM XML extract, samples it down to a manageable size, audits and programmatically cleans problem fields (street names and postal codes), converts the cleaned data into CSV files matching a fixed schema, loads those CSVs into a SQLite database, and then runs SQL queries against the database to surface findings about the area.

Data

The source data is an OpenStreetMap extract for the Greater Salt Lake City area, originally pulled via Mapzen (a service that shut down in February 2018, shortly after this project was completed). The full extract used for the project was approximately 318-325 MB uncompressed. Mapzen's underlying export is described at https://mapzen.com/ and the raw full dataset referenced in the original submission is available at https://goo.gl/mJKpQD.

Because reprocessing the original multi-hundred-megabyte extract is no longer practical (Mapzen is gone, and the full file was never checked into this repository), a representative subsample (sample.osm, about 13 MB) and the downstream CSVs and SQLite database built from it are included in this repository so the analysis is reproducible without needing the original source file.

Method / What was done

1. Sampling. Processing the Dataset.py streams the full OSM XML with iterparse and writes out every 25th top-level element (node, way, relation) to sample.osm, shrinking the dataset from roughly 318 MB to about 13 MB so it could be audited and processed locally.
2. Auditing. The scripts in Auditing the Data/ (and audit.py) walk the sample looking for irregular addr:street values (using a regex over the last word of each street name against an "expected" list of valid suffixes) and irregular addr:postcode values (checking that postal codes are five digits).
3. Cleaning and CSV conversion. The scripts in Cleaning the Data/ apply a mapping dictionary to normalize the street-name irregularities found during auditing (for example, expanding "Rd." to "Road"), then walk the full sample XML and write out five CSV files (nodes.csv, nodes_tags.csv, ways.csv, ways_nodes.csv, ways_tags.csv) whose shape is validated against schema.py using the cerberus library.
4. Loading and exploring. The CSVs are loaded into a SQLite database (SQL_Salt_Lake.db), and the scripts in Exploring the Database/ run a series of SQL queries (GROUP BY / COUNT / ORDER BY aggregations, and a UNION ALL across the tags tables) to characterize contributors, amenities, places of worship, named places, and postal-code concentration in the area.

The three notebooks in the repository root represent successive drafts of this project as it was revised against Udacity's project rubric: OpenStreetMap_Project.ipynb (initial submission), OpenStreetMap_Project(Second Attempt).ipynb (first revision), and OpenStreetMap_Project(Third Attempt).ipynb (final revision, incorporating grader feedback and additional commentary). The numbered .py files under Auditing the Data/, Cleaning the Data/, and Exploring the Database/ are individual code cells exported from the final notebook so each step can be viewed as a standalone script. OpenStreetMap Report.pdf is the written submission addressing the project rubric questions directly.

Key findings

• The sample analyzed contained contributions from 794 unique OSM users.
• A single contributor, "chadbunn," accounted for roughly 20% of edits among the top 20 contributors (9,895 of 48,420 entries from that group).
• The most common amenity tag was restaurant (41 occurrences), followed by place_of_worship (32), fast_food (19), fuel (14), and parking (14).
• Every place of worship in the sample was tagged with religion christian. By denomination: mormon (22), latter_day_saints (2), catholic (1), and jehovahs_witness (1), consistent with Salt Lake City's role as the geographic center of the Latter-day Saint faith.
• The most common named place was "The Church of Jesus Christ of Latter-day Saints" (22 occurrences); the next most common names were convenience stores and fast-food chains (7-11, 7-Eleven, Arby's, Burger King).
• Postal codes were heavily concentrated around central Salt Lake City: 84105 (219 tagged entities), 84108 (127), 84106 (123), and 84102 (56) accounted for the largest counts, with the remaining zip codes trailing off sharply.
• Auditing found no postal codes with a digit count other than five, so no postal-code cleaning was required. Street-name auditing found one irregular value in the sample ("Rd." instead of "Road"), which the cleaning step normalized.
• Data sparsity: of 3,708 rows in the nodes_tags table, only 33 corresponded to named places, which the report flags as a coverage gap in the underlying OSM data for this area.

Repository structure

OpenStreetMap_Project.ipynb                  Initial notebook submission
OpenStreetMap_Project(Second Attempt).ipynb  First revision
OpenStreetMap_Project(Third Attempt).ipynb   Final revision (most complete narrative)
OpenStreetMap Report.pdf                     Written report addressing the project rubric
Processing the Dataset.py                    Samples the raw OSM XML down to sample.osm
audit.py                                     Shared street-name auditing helper
schema.py                                    Cerberus schema used to validate rows before CSV export
Auditing the Data/                           Numbered scripts, one per notebook cell, for the auditing step
Cleaning the Data/                           Numbered scripts, one per notebook cell, for cleaning and CSV export
Exploring the Database/                      Numbered scripts, one per notebook cell, for the SQL exploration step
sample.osm                                   ~13 MB subsample of the full OSM extract (every 25th element)
nodes.csv, nodes_tags.csv                    Cleaned node data and node tags, ready for SQLite import
ways.csv, ways_nodes.csv, ways_tags.csv      Cleaned way data, way-node relationships, and way tags
SQL_Salt_Lake.db                             SQLite database loaded from the CSVs above and queried in the exploration step

How to run

This project was written against Python 2 (note the bare print statements and u'' string literals in the scripts and notebook output) and run in a Jupyter/IPython notebook. Library versions are historical and are not pinned here; at the time this was written the main libraries in use were:

• xml.etree.ElementTree / xml.etree.cElementTree (standard library) for streaming XML parsing
• csv, sqlite3, re, collections, pprint (standard library)
• cerberus for schema validation of rows before CSV export

To explore the project, open OpenStreetMap_Project(Third Attempt).ipynb in Jupyter Notebook (it contains the full, most current narrative and code in one place), or read OpenStreetMap Report.pdf for a condensed write-up of the process and findings. SQL_Salt_Lake.db can be opened directly with any SQLite client to rerun or extend the queries in the exploration step without re-running the earlier wrangling stages.

License

Released under the MIT License. See LICENSE for details.