Geocoding + Google Docs ???

[wikastudio]

I'm trying to put together a simple collaborative map that will take addresses submitted intermitently in a google form and map them on a google map.

I am using google docs for the form / spreadsheet, but I need to convert (geocode) the addresses to LAT/LONG for the google map to read.  The geocoding will need to happen live, and the page will probably get about 20 requests per day.  

Does anyone have an easy google spreadsheets script that I can use for this? Or any guidance?

Thanks!

[wikastudio]

I should add that I'm trying to do this using the Spreadsheets Map Wizard (http://gmaps-samples.googlecode.com/svn/trunk/spreadsheetsmapwizard/makecustommap.htm), but need it to read addresses rather than coordinates.  Is this possible ?

[Luke Mahé]

If you are getting a low amount of traffic then what you can do is geocode each address when you read it from the spreadsheet and then add the location to the map.

More about geocoding can be found on the docs - http://code.google.com/apis/maps/documentation/javascript/services.html#Geocoding

Hope this helps.

-- Luke

On Tue, Sep 6, 2011 at 8:38 AM, wikastudio <gabrie...@gmail.com> wrote:

I should add that I'm trying to do this using the Spreadsheets Map Wizard (http://gmaps-samples.googlecode.com/svn/trunk/spreadsheetsmapwizard/makecustommap.htm), but need it to read addresses rather than coordinates.  Is this possible ?

--
You received this message because you are subscribed to the Google Groups "Google Maps JavaScript API v3" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-maps-js-api-v3/-/DWIQCkwX6-YJ.

To post to this group, send email to google-map...@googlegroups.com.
To unsubscribe from this group, send email to google-maps-js-a...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-maps-js-api-v3?hl=en.

[wikastudio]

Luke, thanks for your reply.  That's exactly what I've been trying to do with the code below, a script that I tried to run in the spreadsheet that geocoded the address using the api and returned the coordinates to a new cell in the spreadsheet.  After loading the page a couple times the script stopped working though, saying that the status of the geocoding was undefined. I thought the geocoder might have hit a frequency limit because it the spreadsheet ran the script everytime the map read it?  Not sure if that's the problem though...  

Any thoughts on why this script isnt working?  Thx

//  GEOCODER

// Example use  =ARRAYFORMULA(getLatLongRange(D2:G202)) where column D has street addresses, 

// E has Cities, F3 has States and G3 has zip codes

//or =ARRAYFORMULA(getLatLongRange(D2:D202)) where column D has complete addresses

// Put this formula in second cell of the column where you want Latitude, Longitude pairs added.

// This works to get a series Latitude, Longitude pairs from a range of cells

function getLatLongRange(range) {

  var n = range.length;

  var m = range[0].length;

  var LLArray =new Array(n);// We want record for each row passed to us

    for (i=0; i<n; i++){

    var fullAddress ="";

    for (var j = 0; j < m; j++) {

      fullAddress = fullAddress + " " + range[i][j];

    }

    try{

      var latLong = "";

      var gc = Maps.newGeocoder();

      var geoJSON = gc.geocode(fullAddress);

      Logger.log("fullAddress is " + fullAddress );

      var lat = geoJSON.results[0].geometry.location.lat;

      var lng = geoJSON.results[0].geometry.location.lng;

      latLong = lat + ", " + lng;

      }catch (e){

        Logger.log("Geocoding Failed: fullAddress is " + fullAddress + " Status is " + geoJSON.status );

      }

    LLArray[i] =new Array(1);// We want to fill in only the current column for each row

    LLArray[i][0] = latLong;

  }

  return LLArray;  // return the answer to the cell which has the formula

}

[MymsMan]

You are only allowed to do approximately 8 geocode calls a second before you start hitting a quota limit.

Once you have stored the result of a Geocode in your spreadsheet you shouldn't need to redo the coding unless the address changes so I would suggest saving a copy of the address along with the LatLng  and checking in your code whether you really need to do the geocode. That way you should only need to process the new/changed entries.