Contour Matrix Generator - MySQL

build_matrix.py

import csv
import time
import MySQLdb

def getCursor():

    conn = MySQLdb.connect(host = "host", user = "username", passwd = "password", db = "db_name")
    curs = conn.cursor()

    return curs

def getDates():

    start_date = '2012-02-01'
    end_date = '2012-02-29'

    return (start_date, end_date)

def createTempTables(curs):

    curs.execute("""CREATE TEMPORARY TABLE tmp.base(lat_key INT, lng_key INT, INDEX (lat_key,lng_key))""")
    curs.execute("""CREATE TEMPORARY TABLE tmp.target(lat_key INT, lng_key INT, cnt INT, INDEX (lat_key,lng_key))""")


def runQueries(curs, start_date, end_date):


    #world
    lat_NW = 90.0
    lng_NW = -180.0
    lat_SE = -90.0
    lng_SE = 180.0

    # About 20 miles
    lat_incr = -.29
    lng_incr = .29

    lat_curr = lat_NW
    lng_curr = lng_NW
    insert_string = []
    dict_matrix = {}
    contour_matrix = [[]]

    while lat_curr > lat_SE:

        lng_curr = lng_NW

        while lng_curr < lng_SE:

            lat_index = abs((lat_NW - lat_curr)  / lat_incr)
            lng_index = abs((lng_NW - lng_curr)  / lng_incr)

            insert_string.append("("+ str(lat_index) + "," + str(lng_index) + ")")

            lng_curr += lng_incr

        lat_curr += lat_incr


    curs.execute("""   INSERT INTO tmp.base(lat_key,lng_key)
                       VALUES %s""" % ",".join(insert_string))

    curs.execute ("""  INSERT INTO tmp.target
                       SELECT
                       FLOOR(ABS((%s - a.latitude)  / %s)) as lat_key,
                       FLOOR(ABS((%s - a.longitude) / %s)) as lng_key,
                       COUNT(*)
                       FROM account a
                       WHERE
                       a.created_at >= '%s 00:00:00'
                       AND a.created_at <= '%s 23:59:59'
                       AND a.latitude <=  %s #lat_NW
                       AND a.latitude >  %s #lat_SE
                       AND a.longitude >= %s  #lng_NW
                       AND a.longitude <  %s #lng_SE
                       GROUP BY 1,2
                       """ % ( lat_NW, lat_incr, lng_NW, lng_incr, start_date, end_date, lat_NW, lat_SE, lng_NW, lng_SE ))


    curs.execute("""   SELECT b.lat_key, b.lng_key, COALESCE(cnt,0)
                       FROM tmp.base b
                       LEFT OUTER JOIN tmp.target t ON t.lat_key = b.lat_key
                           AND t.lng_key = b.lng_key""")

    result = curs.fetchall()

    for line in result:
        (lat_key, lng_key, cnt) = line
        if not lat_key in dict_matrix:
            dict_matrix[lat_key] = []

        dict_matrix[lat_key].append(cnt)

    for cnt in dict_matrix:
        contour_matrix.append(dict_matrix[cnt])

    return contour_matrix

def exportData(data, filename):

    writer = csv.writer(open(filename,'wb'),delimiter=',',lineterminator='\n')

    writer.writerows(data)

if __name__ == '__main__':
    start_time = time.time()
    curs = getCursor()
    createTempTables(curs)
    (start_date, end_date) = getDates()
    contour_matrix = runQueries(curs, start_date, end_date)
    exportData(contour_matrix, 'contour_matrix.csv')
    end_time = time.time()
    print "took %s seconds" % (end_time - start_time)