Okay, that title was a mouthful, but I wanted to accurately describe how decided to Geocode thousands of locations for a client of mine.
Google, awesome as they are, have offered a ridiculously simple API to consume:
http://maps.googleapis.com/maps/api/geocode/json?address=theaddress&sensor=false
But, they naturally have rate limits, and even if they didn't, I'd feel more comfortable using their API responsibly. It is completely free after all.
So, I decided to write a ColdFusion script which does the following:
- Pulls a single location record from a database
- Sends the address from the record to the Google API
- If the API returns a result, store it in the database
- Otherwise, indicate that geocoding failed, so we don't attempt to geocode this record again.
Since we're only doing one record at a time, we'll have to set this up to run automatically at a specified interval. This is where the JavaScript part comes in.
I could have setup a cron job without much effort, but knowing me, I'd forget to remove it once geocoding is complete. So, I decided to open up my web browsers console, and drop in the following:
setInterval(function() {
$.ajax({
url: 'http://www.mysite.com/do-the-geocode-thing.cfm',
success: function(data) {
console.log(data);
}
})
}, 35000);
The 35 second interval is enough to comply with Google's 2,500 query/day limit. The ColdFusion script follows below:
<cfquery name="locations">
SELECT id, address
FROM locations
WHERE latitude = 0
AND failed_geocode = 0
LIMIT 1
</cfquery>
<cfloop query="locations">
<cfset api = "http://maps.googleapis.com/maps/api/geocode/json?address=#URLEncodedFormat(address)#&sensor=false">
<cfhttp url="#api#"></cfhttp>
<cfset response = DeserializeJSON(cfhttp.FileContent)>
<cftry>
<cfset point = response["results"][1]["geometry"]["location"]>
<cfoutput>#SerializeJSON(point)#</cfoutput>
<cfquery>
UPDATE locations
SET latitude = <cfqueryparam value="#point.lat#">,
longitude = <cfqueryparam value="#point.lng#">
WHERE id = <cfqueryparam value="#locations.id#">
</cfquery>
<cfcatch>
<cfquery>
UPDATE locations
SET failed_geocode = 1
WHERE id = <cfqueryparam value="#locations.id#">
</cfquery>
</cfcatch>
</cftry>
</cfloop>