Wednesday 18 December 2013

6 Showing Addresses on a Google Map

In this example, we wish to show on a  the locations of certain schools in Calcutta whose addresses are known. We begin with a primary source of data and copy the same into a Google Docs spreadsheet. This base data is then copied into a another sheet for pre-processing. This activity is partially manual where we cut, copy and paste the data for 10 schools into one portion of the spreadsheet so that the school name and school address of the schools in question appear adjacent to each other -- in columns C, D of this sheet.

Converting addresses to latitude and longitude is known as GeoCoding and Google Maps provides APIs for this. However we have used a simple Google Apps Script adapted from one found in Amit Agarwals Programmer's Library to calculate the latitude and longitude corresponding to each school address.

Running this script ( the listing of which is given in this post)  generates the positions and these are visible in columns F and G of the pre-processing sheet.

The final, clean data consisting of the latitude, longitude and school name is now copied into the final GeoCodeData sheet for transmission to the HTML page that will display the map.

From here onward, we use the templates that Google provides for Map Visualisation with the exception that in this case our data is not hard coded into the HTML page but is being picked up from the spreadsheet.

The HTML code for this is shown in the post below. Note that we have specified the

  • spreadsheet - through the value of the key, that can be picked up from the URL
  • sheet - in this case sheet=GeoCodeData
  • range - B2:D11
  • there are no headers
  • we use columns B,C,D where B = lat, C= long and D is a text field, in this case school name


This is the map that is produced


We observe that the location of certain schools have not been shown correctly though they are by and large in the general area. This is because of a lack of precision in geocoding because Google could not identify the exact address and settled for a general area based perhaps on the name of the road or the PIN code. This can be manually improved by going to the real Google Map and zooming repeatedly until the address is visible and noting the precise lat/long of that address.

This is the GoogleScript code used in Google Docs to GeoCode addresses into lat/long positions

function pm_geocode_Addresses() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var locationInfo = sheet.getRange(2, 4, 10, 1).getValues();
  var geoResults, lat, lng;
 
  for (var i = 0; i < locationInfo.length; i++) {
    geoResults = Maps.newGeocoder().geocode(locationInfo[i]);
 
    // Get the latitude and longitude
    lat = geoResults.results[0].geometry.location.lat;
    lng = geoResults.results[0].geometry.location.lng;
    sheet.getRange(i+2, 6, 1, 1).setValue(lat);
    sheet.getRange(i+2, 7, 1, 1).setValue(lng);    
    Utilities.sleep(1000);    
  }
}


This is the HTML code that is embedded in this blog

<head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["map"]});
      google.setOnLoadCallback(drawMapBU);
      function drawMapBU() {
        var query = new google.visualization.Query(
      'https://docs.google.com/a/yantrajaal.com/spreadsheet/ccc?key=xxxxxxxxxx&sheet=GeoCodeData&range=B2:D11&headers=0');
      query.setQuery('select B,C,D'); 
      query.send(handleQueryResponseBU);;
      
      function handleQueryResponseBU(response) {
       if (response.isError()) {
         alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
       }
 
      var data = response.getDataTable();
      var map = new google.visualization.Map(document.getElementById('map_divBU'));
      map.draw(data, {showTip: true});
       }     
      }
    </script>
  </head>
  <body>
    <div id="map_divBU" style="width: 600px; height: 500px"></div>
  </body>


For some reason, the map on this blog is in black and white even though on the normal HTML page the colours are showing properly. What can be reason for this ? If anyone knows, will appreciate a suggestion.

No comments:

Post a Comment