def insert_map_sql(con,attr,idx,init=True): with con: cur = con.cursor() if init: # initialize table cur.execute("DROP TABLE IF EXISTS tripadvisor_latlng") create_cmd = "CREATE TABLE tripadvisor_latlng(Entry INT PRIMARY KEY AUTO_INCREMENT, \ Id INT, loc_lat DOUBLE, loc_lng DOUBLE, bounds_ne_lat DOUBLE, bounds_ne_lng DOUBLE, \ bounds_sw_lat DOUBLE, bounds_sw_lng DOUBLE, viewport_ne_lat DOUBLE, viewport_ne_lng DOUBLE, \ viewport_sw_lat DOUBLE, viewport_sw_lng DOUBLE)" cur.execute(create_cmd) if attr["street"] == "New York City, NY New York City, NY": lookupVal = attr['name'] print 'Look up by Name!!!', lookupVal else: lookupVal = attr["street"] print lookupVal if lookupVal: results = get_google_address(lookupVal) if not results: # try looking up by name 'no result, so looking up by name' results = get_google_address(attr["name"]) pdb.set_trace() if results: for result in results: loc = result['geometry']['location'] if not within_nyc_bounds(loc['lat'],loc['lng']): print 'out of bounds' print [name['short_name'] for name in result['address_components']] if 'bounds' in result['geometry']: bounds_ne = result['geometry']['bounds']['northeast'].values() bounds_sw = result['geometry']['bounds']['southwest'].values() do_str = False else: bounds_ne = ['null','null'] bounds_sw = ['null','null'] do_str = True viewport_ne = result['geometry']['viewport']['northeast'].values() viewport_sw = result['geometry']['viewport']['southwest'].values() # insert into sql if do_str: cmd = "INSERT INTO tripadvisor_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %s, %s, %s, %s, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) else: cmd = "INSERT INTO tripadvisor_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) cur.execute(cmd)
def insert_map_sql(attr,idx,init=True): con = mdb.connect('localhost', 'root', '', 'insight') with con: cur = con.cursor() if init: # initialize table cur.execute("DROP TABLE IF EXISTS tripomatic_latlng") create_cmd = "CREATE TABLE tripomatic_latlng(Entry INT PRIMARY KEY AUTO_INCREMENT, \ Id INT, loc_lat DOUBLE, loc_lng DOUBLE, bounds_ne_lat DOUBLE, bounds_ne_lng DOUBLE, \ bounds_sw_lat DOUBLE, bounds_sw_lng DOUBLE, viewport_ne_lat DOUBLE, viewport_ne_lng DOUBLE, \ viewport_sw_lat DOUBLE, viewport_sw_lng DOUBLE)" cur.execute(create_cmd) # figure out whether to look up by address or name if attr["address"]: lookupVal = attr["address"] else: lookupVal = attr['name'] print 'Look up by Name!!!' if lookupVal: results = get_google_address(lookupVal) if results: for result in results: loc = result['geometry']['location'] # check within bounds if not within_nyc_bounds(loc['lat'],loc['lng']): print 'out of bounds' print [name['short_name'] for name in result['address_components']] if 'bounds' in result['geometry']: bounds_ne = result['geometry']['bounds']['northeast'].values() bounds_sw = result['geometry']['bounds']['southwest'].values() do_str = False else: bounds_ne = ['null','null'] bounds_sw = ['null','null'] do_str = True viewport_ne = result['geometry']['viewport']['northeast'].values() viewport_sw = result['geometry']['viewport']['southwest'].values() # insert into sql if do_str: cmd = "INSERT INTO tripomatic_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %s, %s, %s, %s, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) else: cmd = "INSERT INTO tripomatic_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) cur.execute(cmd)
def insert_map_sql(con, attr, idx, init=True): with con: cur = con.cursor() if init: # initialize table cur.execute("DROP TABLE IF EXISTS tripadvisor_latlng") create_cmd = "CREATE TABLE tripadvisor_latlng(Entry INT PRIMARY KEY AUTO_INCREMENT, \ Id INT, loc_lat DOUBLE, loc_lng DOUBLE, bounds_ne_lat DOUBLE, bounds_ne_lng DOUBLE, \ bounds_sw_lat DOUBLE, bounds_sw_lng DOUBLE, viewport_ne_lat DOUBLE, viewport_ne_lng DOUBLE, \ viewport_sw_lat DOUBLE, viewport_sw_lng DOUBLE)" cur.execute(create_cmd) if attr["street"] == "New York City, NY New York City, NY": lookupVal = attr['name'] print 'Look up by Name!!!', lookupVal else: lookupVal = attr["street"] print lookupVal if lookupVal: results = get_google_address(lookupVal) if not results: # try looking up by name 'no result, so looking up by name' results = get_google_address(attr["name"]) pdb.set_trace() if results: for result in results: loc = result['geometry']['location'] if not within_nyc_bounds(loc['lat'], loc['lng']): print 'out of bounds' print[ name['short_name'] for name in result['address_components'] ] if 'bounds' in result['geometry']: bounds_ne = result['geometry']['bounds'][ 'northeast'].values() bounds_sw = result['geometry']['bounds'][ 'southwest'].values() do_str = False else: bounds_ne = ['null', 'null'] bounds_sw = ['null', 'null'] do_str = True viewport_ne = result['geometry']['viewport'][ 'northeast'].values() viewport_sw = result['geometry']['viewport'][ 'southwest'].values() # insert into sql if do_str: cmd = "INSERT INTO tripadvisor_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %s, %s, %s, %s, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) else: cmd = "INSERT INTO tripadvisor_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) cur.execute(cmd)
def insert_map_sql(attr, idx, init=True): con = mdb.connect('localhost', 'root', '', 'insight') with con: cur = con.cursor() if init: # initialize table cur.execute("DROP TABLE IF EXISTS tripomatic_latlng") create_cmd = "CREATE TABLE tripomatic_latlng(Entry INT PRIMARY KEY AUTO_INCREMENT, \ Id INT, loc_lat DOUBLE, loc_lng DOUBLE, bounds_ne_lat DOUBLE, bounds_ne_lng DOUBLE, \ bounds_sw_lat DOUBLE, bounds_sw_lng DOUBLE, viewport_ne_lat DOUBLE, viewport_ne_lng DOUBLE, \ viewport_sw_lat DOUBLE, viewport_sw_lng DOUBLE)" cur.execute(create_cmd) # figure out whether to look up by address or name if attr["address"]: lookupVal = attr["address"] else: lookupVal = attr['name'] print 'Look up by Name!!!' if lookupVal: results = get_google_address(lookupVal) if results: for result in results: loc = result['geometry']['location'] # check within bounds if not within_nyc_bounds(loc['lat'], loc['lng']): print 'out of bounds' print[ name['short_name'] for name in result['address_components'] ] if 'bounds' in result['geometry']: bounds_ne = result['geometry']['bounds'][ 'northeast'].values() bounds_sw = result['geometry']['bounds'][ 'southwest'].values() do_str = False else: bounds_ne = ['null', 'null'] bounds_sw = ['null', 'null'] do_str = True viewport_ne = result['geometry']['viewport'][ 'northeast'].values() viewport_sw = result['geometry']['viewport'][ 'southwest'].values() # insert into sql if do_str: cmd = "INSERT INTO tripomatic_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %s, %s, %s, %s, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) else: cmd = "INSERT INTO tripomatic_latlng (Id, loc_lat, loc_lng, bounds_ne_lat, bounds_ne_lng, \ bounds_sw_lat, bounds_sw_lng, viewport_ne_lat, viewport_ne_lng, viewport_sw_lat, viewport_sw_lng) \ VALUES (%d, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g, %.15g) " \ % (idx, loc['lat'], loc['lng'], bounds_ne[0],bounds_ne[1],bounds_sw[0],bounds_sw[1],\ viewport_ne[0],viewport_ne[1],viewport_sw[0],viewport_sw[1]) cur.execute(cmd)