def GeocodeDatabase(user,password):
sleep_time = 0.15
OverQueryLimitFlag = 0
#the following line will need to change
con = mdb.connect('localhost', user, password, 'SheriffSales')
with con:
databasename=_Table
cur = con.cursor(mdb.cursors.DictCursor)
curUpdate = con.cursor(mdb.cursors.DictCursor)
#for this database all records will need to be geocoded
resultcount=int(cur.execute("SELECT id,LOCATION FROM %s WHERE Latitude is NULL"% (_Table)))
print("Need to geocode "+str(resultcount)+" addresses.")
rows = cur.fetchall()
counter=0
out_file_failed = _Outfile
outf_failed = open(out_file_failed,'w')
ComputeFinishTime(sleep_time,resultcount)
for row in rows:
counter+=1
print("Geocoding "+str(counter)+" of "+str(resultcount)+" addresses.")
addr=row["LOCATION"]+"DAYTON OHIO"
if OverQueryLimitFlag == 0:
geocode_data=geocodeV2(addr)
if geocode_data['status']=="OK":
print(geocode_data)
lat=geocode_data['lat']
lon=geocode_data['lng']
curUpdate.execute("UPDATE %s SET Latitude=%s, Longitude=%s WHERE id=%s" % (_Table,lat,lon,row["id"]))
elif geocode_data['status']=="OVER_QUERY_LIMIT":
print("Over Query Limit Notification Received")
OverQueryLimitFlag = 1
else:
print("Geocoding of '"+row["LOCATION"]+"' failed with error code "+geocode_data['status'])
outf_failed.write(row["LOCATION"]+'\n')
outf_failed.flush()
else:
break
time.sleep(sleep_time)
outf_failed.close()
con.commit()
cur.close()
con.close()
def GeocodeDatabase(user,password):
sleep_time = 0.15
con = mdb.connect('localhost', user, password, 'SheriffSales')
with con:
databasename='RealEstateSalesMontgomeryCountyOhio2013'
cur = con.cursor(mdb.cursors.DictCursor)
curUpdate = con.cursor(mdb.cursors.DictCursor)
# resultcount=int(cur.execute("SELECT id,PARCELLOCATION FROM RealEstateSales WHERE Latitude is NULL"))
# print("SELECT id,PARCELLOCATION FROM %s WHERE Latitude is NULL",(databasename))
# resultcount=int(cur.execute("SELECT id,PARCELLOCATION FROM %s WHERE Latitude is NULL",(databasename)))
resultcount=int(cur.execute("SELECT id,PARCELLOCATION FROM RealEstateSalesMontgomeryCountyOhio2013 WHERE Latitude is NULL"))
print("Need to geocode "+str(resultcount)+" addresses.")
rows = cur.fetchall()
counter=0
out_file_failed = 'geocode_failed.txt'
outf_failed = open(out_file_failed,'w')
ComputeFinishTime(sleep_time,resultcount)
for row in rows:
counter+=1
print("Geocoding "+str(counter)+" of "+str(resultcount)+" addresses.")
if 1==1:
addr=row["PARCELLOCATION"]+"DAYTON OHIO"
geocode_data=geocodeV2(addr) #I wrote a geocodeV2 module that is in the SheriffSaleProcessors directory that could be used here
#if len(geocode_data)>1:
if geocode_data['status']=="OK":
lat=geocode_data['lat']
lon=geocode_data['lng']
# curUpdate.execute("UPDATE %s SET Latitude=%s, Longitude=%s WHERE id=%s", (databasename,lat,lon,row["id"])) #SELECT count(*) FROM Property WHERE Latitude is NULL
curUpdate.execute("UPDATE RealEstateSalesMontgomeryCountyOhio2013 SET Latitude=%s, Longitude=%s WHERE id=%s", (lat,lon,row["id"])) #SELECT count(*) FROM Property WHERE Latitude is NULL
else:
print("Geocoding of '"+row["PARCELLOCATION"]+"' failed with error code "+geocode_data['status'])
outf_failed.write(row["PARCELLOCATION"]+'\n')
outf_failed.flush()
time.sleep(sleep_time)
outf_failed.close()
con.commit()
cur.close()
con.close()
