Geo-coding in Sharepoint Lists

(2009)

Today I was thinking it would be nice to do some Geocoding in Sharepoint. Specifically, I wanted to make it so list items could have longitude and latitude fields that could be populated with a button click from the EditItem page. Geocoding is pretty easy to do with both Google and Yahoo. For my situation, Yahoo seemed more appropriate.

My first intuition was that this should be easy to do with Javascript. Just call the Yahoo Maps API with the right data and parse the response. Except.. this usage violates the same site origin policy for Javascript. Drat. Well, there are several things that can be done. If you're handy with C# and M$ technologies, you can just create a proxy on the same server. Unfortunately, I don't know the first thing about the Microsoft stack and I'm too lazy to learn. As an alternative, I opted to create a PHP proxy on another server and force it to return JSON, which skirts around the same site origin problem.

First, the code for the PHP proxy which lives somewhere besides the Sharepoint server. This proxy forwards requests to Yahoo, parses the response, and emits JSON back to the caller.

<?php
//get params from request
$appid = 'YOUR_YAHOO_APPID';
$street = $_GET['street'];
$city = $_GET['city'];
$state = $_GET['state'];

//build new request
$req = 'http://local.yahooapis.com/MapsService/V1/geocode?';
$req .= 'appid=' . $appid;
$req .= '&amp;amp;street=' . urlencode($street);
$req .= '&amp;amp;city=' . urlencode($city);
$req .= '&amp;amp;state=' . urlencode($state);

//fetch XML using cURL
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $req);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$result = trim(curl_exec($ch));
curl_close($ch);

//parse XML
$xml = simplexml_load_string($result);
$lat = $xml->Result[0]->Latitude;
$lng = $xml->Result[0]->Longitude;

//return JSON
echo $_GET['jsoncallback'] . "({lat: \"$lat\", lng: \"$lng\"})";
?>

Now, the Javascript part of this uses jQuery to make life much much easier. It basically injects a new Button onto the page. When clicked, it builds a request from the location data on the page, sends the request to the proxy, parses the response, and puts the latitude and longitude into the form fields. Then the user can click 'Save' as usual. This code needs to be added to the EditItem.aspx page for that particular list. You also need to have jQuery on the page. In my case, I just included them both as external scripts to keep my changes to .aspx pages to a minimum.

$(document).ready(function() {
    //create a new Button, match Sharepoint styles
    var geoButton = $('<input type="button">').attr({'class':'ms-ButtonHeightWidth', 'value':'GeoCode'});

    //add our Button after the default 'Cancel' Button
    $('.ms-formtoolbar .ms-toolbar:last').after($('<td>&amp;amp;nbsp;</td>').attr({'class':'ms-separator'}));
    $('.ms-formtoolbar .ms-separator:last').after(geoButton);

    //wrap Button in a Table to match Sharepoint's style
    geoButton.wrap($('<td></td>').attr({'class': 'ms-toolbar', 'no-wrap':'true'}))
         .wrap($('<table></table>').attr({'cellspacing':'0','cellpadding':'0','width':'100%'}))
         .wrap($('<tbody></tbody>'))
             .wrap($('<tr></tr>'))
              .wrap($('<td></td>').attr({'nowrap':'','align':'right','width':'100%'}));

    //onClick, perform geoCode and put Long/Lat into form fields
    geoButton.click(function() {

    //get data from form fields
    var street = $('input[title=Street]').val();
    var city =  $('input[title=City]').val();
    var state =  $('input[title=State]').val();

    //fail early if some data is absent, since we wouldn't get a good geocode
    if(street == '' || city == '' || state == '')
        return;

    //wrap data into a URL so we can do an HTTP GET
    var address = '&amp;amp;street='+street+'&amp;amp;city='+city+'&amp;amp;state='+state;

    //use jQuery.getJSON to avoid that pesky cross domain security restriction
    //?jsoncallback=? is a peculiarity required by jQuery, the server must echo this back
    $.getJSON("http://path_to/yahoo-geocoder.php?jsoncallback=?"+address,
        function(json) {
            //async callback, unpack the data
            var lat = json.lat;
            var lng = json.lng;
                //simple error detection
            if(lat == '' || lng == '') {
            //show '!!!' after form fields to indicate that geocoding failed
            $('input[title=Latitude]')
            .after($('<span>!!!</span>').attr({'id':'error-lat'}).css('color','red'));
            $('input[title=Longitude]')
            .after($('<span>!!!</span>').attr({'id':'error-lng'}).css('color','red'));
            } else {
            //success, place results into form fields
            $('input[title=Latitude]').val(lat);
            $('input[title=Longitude]').val(lng);
            //remove any previous error indicators
            $('#error-lat').remove();
            $('#error-lng').remove();
            }
        });
    });
});