northeast_atlanta_OSM.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project 3: Wrangle OpenStreetMap Data\n",
    "\n",
    "## Note\n",
    "<i>(All of the Python code referenced in this report is included in the project folder. Several of these pieces of code were run against the complete OSM file during the auditing process. The files in the folder are set up to run against the sample OSM that's included in the project folder.)</i>\n",
    "\n",
    "# Auditing Tag Types\n",
    "As a starting point, I used an adapted Python script from [the course](https://classroom.udacity.com/nanodegrees/nd002/parts/0021345404/modules/316820862075463/lessons/3168208620239847/concepts/77135319070923#) (<b>sample_map.py</b>) to create a couple of samples of the OSM file with k=3, k=7, k=10, and k=12. The k=12 sample is about 9MB in size and is the sample I've included in the project files. I also ran some tag-counting Python code from [the course](https://classroom.udacity.com/nanodegrees/nd002/parts/0021345404/modules/316820862075461/lessons/5436095827/concepts/54475500150923#) (<b>count_tags.py</b>) against the complete OSM file (<b>northeast_atlanta.osm</b>). This code returned a dictionary that provided a picture of the top-level tags in the data: 454,624 node tags, 55,337 way tags, and 346 relation tags.\n",
    "\n",
    "# Auditing Street Names\n",
    "I used some Python code from [the course](https://classroom.udacity.com/nanodegrees/nd002/parts/0021345404/modules/316820862075461/lessons/5436095827/concepts/54446302850923#) (<b>audit.py</b>) to do a quick run-through of the street name \"way\" tags in one of the sample files. The most immediately apparent quirk of the street names was that a number of them end with \"Northeast\", \"Northwest\", \"Southeast\", and \"Southwest\". This is because of the quadrants that the City of Atlanta is divided up into per [this piece of law](https://www.municode.com/library/ga/atlanta/codes/code_of_ordinances?nodeId=PTIIICOORANDECO_PT16ZO_CH2OFZOMAAPRE_S16-02.019CIQUSTNU). Abbreviations of these quadrants also appeared: \"NW\", \"NE\", \"SE\", and \"SW\" (with and without periods).\n",
    "\n",
    "I did an audit of the complete OSM file with an altered version of the auditing code from [the course](https://classroom.udacity.com/nanodegrees/nd002/parts/0021345404/modules/316820862075461/lessons/5436095827/concepts/54446302850923#) (<b>audit_last_word.py</b>) in order to get a short list of all the street names that did not end with one of the aforementioned quadrants.\n",
    "\n",
    "This run-through resulted in a few observations:\n",
    "* spelled-out and abbreviated expected street types (\"Street\", \"Avenue\", \"Boulevard\", etc.)\n",
    "* a few unexpected street types (\"Circle\", \"Way\", \"Promenade\")\n",
    "* some unusual street names that do not appear to be typical street types (\"Clifton\", \"Parkwood\")\n",
    "* some street names that start with cardinal directions (\"North\", \"South\", \"East\", \"West\") or abbreviations of these cardinal directions (\"N\", \"S\", \"E\", \"W\")\n",
    "\n",
    "One oddball street name that popped up was \"Center\", which was referring to the address \"One CNN Center\". This is the address of [Turner Headquarters](https://www.turner.com/contact-us). Despite being within Atlanta's city limits this address does not appear to have a quadrant suffix.\n",
    "\n",
    "I wrote and ran another Python auditing program (<b>audit_second_last.py</b>) against the complete OSM file in order to look at the second-to-last word of street names that end in one of the aforementioned quadrants. A few new street types showed up in this audit: \"Hollow\", \"Park\", \"Plaza\", \"Terrace\", \"View\", and \"Walk\". I added these, along with the street types that showed up in my first two audits, to the expected street types and abbreviations that I would use during the data cleaning phase.\n",
    "\n",
    "A few new unusual items showed up as second-to-last words, including \"Prado\" and \"West\". [The Prado Northeast](https://www.google.com/maps/place/The+Prado+NE,+Atlanta,+GA+30309/@33.7933553,-84.3813676,17z/data=!3m1!4b1!4m5!3m4!1s0x88f50448c4762323:0x2ddcb7137f39bfc2!8m2!3d33.7933553!4d-84.3791789) and [South Prado Northeast](https://www.google.com/maps/place/S+Prado+NE,+Atlanta,+GA+30309/data=!4m2!3m1!1s0x88f50447f403c0ef:0xc7a16ec92f22a4c2?sa=X&ved=0ahUKEwjn4sads9_RAhXDSSYKHQsgArwQ8gEIGTAA) are streets in the Ansley Park neighborhood of Atlanta according to Google Maps and appear to be correctly formatted as is. The street name with the second-to-last word \"West\" is \"Park Ave West N.W.\", which, while a confusing name, [appears to be the correct name](https://www.google.com/maps/place/Park+Ave+W+NW,+Atlanta,+GA+30313/data=!4m2!3m1!1s0x88f5047fb6a4445b:0x99b88a255c11ef3a?sa=X&ved=0ahUKEwiHoeO_s9_RAhUJ7yYKHaD9CrMQ8gEIGTAA), and should be changed to \"Park Avenue West Northwest\" when it comes time to clean the dataset.\n",
    "\n",
    "# Auditing Other Data Features\n",
    "\n",
    "## Postal Codes\n",
    "I wanted to take a look at the postal codes of nodes in the dataset to see if they are consistently format. I wrote a short Python program (<b>audit_postal_codes.py</b>) that I ran against the complete OSM file. It returned the following set:\n",
    "```\n",
    "set(['1520', '1540', '30030', '30030-3807', '300313', '30032', '30033',\n",
    "'30033-5367', '30303', '30303-3506', '30306', '30307', '30308', '30308-3217',\n",
    "'30309', '30312', '30313', '30313-1591', '30316', '30317', '30318', '30322',\n",
    "'30324', '30329', '30332', '30333', '30363', 'GA 30307', 'GA 30309'])\n",
    "```\n",
    "Most of the results in the set that the program returned appeared to be correctly formatted as five-digit codes and also fall within the greater Atlanta area. There were some exceptions, however:\n",
    "* \"GA 30307\" and \"GA 30309\", which are preceded by \"GA\"\n",
    "* \"30033-5367\", \"30030-3807\", \"30033-3506\", \"30308-3217\", and \"30313-1591\", which include a four-digit postcode suffix\n",
    "* \"300313\", which is too long to be a zip code\n",
    "* \"1520\" and \"1540\", which are too short to be zip codes\n",
    "\n",
    "I made a note of these in order to ensure that the cleaning process addressed these particular issues. I also wrote a little bit of Python code (<b>find_erroneous_zips.py</b>) to determine which addresses have the long/short postal codes so I could look them up on the U.S. Postal Service website.\n",
    "\n",
    "When I ran this code against the complete OSM file it returned a defaultdict with two entries for zip code 300313 and one each for zip codes 1520 and 1540.\n",
    "\n",
    "The two items with postcode 300313 are the Children's Museum of Atlanta and the Museum Tower at Centennial Hill. The Children's Museum has the zip code 30313 according to [their website](http://childrensmuseumatlanta.org/), and the [Museum Tower](http://museumtower.com/) has the same zip code per the USPS zip code [lookup tool](https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=1&companyName=&address1=285+Centennial+Olympic+Park+Drive&address2=&city=Atlanta&state=GA&urbanCode=&postalCode=&zip=). I made a note of these to ensure these would corrected during data cleaning.\n",
    "\n",
    "As for the postal codes that were too short, the address names that were returned were '100' and '200', which I thought might be building numbers. I wrote a short Python program (<b>look_up_short_zips.py</b>) to find the names of these buildings, which turned out to be \"100 Emory Place\" and \"200 Emory Place\", both of which are located in the 30329 zip code as part of a development near Emory University called Emory Point. I made a note of this in order to correct these zip codes during the cleaning phase.\n",
    "\n",
    "## Places of Worship\n",
    "I was curious about the various places of worship that are in the dataset, as well as how they were formatted. I decided to use the XML ElementTree module in Python to get a sense of what's in the OSM file. I wrote a short Python program (<b>audit_places_worship.py</b>) to run through nodes with key='amenity' and value='place_of_worship'.\n",
    "\n",
    "Along with a defaultdict listing out the names of each place of worship, the code also returned this information:\n",
    "```\n",
    "Number of places of worship counted in dataset: 70\n",
    "Number of Hindu places of worship: 1\n",
    "Number of Muslim places of worship: 1\n",
    "Number of Christian places of worship: 66\n",
    "```\n",
    "I didn't see any problematic or inconsistent formatting or abbreviations in the names of the places of worship. One thing I immediately thought of, however, is the fact that none of the local Jewish temples or synagogues are in the dataset, as well as the fact that only three religions showed up. Additionally, the specific religions that showed up in the results only add up to 68 whereas 70 places of worship were counted, so there are at least 2 'place_of_worship' nodes that are not labeled with a religion. \n",
    "\n",
    "# Cleaning the Data\n",
    "I decided that the tidiest, most clear way of formatting the street names would be to have fully spelled-out street names (\"Avenue\" as opposed to \"Ave\") and, for City of Atlanta addresses, to have the quadrant fully spelled out (\"Northeast\" as opposed to \"NE\"). I did not want to attach quadrants to street names that do not already have them because these street names might fall in Decatur, Brookhaven, Sandy Springs, unincorporated Fulton County, unincorporated Dekalb County, or some other non-City of Atlanta area.\n",
    "\n",
    "Here were my notes going into the cleaning process:\n",
    "* All the two- to four-letter abbreviations of street types have a corresponding spelled-out name that they could be replaced with.  \n",
    "* Streets where it isn't at all clear what type of street is being referred to should be left alone. For instance, \"Clifton\" and \"Parkwood\".\n",
    "* There's no foolproof way I know of to attach quadrants to street names without them.\n",
    "* The quadrant abbreviations (\"NE\", \"SE\", etc.) can easily be replaced with the spelled-out quadrant name. For these streets the word preceding the quadrant or quadrant abbreviation needs to be cleaned as well.\n",
    "* I need to fix any first-word abbreviations of the cardinal directions: \"N\", \"N.\", \"E\", \"E.\", \"S\", \"S.\", \"W\", or \"W\".\n",
    "* I need to fix the erroneous postal codes per my notes from auditing.\n",
    "* I also want to make sure that \"Park Avenue West\" appears correctly in the cleaned document.\n",
    "\n",
    "I added helper functions to the shape_element function of the Python program from [the course](https://classroom.udacity.com/nanodegrees/nd002/parts/0021345404/modules/316820862075461/lessons/5436095827/concepts/54908788190923#) that prepares the node and way data from the OSM file for insertion as CSVs into a database. I also used the <b>schema.py</b> file from [that part of the course](https://classroom.udacity.com/nanodegrees/nd002/parts/0021345404/modules/316820862075461/lessons/5436095827/concepts/54908788190923#).\n",
    "\n",
    "Here is one helper function I wrote to update postal codes in the dataset along with the dictionary that specifies the mapping for these codes.\n",
    "\n",
    "    zip_mapping = {\"30033-5367\": \"30033\", \"GA 30307\": \"30307\", \"GA 30309\": \"30309\",\n",
    "        \"30030-3807\": \"30030\", \"30303-3506\": \"30303\", \"30308-3217\": \"30308\", \n",
    "        \"30313-1591\": \"30313\", \"300313\": \"30313\", \"1520\": \"30329\", \"1540\": \"30329\"}\n",
    "\n",
    "    def update_postcode(postcode, mapping):\n",
    "        \"\"\"Fix postcode from Northeast Atlanta dataset.\"\"\"\n",
    "        if postcode in mapping.keys():\n",
    "            fixed_postcode = zip_mapping[postcode]\n",
    "            return fixed_postcode\n",
    "        else:\n",
    "            return postcode\n",
    "\n",
    "This program (<b>database_prep.py</b>) worked with the sample OSM files, so I ran it against the complete OSM file and imported the resulting CSVs into a database via SQLite entitled \"atlanta_db.db\" using the [schema](https://gist.github.com/swwelch/f1144229848b407e0a5d13fcb7fbbd6f) provided in the project instructions. An overview of the data within (along with the SQL queries I used to get this information) are described below.\n",
    "\n",
    "# Data Overview\n",
    "\n",
    "## File Sizes\n",
    "I determined the file size of each of the relevant files from the command line in Terminal using the command `ls -l FILENAME` for each:\n",
    "\n",
    "    northeast_atlanta.osm     107 MB\n",
    "    atlanta_db.db             58.5 MB\n",
    "    nodes.csv                 43 MB\n",
    "    node_tags.csv             0.87 MB\n",
    "    ways.csv                  3.9 MB\n",
    "    ways_tags.csv             6.5 MB\n",
    "    ways_nodes.csv            12 MB\n",
    "\n",
    "## Number of Nodes and Ways\n",
    "Here's the SQL query I used to get the number of nodes:\n",
    "```\n",
    "sqlite> SELECT COUNT(*) FROM nodes;\n",
    "```\n",
    "    454624\n",
    "\n",
    "And this is the SQL query I used for getting the number of ways:\n",
    "```\n",
    "sqlite> SELECT COUNT(*) FROM ways;\n",
    "```\n",
    "    55337\n",
    "    \n",
    "These were the same queries used in the [sample SQL project](https://gist.github.com/carlward/54ec1c91b62a5f911c42#file-sample_project-md) and they matched the results of the tag-counting Python code used earlier.\n",
    "\n",
    "## Number of Unique Users\n",
    "```\n",
    "sqlite> SELECT COUNT(DISTINCT(u.uid)) FROM (SELECT uid FROM nodes UNION ALL\n",
    "SELECT uid FROM ways) u;\n",
    "```\n",
    "    489\n",
    "\n",
    "## Number and Variety of Leisure Options\n",
    "```\n",
    "sqlite> SELECT tags.value, COUNT(*) as count FROM (SELECT * FROM nodes_tags\n",
    "UNION ALL SELECT * FROM ways_tags) tags WHERE tags.key='leisure' GROUP BY\n",
    "tags.value ORDER BY count DESC;\n",
    "```\n",
    "\n",
    "    pitch|215\n",
    "    park|151\n",
    "    recreation_ground|119\n",
    "    playground|49\n",
    "    swimming_pool|20\n",
    "    sports_centre|18\n",
    "    garden|13\n",
    "    dog_park|6\n",
    "    stadium|6\n",
    "    golf_course|5\n",
    "    nature_reserve|4\n",
    "    track|3\n",
    "    dance|1\n",
    "    fitness_centre|1\n",
    "    fitness_station|1\n",
    "    sport|1\n",
    "\n",
    "(I think that \"pitch\" is the UK term for soccer fields. My reason for thinking this is the fact that the UK spelling of the word \"center\" appears in one of the results, \"fitness_centre.\")\n",
    "\n",
    "## Number of Cafes\n",
    "```\n",
    "sqlite> SELECT tags.value, COUNT(*) as count FROM (SELECT * FROM nodes_tags\n",
    "UNION ALL SELECT * FROM ways_tags) tags WHERE tags.key='amenity' and\n",
    "tags.value='cafe';\n",
    "```\n",
    "    cafe 57\n",
    "\n",
    "## Most Common Gas Station Brands\n",
    "I wanted to find the most common gas station brands in the region. Some gas stations in the dataset are ways and some are nodes. The brand name should be the value of key='brand'.\n",
    "\n",
    "```\n",
    "sqlite> SELECT nodes_tags.value, COUNT(*) as count FROM nodes_tags JOIN\n",
    "(SELECT DISTINCT(nodes_tags.id) FROM nodes_tags WHERE value='fuel') fuel\n",
    "on nodes_tags.id=fuel.id WHERE nodes_tags.key='brand' GROUP BY nodes_tags.value\n",
    "ORDER BY count DESC;\n",
    "```\n",
    "    Exxon|3\n",
    "    Chevron|1\n",
    "    Marathon|1\n",
    "```\n",
    "sqlite> SELECT ways_tags.value, COUNT(*) as count FROM ways_tags JOIN (SELECT\n",
    "DISTINCT(ways_tags.id) FROM ways_tags WHERE value='fuel') fuel on\n",
    "ways_tags.id=fuel.id WHERE ways_tags.key='brand' GROUP BY ways_tags.value\n",
    "ORDER BY count DESC;\n",
    "```\n",
    "    Shell|2\n",
    "    Valero|2\n",
    "    Conoco|1\n",
    "    \n",
    "This seems like very few gas stations considering the size of the dataset's area. I'll check to see if the key='name' field has the station brand in the nodes and ways tags.\n",
    "\n",
    "```\n",
    "sqlite> SELECT nodes_tags.value, COUNT(*) as count FROM nodes_tags JOIN (SELECT\n",
    "DISTINCT(nodes_tags.id) FROM nodes_tags WHERE value='fuel') fuel on\n",
    "nodes_tags.id=fuel.id WHERE nodes_tags.key='name' GROUP BY nodes_tags.value\n",
    "ORDER BY count DESC;\n",
    "```\n",
    "    Chevron|3\n",
    "    Exxon|2\n",
    "    Shell|2\n",
    "    BP|1\n",
    "    Gulf|1\n",
    "    Marathon|1\n",
    "    Pure Station|1\n",
    "    QuikTrip|1\n",
    "    SHELL|1\n",
    "    Standard|1\n",
    "\n",
    "```\n",
    "sqlite> SELECT ways_tags.value, COUNT(*) as count FROM ways_tags JOIN (SELECT\n",
    "DISTINCT(ways_tags.id) FROM ways_tags WHERE value='fuel') fuel on\n",
    "ways_tags.id=fuel.id WHERE ways_tags.key='name' GROUP BY ways_tags.value\n",
    "ORDER BY count DESC;\n",
    "```\n",
    "    Shell|2\n",
    "    BP|1\n",
    "    CITGO|1\n",
    "    Exxon|1\n",
    "\n",
    "It appears that some gas stations have their brand name mistakenly appearing as the value for the key='name' field rather than the key='brand' field where it should be per the [OSM Wiki](http://wiki.openstreetmap.org/wiki/Tag:amenity%3Dfuel).\n",
    "\n",
    "# Larger Issues to Address\n",
    "Here are some larger issues with the dataset that I think would be great to address.\n",
    "\n",
    "## Additional Quadrant Info\n",
    "It'd be great to determine definitively which street names without quadrant suffixes are in need of them and add them where needed. One problem I foresee with this would be streets that run through multiple cities or counties. For instance, one street might begin in Atlanta, where it would have a quadrant suffix, and continue into Decatur where it would no longer have the suffix. Additionally there seem to be exceptions to the quadrant rule within the City of Atlanta, such as the aforementioned One CNN Center. It may be possible to get some answers as to the reasons for these exceptions with some further research on the laws pertaining to street-naming in Atlanta.\n",
    "\n",
    "## Eliminating Redundancy with Street Names\n",
    "I'd like to look for instances where multiple street names may be referring to precisely the same street in the dataset. For instance, I could easily see there being two streets that have almost exactly the same name but one incorrectly lacks a quadrant suffix. However, as with the additional quadrant information I'd like to add, there's the possibility of one street correctly having different names as it passes through different cities. One issue here is that I'm not sure what the gold standard data would be for this kind of information. Whatever I do with these streets would obviously need to be systematic and consistent across the board.\n",
    "\n",
    "## Cleaning Up Gas Station Formatting\n",
    "The formatting of the gas station ways and tags should be cleaned up so the brand name only appears under key='brand' and the key='name' field is reserved for the station's unique name if it has one.\n",
    "\n",
    "## Adding Missing Places of Worship\n",
    "Additionally I'd like to comb through information on places of worship in northeast Atlanta and determine what places of worship (particularly non-Christian ones) do not currently appear in the OSM data. This would include adding any Jewish, Hindu, Muslim, Buddhist, and other places of worship. For instance, I think that local Jewish synagogue [Shearith Israel](https://shearithisrael.com/about/) should have shown up in the data somewhere, but the search for places of worship didn't turn it up.\n",
    "\n",
    "One problem with systematically adding places of worship is that I'm not sure if there's any gold standard data regarding all the places of worship in the city. That said the standards on the [OpenStreetMap Wiki](http://wiki.openstreetmap.org/wiki/Tag:amenity%3Dplace_of_worship) regarding adding places of worship to the data would prove helpful if I were to start adding these kinds of places to the dataset."
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}