Code for bulk geocoding

[bjacobson]

Here's some code I've used for bulk geo-coding a lot of addresses. It's basically Martin's code from the Wiki, with a typo correction and some error-checking. I found that the sample did not deal well with records that had missing address. Also since most databases will not have a field with the complete address, it adds that step. Hope this is useful.

------------------------


dim cn as sql::Connection
flag = cn.open("::Name::YOURDATABASE")
dim sql as c
'execute a query to get the primary key of each record you want to update
sql = "select ID from YOURTABLE"
cn.Execute(sql)
rs = cn.ResultSet

dim flag as l

'check to see if any rows in resultset
flag = rs.nextRow()

delete args
dim args as sql::Arguments
dim sql1 as c

' assumes you have fields latitude and longitude in your table
sql1 = "select address, city, state, country, postalcode from YOURTABLE where ID = :primaryKey"
sql2 = "update YOURTABLE set latitude = :lat, longitude = :lon where ID = :primaryKey"
dim pa as p
dim rs2 as sql::ResultSet
dim fulladdress as c
dim city as c
while flag
args.add("primaryKey",rs.data(1))
flag = cn.Execute(sql1,args)
rs2 = cn.ResultSet
fulladdress = rs2.data("address")+ "," + rs2.data("city")+ "," + rs2.data("state")+ "," + rs2.data("country")
city = rs2.data("city")
if city <> ""
pa = geocode_address(fulladdress,"Google")
args.add("lat",pa.lat)
args.add("lon",pa.lon)
flag = cn.execute(sql2,args)
end if
flag = rs.nextRow()

end while

[dionmccormick]

Re: Code for bulk geocoding

Thank you for your contribution. Great code!

[TPeterson]

Re: Code for bulk geocoding

I have a grid in a tabbedui that shows our employee home addresses, phone numbers, etc. I created an alternate view to include the Google map component so that the employees home locations could be pinned on a map based on their address. Some problems I'm having include addresses that get mapped sometimes, but not all the time, some that don't get mapped at all, and I receive the Google query limit warning. I think I need to add latitude and longitude fields to my SQL Server DB and utilize the code above...which brings me to my questions.

1. Can someone explain where the code should be placed, and how it is run? (action button?)
2. Which SQL Server data type would be recommended to use for fields for Longitude and Latitude?

Thanks very much.

[TPeterson]

Re: Code for bulk geocoding

Still waiting on any responses to my questions, but in the meantime, I came across a good site (URL below) that converts addresses to longitude/latitude, either one at a time or in a batch mode.

http://stevemorse.org/jcal/latlon.php

[bjacobson]

Re: Code for bulk geocoding

Hi. Sorry about the slow response. I run code in two different places:

1) As a one-time event, I ran the code above on all my existing records to update them with latitude/longitude. I created a script (from the Control Panel) and ran it from there. (I did some tests first with a copy of the real table). I don't know that it really matters much on the data type on the back end. I used strings (varchar(50), and that works fine.

2) To set these fields for new / updated records, I have short piece of code in the BeforeValidateExistingRecords and BeforeValidateNewRecords server events. The code is below. Hope this helps.

-------------------------

function BeforeValidateExistingRecords as v (DataSubmitted as P, Args as p, PageVariables as p)
with PageVariables

dim fulladdress as C
dim coords as P

fulladdress = DataSubmitted.address + " " + DataSubmitted.city + ", " + DataSubmitted.state + " " + DataSubmitted.country
coords = geocode_address(fulladdress, "Google")
if coords
DataSubmitted.latitude = coords.lat
DataSubmitted.longitude = coords.lon
end if

end with
end function

[TPeterson]

Re: Code for bulk geocoding

Barry, thanks a lot. I'm looking to working with your code later today...after my day job!

[alexismoore]

Re: Code for bulk geocoding

Same here. Found this site called https://csv2geo.com.
I even tested it. It does appear to accept bulk addresses in either flat file or via paste. I tested the free option, bu the paid is dirt cheap from what i saw.