Batch Geocoding with ColdFusion, Google Maps API and JavaScript

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:

1. Pulls a single location record from a database
2. Sends the address from the record to the Google API
3. If the API returns a result, store it in the database
4. 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>