User input registration then geocoded into database

Question

I've been having quite a bit of trouble with Google Maps API and a database driven map. In a previous question I had inquired about faster geocoding of addresses to a map and basically what I need to do is store Lat/Lng into the database and create a map from there, instead of geocoding while the map is loading.

However, I'm unsure exactly how to do this. I know the basic premise - 1) User registers and enters in their address. 2) Database should store the user's information, along with the address. 3) Address needs to be geocoded and then added to the Lat/Lng fields in the database. 4)Markers can then be placed on the map by looping through a query involving the Lat/Lng fields.

Any ideas how to do this? I'm using ColdFusion, SQL Server.

Answer 1

You can use Google's Geocoding Service for this. The API is dead simple. You send an address in the url and it returns some xml with the geocoded result. Even better, there's a project on RiaForge called Google Geocoder v3 that will do all the work for you.

Here's some sample code of the CFC:

<cfinvoke component="googlegeocoder3" method="googlegeocoder3" returnvariable="variables.geocode_query1">     
  <cfinvokeargument name="address" value="1600 Amphitheatre Parkway, Mountain View, CA">
  <cfinvokeargument name="ShowDetails" value="false">
</cfinvoke>

<cfdump var="#variables.geocode_query1#">

As for when to do the geocoding, you have two options. One is just to do it when they register - take the address from the registration form, call the geocoding service to get the lat/lng and then save everything to the database. The other option is to save the address from registration and then have a separate scheduled task to periodically checks and geocodes any un-geocoded addresses.

Answer 2

Well after working with Peter's advice, and the Google Geocoder v3, I threw together my own stuff because the Google Geocoder v3 cfc wouldn't work.

        <!---Geocode the address and convert it to Lat/Lng --->
        <cfset address2geocode = '#Form.Client_Address#, #Form.Client_City#, #Form.Client_State#'>

        <cfhttp url="http://maps.google.com/maps/api/geocode/xml?address=#address2geocode#&amp;sensor=true" />
        <cfset geocodedXML = #xmlParse(cfhttp.filecontent)#>

      <!--- Add the record to the database--->
      <cfquery datasource="#dsn#" name="AddMember">
        INSERT INTO BSC_Clients (Client_Lng, Client_Lat)
        VALUES ('#geocodedXML.GeocodeResponse.result.geometry.location.lng.XmlText#', '#geocodedXML.GeocodeResponse.result.geometry.location.lat.XmlText#' )
      </cfquery> 

This will effectively take the Address + City + State given (throw all other user information in database, which isn't shown in the code) and convert that to Lat/Long values which will be stored back into the database. The google map should then show the new address via a marker