OpenStreetMap Data Wrangling with SQL

#This is not complete list of mapping. Please refer data.py

expected = ["Street", "Avenue", "Boulevard", "Drive", "Court", "Place", "Square", "Lane", "Road",
            "Trail", "Parkway", "Commons", "Layout", "Main", "Broadway", "Plaza", "Park"]

mapping = {"St": "Street",
           "st": "Street",
           "ST": "Street",
           "St.": "Street",
           "St,": "Street",
           "Street.": "Street",
           "street": "Street",
           "Sq": "Square",
           "Rd.": "Road",
           "Rd": "Road",
           }

#Auditing Street Names

def audit_street_type(street_types, street_name):
    m = street_type_re.search(street_name)
    if m:
        street_type = m.group()
        if street_type not in expected:
            street_types[street_type].add(street_name)


def is_street_name(elem):
    return (elem.attrib['k'] == "addr:street")


def audit(osmfile):
    osm_file = open(osmfile, "r")
    street_types = defaultdict(set)
    for event, elem in ET.iterparse(osm_file, events=("start",)):

        if elem.tag == "node" or elem.tag == "way":
            for tag in elem.iter("tag"):
                if is_street_name(tag):
                    audit_street_type(street_types, tag.attrib['v'])
    osm_file.close()
    return street_types


def update_name(name, mapping):
    for key, value in mapping.iteritems():
        if re.search(key, name):
            name = re.sub(street_type_re, value, name)
        #print name

    return name

# Audit Postal codes

def is_postcode(elem):
    return (elem.attrib['k'] == "addr:postcode")

def update_postcode(postcode):
    # new regular expression pattern
    search = re.match(r'^\D*(\d{5}).*', postcode)
    # select the group that is captured
    if search:
        clean_postcode = search.group(1)
        return clean_postcode

def audit_postcode(osmfile):
    post_file = open(osmfile, "r")
    for event, elem in ET.iterparse(post_file, events=("start",)):
        if elem.tag == "node" or elem.tag == "way":
            for tag in elem.iter("tag"):
                if is_postcode(tag):
                    post_code = tag.attrib['v'].strip()
                    #print post_code
                    post_code = update_postcode(post_code)
                    #print post_code
                    #print ""
    post_file.close()
    #return post_code

import sqlite3
import csv
from pprint import pprint

sqlite_file = 'mydb.db'

# Connect to the database
conn = sqlite3.connect(sqlite_file)

# Get a cursor object
cur = conn.cursor()

#Create nodes table
#Before I (re)create the table, I will have to drop the table if it already exists
cur.execute('''DROP TABLE IF EXISTS nodes''')
conn.commit()
cur.execute('''
CREATE TABLE nodes (
    id INTEGER PRIMARY KEY NOT NULL, lat REAL, lon REAL, user TEXT, uid INTEGER, version INTEGER, changeset INTEGER,
    timestamp TEXT)
''')
conn.commit()

# Read in the csv file as a dictionary, format the
# data as a list of tuples:
with open('nodes_tags.csv','rb') as fin:
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['id'], i['key'],i['value'].decode("utf-8"), i['type']) for i in dr]

# insert the formatted data
cur.executemany("INSERT INTO nodes_tags(id, key, value,type) VALUES (?, ?, ?, ?);", to_db)
# commit the changes
conn.commit()

# Number of nodes

cur.execute("SELECT COUNT(*) FROM nodes;") 
pprint("Number of nodes")
pprint(cur.fetchall())

# Number of ways

cur.execute("SELECT COUNT(*) FROM ways;")
pprint("Number of ways")
pprint(cur.fetchall())

# Number of unique users

cur.execute("SELECT COUNT(DISTINCT(e.uid)) \
FROM (SELECT uid FROM nodes UNION ALL SELECT uid FROM ways) e;") 
pprint("Number of unique users")
pprint(cur.fetchall())

# Top 10 contributing users


cur.execute("SELECT e.user, COUNT(*) as num \
FROM (SELECT user FROM nodes UNION ALL SELECT user FROM ways) e \
GROUP BY e.user \
ORDER BY num DESC \
LIMIT 10")
pprint("Top 10 contributing users")
pprint(cur.fetchall())

# Top 10 common amenities in Boston 

cur.execute("SELECT value, COUNT(*) as num \
FROM nodes_tags \
WHERE key='amenity' \
GROUP BY value \
ORDER BY num DESC \
LIMIT 10;")
pprint("Top 10 common amenities in Boston")
pprint(cur.fetchall())

# Top 10 popular cuisines 

cur.execute("SELECT nodes_tags.value, COUNT(*) as num \
FROM nodes_tags \
    JOIN (SELECT DISTINCT(id) FROM nodes_tags WHERE value='restaurant') i \
    ON nodes_tags.id=i.id \
WHERE nodes_tags.key='cuisine' \
GROUP BY nodes_tags.value \
ORDER BY num DESC \
LIMIT 10;")
pprint("Top 10 popular cuisines")
pprint(cur.fetchall())

# Number of Book stores

cur.execute("select count(*) from nodes_tags where key='shop' and value='books';")
pprint("Number of Book stores")
pprint(cur.fetchall())

# Top 5 popular cafes

cur.execute("SELECT nodes_tags.value, COUNT(*) as num FROM nodes_tags JOIN (SELECT DISTINCT(id) \
             FROM nodes_tags WHERE value='cafe') i ON nodes_tags.id=i.id WHERE nodes_tags.key='name' \
             GROUP BY nodes_tags.value ORDER BY num DESC LIMIT 5;")
pprint("Top 5 popular cafes")
pprint(cur.fetchall())

# Top 5 most occuring banks

cur.execute("SELECT nodes_tags.value, COUNT(*) as num \
        FROM nodes_tags \
            JOIN (SELECT DISTINCT(id) FROM nodes_tags WHERE value='bank') i \
            ON nodes_tags.id=i.id \
        WHERE nodes_tags.key='name' \
        GROUP BY nodes_tags.value \
        ORDER BY num DESC \
        LIMIT 5;")
pprint("Top 5 most occuring banks")
pprint(cur.fetchall())

cur.execute("SELECT * FROM nodes_tags where key='phone' limit 318, 10;") 
pprint("Number of nodes")
pprint(cur.fetchall())