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()