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: 'http://www.mysite.com/do-the-geocode-thing.cfm',
        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 = "http://maps.googleapis.com/maps/api/geocode/json?address=#URLEncodedFormat(address)#&sensor=false">
    <cfhttp url="#api#"></cfhttp>
    <cfset response = DeserializeJSON(cfhttp.FileContent)>
        <cfset point = response["results"][1]["geometry"]["location"]>


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


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



Leave a Reply

Your email address will not be published. Required fields are marked *