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)
