<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 P3 - Wrangle OpenStreetMap Data
Table of Content
About the data - Sydney
XML to GeoJSON to MongoDB
A Quick Peak into Database
Overview Statistics
Data Audit and Cleaning
Problem #1 - Invalid and Inconsistent Postcode
Problem #2
Step1:  First, Let's check the size of our data file.
Step2:  I will convert the XML file into GeoJSON format then import it to a mongo database. It's should be much easier to do various data wrangling tasks inside mongo as we can leverage on various its advanced tools.  
For converting to GeoJSON, I will be using the command-line inference of osmtogeojson(https
Step3:  3. A Quick Peak into Database
Now, we have everything inside our Mongo database, we are ready to do data audit and cleaning.
let get some basic statistic about our database collection.
Step4:  First, let's take a look at the data in its GeoJSON format.
Step5:  The id field contains the top-level xml tags name and its id, seperated by a /. 
The key/value attribute of the tag tags are packed into the properties fields.
Overview Statistics
With this, I am ready to compute some basic statistics about this collection.
Number of documents
Step6:  Number of nodes
Step7:  Number of ways
Step8:  Number of relations
Step9:  Number of Contributors
Step10:  4. Data Audit and Cleaning
After some exploration of the datasets, I found three problems associated with the data. 
* Invalid and Inconsistent Postcode
* Invalid Street Number
* Inconsistent (non-standard) highway attribute
Problem #1 - Invalid and Inconsistent Postcode
Sydney is the capital city of New South Wales(NSW). According to Wikipedia, NSW's postcodes falls in the ranges of, 
* 2000—2599
* 2620—2899
* 2921—2999
We will define a function that returns the entries in the database whose postcode, if exists, does not follow the rules described earlier. We call the function, and shows the first five results.
Step11:  One of most common inconsistent pattern is having the 'NSW' prefixed to the postcode. So let's update those to have the 4-digits numbers only.
Step12:  let run the entries_with_unexpected_postcode again to see what other inconsistent or invalid pattern are there in the database
Step13:  The rest of the postcodes are just invalid entries. Let's remove those postcodes from our database.
Step14:  Problem #2
Step15:  I will fix it by dropping those addr
Step16:  Problem #3
Step17:  With this list of road type appeared in the dataset, I then compared it against a list of standard highway types I pulled mannually from OSM documentation and print out a list of highway type that is not listed in OSM documentation.
Step18:  The above set includes all of the non standard highway types. Let take a closer look at the first one, Corridor.
Step19:  Only the first and last of those have name, "Epping Club Walk" and "Epping Station Concourse". The other 9 way tags are created by Leon K. And among those, 8 of them are created at the same time. 
To get a better sense of it, I will use geopandas and mplleaflet.display() to plot those corridor on the map.
Step20:  Looks like, this is an footpath inside a building
Step21:  Those are actually the corridors inside the airport terminal.
Step22:  Looks like, this is also a footpath inside a building. 
Now, I know what those corridors are, I decided to convert them to footpath.
<ASSISTANT_TASK:>
Python Code:
%matplotlib inline
import os, subprocess, mplleaflet, re, json
import xml.etree.ElementTree as ET
import pandas as pd
import geopandas as gpd
import seaborn as sns
from pymongo import MongoClient
from pprint import pprint
from collections import defaultdict
from shapely.geometry import shape
Explanation: P3 - Wrangle OpenStreetMap Data
Table of Content
About the data - Sydney
XML to GeoJSON to MongoDB
A Quick Peak into Database
Overview Statistics
Data Audit and Cleaning
Problem #1 - Invalid and Inconsistent Postcode
Problem #2: Invalid Street Number
Problem #3: Inconsistent highway type
Ideas about improving the datasets
1. About the data - Sydney
For this project, I will be look at the OpenStreetMap data for Sydney, Australia.
The dataset is download from Mapzen's metro extracts@https://mapzen.com/data/metro-extracts/metro/sydney_australia/.
Why I picked this dataset ?
I live in Canberra. Given it's a relatively small city, the dataset isn't large enough to meet the requirement for this project. 
Because Canberra is close to Sydney, I travel there quite frequently. So it makes sense for me to use Sydney's data for this project. 
As I am quite interested in the optimal routing in logistics, I will be focusing on the cleaning the data related to roads/street.
2. XML to GeoJSON to MongoDB
End of explanation
print "File Size .... {:.2f} MB".format(os.stat("./data/sydney_australia.osm").st_size / 1024.0 ** 2)
Explanation: First, Let's check the size of our data file.
End of explanation
# convert xml to geojson
cmd_convert = [ 
    'node', '--max_old_space_size=4096', './node_modules/osmtogeojson/osmtogeojson', './data/sydney_australia.osm'
]
geojson = subprocess.check_output(cmd_convert, shell=True)
# extract features 
with open('./data/sydney_australia.geojson', 'r+') as f:
    geojson = json.loads(geojson)
    json.dump(geojson['features'], f)
# reset mongodb
cmd_resetDB = ['mongo', 'osm', '--eval', 'db.dropDatabase()']
subprocess.call(cmd_resetDB)
# import geojson to mongodb
cmd_import = [
    'mongoimport', '--db', 'osm', '--collection', 'sydney', '--file', 
    './data/sydney_australia.geojson', '--jsonArray'
]
subprocess.call(cmd_import)
Explanation: I will convert the XML file into GeoJSON format then import it to a mongo database. It's should be much easier to do various data wrangling tasks inside mongo as we can leverage on various its advanced tools.  
For converting to GeoJSON, I will be using the command-line inference of osmtogeojson(https://github.com/tyrasd/osmtogeojson) library by tysasd.
Challenge #1. V8 memory limit
According to osmtogeojson's documentation, I can convert any OSM XML file to GeoJson format by running the following in the command line.
osmtogeojson sydney_australia.osm &gt; sydney_australia.geojson
Here, I encounter the first problem. When I run the above command, I got a Error on NodeJS memory limit. After some googling, I found that, by default, the V8 engine can't utilise all spare memory on the a machine. The engine limits the memory usage to somewhere between 1GB to 1.76GB (didn't spent too much time to dig up the exact number from offical source).
Lucky, the solution to the problem is documentated in the osmtogeojson's documentation. You can increase this memory limit by running 
node --max_old_space_size=4096 ./node_modules/osmtogeojson/osmtogeojson sydney_australia.osm &gt; sydney_australia.geojson
After running this, I successful converted the .osm file to geojson.
Challenge #2. MongoDB document size limit
Now I got my geojson, it's time to import it into MongoDB. After reading the MongoDB documentation, I know I can do this by using the mongoimport utilities by running this command:
mongoimport --db osm --collection sydney --file "sydney_australia.geojson"
This time I got an Error on hitting MongoDB's document size limit. Again, I turn to google for help and this blog post pointed to the right direction. So, the problem is, in Mongo, a single document can't be larger than 16MB. And GeoJSON wraps everything inside { "type": "FeatureCollection","features": [...] }. And because of this, Mongo is treating the entire geojson as one big document. 
After some trial and error, I found the solution is to extract the features, and save it as a array of features and then add a --jsonArray flag to the mongoimport command.
Code for the complete import process
End of explanation
client = MongoClient()
db = client.osm
sydney = db.sydney
Explanation: 3. A Quick Peak into Database
Now, we have everything inside our Mongo database, we are ready to do data audit and cleaning.
let get some basic statistic about our database collection.
End of explanation
pprint(sydney.find_one(
    { 'id': { '$regex': 'way*' } },
    { 'geometry': 0 } # hide geometry for better display.
))
Explanation: First, let's take a look at the data in its GeoJSON format.
End of explanation
number_of_documents = sydney.find({}).count()
print number_of_documents
Explanation: The id field contains the top-level xml tags name and its id, seperated by a /. 
The key/value attribute of the tag tags are packed into the properties fields.
Overview Statistics
With this, I am ready to compute some basic statistics about this collection.
Number of documents
End of explanation
number_of_nodes = sydney.find({'id': {'$regex': 'node*'} }).count()
print number_of_nodes
Explanation: Number of nodes
End of explanation
number_of_ways = sydney.find({ 'id': {'$regex': 'way*'} }).count()
print number_of_ways
Explanation: Number of ways
End of explanation
number_of_relation = sydney.find({ 'id': {'$regex': 'relation*'} }).count()
print number_of_relation
# verify those three covers all of the document
number_of_documents == number_of_nodes + number_of_ways + number_of_relation
Explanation: Number of relations
End of explanation
len(list(sydney.aggregate([
    {'$group': {
        '_id': '$properties.uid',
        'count': { '$sum': 1 }
        }
     }
])))
Explanation: Number of Contributors
End of explanation
def entries_with_unexpected_postcode():
    postcodes = sydney.find(
        {'$and': [
            {'properties.addr:postcode': {'$not': re.compile('^2[0-5][0-9][0-9]$') }}, # 2000—2599
            {'properties.addr:postcode': {'$not': re.compile('^2[6-8][2-9][0-9]$') }}, # 2620—2899
            {'properties.addr:postcode': {'$not': re.compile('^29[2-9][1-9]$') }}, # 2921—2999
            {'properties.addr:postcode': {'$exists': 1}}
        ]},
        { 'properties.addr:postcode': 1 }
    ) 
    return list(postcodes)
pprint(entries_with_unexpected_postcode()[0:5])
Explanation: 4. Data Audit and Cleaning
After some exploration of the datasets, I found three problems associated with the data. 
* Invalid and Inconsistent Postcode
* Invalid Street Number
* Inconsistent (non-standard) highway attribute
Problem #1 - Invalid and Inconsistent Postcode
Sydney is the capital city of New South Wales(NSW). According to Wikipedia, NSW's postcodes falls in the ranges of, 
* 2000—2599
* 2620—2899
* 2921—2999
We will define a function that returns the entries in the database whose postcode, if exists, does not follow the rules described earlier. We call the function, and shows the first five results.
End of explanation
with_nsw_prefix = sydney.find(
    {'properties.addr:postcode': re.compile('NSW') }, # 2000—2599
    { 'properties.addr:postcode': 1 }
)
pattern = re.compile('^NSW ([0-9]{4})$')
for entry in with_nsw_prefix:
    postcode = entry['properties']['addr:postcode']
    result = sydney.update_one(
        {'_id': entry['_id'] },
        {'$set': {'properties.addr:postcode': pattern.search(postcode).group(1)} }
    )
Explanation: One of most common inconsistent pattern is having the 'NSW' prefixed to the postcode. So let's update those to have the 4-digits numbers only.
End of explanation
rest_of_invalid = entries_with_unexpected_postcode()
pprint(rest_of_invalid)
Explanation: let run the entries_with_unexpected_postcode again to see what other inconsistent or invalid pattern are there in the database
End of explanation
for entries in entries_with_unexpected_postcode():
    sydney.update_one(
        { '_id': entries['_id'] }, 
        { '$unset': {'properties.addr:postcode': ''} }
    )
Explanation: The rest of the postcodes are just invalid entries. Let's remove those postcodes from our database.
End of explanation
invalid_street_number = list(sydney.find(
    {'$and': [
        {'properties.addr:housenumber': { '$not': re.compile('[0-9]+(-[0-9]+)?') } },
        {'properties.addr:housenumber': { '$exists': 1 } }
    ]},
    {'properties.addr:housenumber': 1 }
))
pprint(invalid_street_number)
Explanation: Problem #2: Invalid Street Number
There are some also invalid street numbers in the database. For the vast majority entries, the street number, if exists, is either a number or a range of number. However, invalid street number does exist, as show in the query result below.
End of explanation
for entry in invalid_street_number:
    sydney.update_one(
        { '_id': entry['_id'] },
        { '$unset': { 'properties.addr:housenumber': '' } }
    )
Explanation: I will fix it by dropping those addr:housenumber entries
End of explanation
pprint(sydney.find_one({'id': {'$regex': 'way*'}, 'properties.highway': {'$exists': 1} }, {'geometry': 0}))
roads = list(sydney.aggregate([
    {'$match': {
        'id': { '$regex': 'way*' }, 
        'properties.highway': { '$exists': 1 } 
        }
    },
    {'$group': {
        '_id': '$properties.highway',
        'count': { '$sum': 1 }
        }
     }
]))
pprint(roads)
Explanation: Problem #3: Inconsistent highway type
OSM documentation on Map Features contains the informal standard for representing common map features, including roads/streets. 
Roads/Streets are standard map features. Roads/Streets are known as highway is OSM. They are represented by way tag. way representing a road/street should contains a tag with its k attribute equal to highway and the value of v attribute represents the type of the road. 
Translate this definition to GeoJSON, and run a find query to take a peak at it.
End of explanation
road_types = map(lambda x: x['_id'], roads)
# this is a list I pulled manually from OSM document on map feature
osm_highway_types = set([
    # way
    "motorway", "trunk", "primary", "secondary", "tertiary", "unclassified", "residential", "service", 
    "motorway_link", "trunk_link", "primary_link", "secondary_link", "tertiary_link", "living_street", "pedestrian", 
    "track", "bus_guideway", "escape", "raceway", "road", "footway", "bridleway", "steps", "path", "cycleway", "crossing"
    # area
    "rest_area", "services",
    # lifecycle
    "proposed", "construction"
])
set(road_types) - osm_highway_types # non-standard highway types
Explanation: With this list of road type appeared in the dataset, I then compared it against a list of standard highway types I pulled mannually from OSM documentation and print out a list of highway type that is not listed in OSM documentation.
End of explanation
corridors = gpd.GeoDataFrame(list(sydney.find({'properties.highway': "corridor"})))
corridors['geometry'] = corridors['geometry'].apply(lambda x: shape(x))
corridors
Explanation: The above set includes all of the non standard highway types. Let take a closer look at the first one, Corridor.
End of explanation
gpd.GeoDataFrame(corridors.iloc[0]).transpose().plot(color='red', linewidth='5')
mplleaflet.display()
Explanation: Only the first and last of those have name, "Epping Club Walk" and "Epping Station Concourse". The other 9 way tags are created by Leon K. And among those, 8 of them are created at the same time. 
To get a better sense of it, I will use geopandas and mplleaflet.display() to plot those corridor on the map.
End of explanation
corridors[1:9].plot(color='red', linewidth='5')
mplleaflet.display()
Explanation: Looks like, this is an footpath inside a building
End of explanation
gpd.GeoDataFrame(corridors.ix[10]).transpose().plot(color='red', linewidth='5')
mplleaflet.display()
Explanation: Those are actually the corridors inside the airport terminal.
End of explanation
x = sydney.update_many(
    {'properties.highway': 'corridor'},
    {'$set': { "properties.highway": "footpath" } },
)
pprint(list(sydney.find({'properties.highway': 'corridor'}))) #double checking
Explanation: Looks like, this is also a footpath inside a building. 
Now, I know what those corridors are, I decided to convert them to footpath.
End of explanation
<END_TASK>