Geocoding by Google Spreadsheets

Tony Hirst published a highly inspiring article about doing a Google Maps mashup without JavaScript.

I would like to show that Google Spreadsheets can do geocoding as well as Pipes.

There is http-version of Google Maps geocoder. Send a request:

http://maps.google.com/maps/geo?q=1600+Amphitheatre+Parkway,+Mountain+View,+CA&output=xml

and you get a full-blown XML response. With parameter output=csv you get a simplified response

200,6,42.730070,-73.690570 Status, accuracy, lat, lng, as a four cell csv text response,  yahoo!

Google Spreadsheets can import csv-data by  =ImportData()formula. So why don't we make it fetch coordinates of the address in the next cell.

The order of the parameters is not convenient for us, so we change it. The query-parameter 'q' shall be the last one. We type in the first cell of Spreadsheets:

http://maps.google.com/maps/geo?output=csv&q=

In the next cell we type the requested address like Helsinki.

The third cell will add together the two previous cells and ask for data by ImportData() formula:

=ImportData(CONCATENATE(A2,B2))

When you finished typing that, you will see a miracle in the next cells: '200' (http status), '4' (Google accuracy parameter), 60.169879 (latitude of Helsinki) and 24.938408(longitude of Helsinki). Did we ask anything more?

And they say that there is no geocoding in Google Spreadsheets. Plus think how it can import and export. Hold my hat!

There is also automatic googling to get input data.

=GoogleLookup(entity,attribute)

can fetch your POI (entity) address(attribute), and you geocode that as above.

You can use the output via a variety of feeds or read it by ajax-api as JSON. You can for instance preload your pages GClientGeocoder cache to know the POIs. Your visitors don't find the POI by geocoding unless you took care of it.

A test spreadsheet and its csv-feed.

What happens when all the pages read each other in a loop.

Batch geocoder with CSV output

Geocoding a list of addresses as a batch can be done faster than some times ago. Still you cannot send a bunch of geocode requests by GClientGeocoder at one moment. They must be done one by one. Just wait that the former request is answered before you send a new one.

This is a batch geocoder page that takes a line feed separated list of addresses. Many address lists don't contain country or state names if they are constant. There are even lists without town mentioned. Those missing constant names can be added in the [extension] field of the page.

Paste your address list and hit [geocode]. The geocoded coordinates will be listed on output field and corresponding markers are placed on map. Non-found addresses will be listed on error report field.

Reverse GClientGeocoder

Pamela announced a few hours ago that the much requested feature is here. Great. I made a random click on the example page and saved the server response.

_xdc_._1fmnhtk4s && _xdc_._1fmnhtk4s({
"name":"-122.124367,37.436975",
"Status":{
 "code":200,
 "request":"geocode"
},
"Placemark":[{
 "id":"p1",
 "address":"870 Bruce Dr, Palo Alto, CA 94303, USA",
 "AddressDetails":{
   "Country":{
     "CountryNameCode":"US",
     "CountryName":"USA",
     "AdministrativeArea":{
       "AdministrativeAreaName":"CA",
       "Locality":{
         "LocalityName":"Palo Alto",
         "Thoroughfare":{
           "ThoroughfareName":"870 Bruce Dr"
         },
         "PostalCode":{
           "PostalCodeNumber":"94303"
         }
       }
     }
   },
   "Accuracy": 8
 },
 "Point":{
   "coordinates":[-122.124557,37.437032,0]
 }
}]
})

I don't know who's address that is but you can see the structure.

The query is given conveniently as a GLatLng() to the GClientGeocoder.getLocations() as you can see in the updated documentation.

So the client side script makes the decision wheter we send geocode or reverse-geocode request. That is clever.

The new feature is a perfect building brick for my next csv generator page. I was just intending to publish the page!

See a temporary test page.

GDraggableObject

I had to update the contex menu of the editor of previous post. The idea of using infowindow as a context menu was bad. It covers the map and the area of interest is just beneath the infowindow.

I wrote a function that creates a popup block with hide() and show() methods. The block is made a GDraggableObject so that it will no more be on your way.

There has been a trouble with text input fields in a GDraggableObject.  No matter how hard you try to click it, the cursor doesn't stay in the field and you cannot enter anything. Suddenly the solution was found.

TIP! Add to the properties of the text input:
onmouseover="this.focus()";

Driving directions were also added to the editor.

A few hours later:

Drop down selector is another problematic element on draggable block.  The block gets dragged unintentionally when the scrollbar is dragged. Actually all the elements with scrollbars do the same.

Fortunately GDraggableObject has some handy methods. disable() freezes the block and enable() makes it draggable again. The scrollbar problem was solved by disabling draggability on selector mouseover.

TIP! Add to the properties of a draggable selector:
onmouseover = draggableObject.disable();

onmouseout = draggableObject.enable();

After a few hours of sleep:

I noticed that Chrome doesn't build the selector options dynamically in the way I been using for a long time.

selector.options[index] = new Option(value);

Chromes dom inspector shows the generated options but they are not rendered! So the trouble is in Chrome. Lets go on.

Polyline editor, csv output

A polyline editor is the first page that uses the CSV parser of the last post. At least I have not heard of any other.

The online editor uses the GPolyline.enableDrawing() method that came with api v2.111. The behaviour is similar to MyMaps. The line data is printed on a textarea in CSV format.

The csv parser is used for redrawing the polyline from generated code. You can come back with your csv file and go on editing.  That is a client side application aka web page. So you have to copy/paste the cvs text to your own text editor and save it.

Why csv? The reasons are still the same as they were in 1967.  It is still the interchange format with widest support. Also people are asking for it. It is the easiest to understand and edit. You can use it with any character set. A syntax error will not corrupt the whole file. The list goes on..

There is no csv standard. The concept is so simple that there was never a need for a written standard. Instead there is an informational RCF document 4108 from 2005!

The data validation I am using at the moment is super simple. If either of the excpected coordinate cells includes NaN data, the whole line is skipped. You don't even need a separate empty line skipping. I have not managed to write a bad line that would confuse the other lines.

The current parser is even slightly simplier than the one in previous post. I will not update the previous post. See the source of the editor page.

A series of csv based pages follows.

CSV parser

Comma separated text file CSV  is a lightweight alternative to XML/KML and JSON. It is extremely simple to create and maintain by a text editor. All the spreadheets and databases can export CSV.

GPS POI files are widely available around the web in CSV format. No wonder that many people are trying to display those files on Google Maps. The code of my old page is so messy that not many people have managed to use it as a template. I am just finishing a cleaner page but actually the CSV parsing was one the main troubles.

Basic CSV is extremely simple to parse. Split the file into lines by split("\n") and split the lines by split(","). That's it, BUT. In real CSV the strings (that are separated by commas) may also contain commas if the string is quoted by double quotes. How not to split those quoted strings?

Four hours of googling and 100+ articles, discussions and snippets didn't bring a script but an idea how to do it.

First split the line by quote. Then find the odd-indexed splits by if(i%2) and replace its commas temporarily with something like "::::". Do the usual splitting by comma and after that replace the original commas back.

Four hours googling plus one hour of code writing and testing gave results. A CSV parser that seems to work perfect.

/**

 * parseCsv()

 * @return an array of GLatLng() objects

 * @author Esa 2008

 */

String.prototype.parseCsv = function(opt_options){

  var results = [];

  var opts = opt_options||{};

  var iLat = opts.lat||1;

  var iLng = opts.lng||0;

  var lines = this.split("\n");

  for (var i=0; i

    var blocks = lines[i].split('"');

    //finding commas inside quotes. Replace them with '::::'

for(var j=0;j

      if(j%2){

        blocks[j]=blocks[j].replace(/,/g,'::::');

      }

    }  //@author Esa 2008, keep this note.

    lines[i] = blocks.join("");

    var lineArray = lines[i].split(",");

    //skip empty lines

    if(lineArray.length>1){

      var lat = lineArray[iLat]*1;

      var lng = lineArray[iLng]*1;

      var point = new GLatLng(lat,lng);

      //after splitting with commas, we put hidden ones back

 for(var cell in lineArray){

        lineArray[cell] = lineArray[cell].replace(/::::/g,',');

      }

 point.textArray = lineArray;

 results.push(point);

    }

  }

  return results;

}

Note that the array of a line cells is attached as .textArray property to the GLatLng() object.

Sprites

GIcon() property .sprite was discovered in the api code two months ago but no-one has presented any use for that. It is an object with two properties .image and .top.

sprite.image is uri of an image and sprite.top sets its style.top relative to the marker container.

You can have a set of markers in a single image file and bring one icon at a time visible by .top.

Hosting a large set of markers is not fun. A single file will do now. Also you don't have to preload separate image files anymore.

See a demo.

Update:

There are also 'width' and 'height' properties. Those can be used if you did not specify GIcon.size. You can have different sized icon images in a single sprite. Thanks Mike.