coldfusion geocode

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:

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() {
        url: '',
        success: function(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

<cfloop query="locations">
    <cfset api = "">
    <cfhttp url="#api#"></cfhttp>
    <cfset response = DeserializeJSON(cfhttp.FileContent)>
        <cfset point = response["results"][1]["geometry"]["location"]>


            UPDATE locations
            SET latitude = <cfqueryparam value="">,
                longitude = <cfqueryparam value="#point.lng#">
            WHERE id = <cfqueryparam value="">


            UPDATE locations
            SET failed_geocode = 1
            WHERE id = <cfqueryparam value="">


Why follow me on Twitter?

  • I tweet about new technologies, services or libraries I find interesting
  • Yeah, sometimes I'll post a pet-peeve or rant about something trivial
  • If I discover something that made my web development life easier, I share it
  • I'll shout out any handy tip that I think might be useful to other devs

Tagged .

Updated: 2013-08-27

Phil LaNasa follow us in feedly