geocode

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import csvkit
import os
import errno
import glob
import sys
import time
import math
import sqlite3
import urllib
import urllib2
import json
import hashlib
import argparse
import time

parser = argparse.ArgumentParser(
    description="A geocoder for for Virginia State Corporation Commission records",
    epilog="https://github.com/openva/crump/")
parser.add_argument('-i', '--input', help="the file to parse", metavar="addresses.csv", required=True)
parser.add_argument('-c', '--columns', help="the range of columns containing addresses", metavar="9-14", required=True)
parser.add_argument('-v', '--verbose', help="verbose mode", action='store_true')
parser.add_argument('-s', '--since', help="only geocode records updated since this date (inclusive)", metavar="YYYY-MM-DD")
args = parser.parse_args()
input_file = args.input
verbose = args.verbose
columns = args.columns
date_since = args.since
if date_since:
    date_since = time.strptime(date_since, "%Y-%m-%d")

# Specify the file and the columnar start and end. Realistically, we should be pulling this data out
# of the YAML table maps, since we specify which collections of fields are address fields. That
# would obviate the need to specify filenames or column ranges.
range = columns.split('-')
file_column_start = range[0]
file_column_end = range[1]
#input_file = "output/2_corporate.csv" #9-13
#input_file = "output/3_lp.csv" #10-14
#input_file = "output/9_llc.csv" #9-13

def main():

    # We keep track of how many consecutive errors the API throws, to stop if there are too many.
    api_error_count = 0

   # Make sure that we can connect to the database.
    try:
        db = sqlite3.connect('addresses.db')
    except sqlite3.error, e:
        print "Count not connect to SQLite, with error %s:" % e.args[0]
        sys.exit(1)

    # Create a SQLite cursor.
    cursor = db.cursor()

    # See if the addresses table already exists.
    cursor.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name='addresses'")
    exists = cursor.fetchone()

    # If the addresses table does not exist, try to retrieve it from a remote cache.
    if exists is None:

        from urllib2 import Request, urlopen, HTTPError
        url = 'https://s3.amazonaws.com/virginia-business/addresses.db'
        req = Request(url)

        try:
            f = urlopen(req)

        # If the addresses table cannot be retrieved from a remote cache, create it.
        except HTTPError as e:

            cursor.execute("CREATE TABLE addresses(address_hash TEXT PRIMARY KEY NOT NULL, "
                + "address_cleaned TEXT, latitude INTEGER, longitude INTEGER, date INTEGER, source TEXT)")
            db.commit()

        print "Downloading data from " + url
        local_file = open('addresses.db', 'w')
        local_file.write(f.read())
        local_file.close()

    # Start reading from our input file.
    with open(input_file, 'rb') as csvfile:

        # Iterate through the lines in our input file.
        csvreader = csvkit.DictReader(csvfile)
        for record in csvreader:

            if verbose:
                print "\n" + record['street_1'] + ", " + record['street_2'] + ", " + record['city'] + ", " \
                    + record['state'] + ", " + record['zip']

            # Skip records without addresses.
            if record['street_1'] == '' and record['street_2'] == '':
                if verbose:
                    print "Skipping record without address"
                else:
                    sys.stdout.write('A')
                    sys.stdout.flush()
                continue

            # Optionally skip any old records that don't need to be re-geocoded.
            if date_since:
                if record['address_date'] != '':
                    if time.strptime(record['address_date'], "%Y-%m-%d") < date_since:
                        if verbose:
                            print "Skipping old record"
                        else:
                            sys.stdout.write('S')
                            sys.stdout.flush()
                        continue
                if time.strptime(record['status_date'], "%Y-%m-%d") < date_since:
                    if verbose:
                        print "Skipping old record"
                    else:
                        sys.stdout.write('S')
                        sys.stdout.flush()
                    continue
                

            # Note the time at which we started reading this record, to make sure that we don't hit
            # the API more than per second.
            start_time = time.time()

            # Collapse our address columns and save that as a hash, to save in the database. We
            # convert to a byte str to avoid fatal Unicode-related erors for the odd problematic
            # character.
            try:
                address_hash = hashlib.md5(str(record['street_1']).encode('utf-8') \
                    + "," + str(record['street_2']).encode('utf-8') + "," \
                    + str(record['city']).encode('utf-8') + "," \
                    + str(record['state']).encode('utf-8') + "," \
                    + str(record['zip']).encode('utf-8')).hexdigest()

            # In the very rare instance of a character encoding error, just skip this record.
            except UnicodeEncodeError as exception:
                continue

            # If the hashed, combined string is already stored in the database, skip to the next
            # address.
            hash_param = (address_hash,)
            cursor.execute("SELECT 1 FROM addresses WHERE address_hash = ?", hash_param)
            exists = cursor.fetchone()
            if exists is not None:
                if verbose:
                    print "Address found in local cache"
                else:
                    sys.stdout.write('C')
                    sys.stdout.flush()
                continue

            # Remove quotation marks from street addresses.
            record['street_1'] = record['street_1'].replace('"', '')
            record['street_2'] = record['street_2'].replace('"', '')

            # If either address field starts with "C/O" or "ATTN:" drop it.
            if record['street_1'][:4].lower() == 'c/o ':
                del record['street_1']
            elif record['street_1'][:6].lower() == 'attn: ':
                del record['street_1']
            if record['street_2'][:4].lower() == 'c/o ':
                del record['street_2']
            elif record['street_2'][:6].lower() == 'attn: ':
                del record['street_2']

            # If either address 1 or address 2 contains "P[\.?][\s*])O[\.?] BOX" (or its variants),
            # then drop it.
            if 'street_1' in record:
                if record['street_1'][:6].lower() == 'po box':
                    del record['street_1']
                elif record['street_1'][:8].lower() == 'p.o. box':
                    del record['street_1']
            if 'street_2' in record:
                if record['street_2'][:6].lower() == 'po box':
                    del record['street_2']
                elif record['street_2'][:8].lower() == 'p.o. box':
                    del record['street_2']

            # If either address 1 or address 2 is blank, drop it.
            if 'street_1' in 'record' and record['street_1'] == '':
                del record['street_1']
            if 'street_2' in 'record' and record['street_2'] == '':
                del record['street_2']

            # Encode each field as a key/value pair, for our query, Key=[Value], combining address 1
            # and 2 (if both fields exist) into a single field.
            url_parameters = {}
            try:
                url_parameters['Street'] = record['street_1'] + ", " + record['street_2']
            except:
                try:
                    url_parameters['Street'] = record['street_1']
                except:
                    try:
                        url_parameters['Street'] = record['street_2']
                    except:
                        pass
            url_parameters['City'] = record['city']
            url_parameters['State'] = record['state']
            url_parameters['ZIP'] = record['zip']
            url_parameters['maxLocations'] = '1'
            url_parameters['f'] = 'json'
            url_parameters = urllib.urlencode(url_parameters)

            # Assemble the URL.
            if record['state'] == 'VA':
                request_url = 'http://gismaps.vita.virginia.gov/arcgis/rest/services/Geocoding/VGIN_Composite_Locator/GeocodeServer/findAddressCandidates?' \
                    + url_parameters
            else:
                request_url = 'http://geocoding.geo.census.gov/geocoder/locations/address?benchmark=Public_AR_Current&format=json&' \
                    + url_parameters

            # Fetch that URL.
            try:
                response = urllib2.urlopen(request_url)
            except urllib2.URLError as e:
                sys.stdout.write('‽')
                sys.stdout.flush()
                api_error_count += 1
                if api_error_count == 10:
                    print "Too many API errors: halting."
                    sys.exit()
                continue

            # Load the returned JSON as an object.
            address_data = json.load(response)

            # Since this worked, reset the API error count to zero.
            api_error_count = 0

            # If this is a Virginia record.
            if record['state'] == 'VA':
                # If no address candidates were found, then record this as a failure and skip to the
                # next line.
                if len(address_data['candidates']) == 0:
                    if verbose:
                        print "API returned no address candidates"
                    else:
                        sys.stdout.write('✗')
                        sys.stdout.flush()
                    continue

                # If the top result score is below 80, then record this as a failure and skip to the
                # next line.
                if address_data['candidates'][0]['score'] < 80:
                    ### Realistically, we need to store in the database that no solid match could be
                    ### found. Otherwise, we're going to look up this address over and over again.
                    if verbose:
                        print "API has insufficient confidence in its result"
                    else:
                        sys.stdout.write('✗')
                        sys.stdout.flush()
                    continue

                # If the address is just a ZIP (indicating that we're getting the ZIP centroid), then
                # record this as a failure and skip to the next line.
                if len(address_data['candidates'][0]['address']) < 6:
                    if verbose:
                        print "API only returned a ZIP centroid"
                    else:
                        sys.stdout.write('✗')
                        sys.stdout.flush()
                    continue

                final = {}
                final['address'] = address_data['candidates'][0]['address']
                final['longitude'] = address_data['candidates'][0]['location']['x']
                final['latitude'] = address_data['candidates'][0]['location']['y']
                final['time'] = round(time.time())

                # Note the name of the API that we're using to geocode this data.
                final['source_api'] = 'VITA'

            # If this is an extra-Virginia record.
            if record['state'] != 'VA':

                # Bring the useful contents up a level.
                address_data = address_data['result']['addressMatches']

                # If no address candidates were found, then record this as a failure and skip to the
                # next line.
                if len(address_data) == 0:
                    if verbose:
                        print "Census API returned no address candidates"
                    else:
                        sys.stdout.write('✗')
                        sys.stdout.flush()
                    continue

                final = {}
                final['address'] = address_data[0]['matchedAddress']
                final['longitude'] = address_data[0]['coordinates']['x']
                final['latitude'] = address_data[0]['coordinates']['y']
                final['time'] = round(time.time())

                # Note the name of the API that we're using to geocode this data.
                final['source_api'] = 'VITA'

            # Insert the hashed address, cleaned address, latitude, longitude, timestamp, and source
            # into the database.
            cursor.execute("INSERT INTO addresses VALUES(?, ?, ?, ?, ?, ?)", \
                (address_hash, final['address'], final['latitude'], final['longitude'], \
                final['time'], final['source_api']))
            db.commit()

            # Report success to the terminal.
            if verbose:
                print "Address geocoded"
            else:
                sys.stdout.write('✓')
                sys.stdout.flush()

            # Pause long enough that 1 second has elapsed for this loop, to avoid hammering the API.
            if record['state'] != 'VA':
                if time.time() - start_time < 1:
                    time.sleep(1 - (time.time() - start_time))

    # Close our database connection.
    db.close()

if __name__ == "__main__":
    main()