Geocoding a SharePoint List Item
Bing Maps is often integrated into SharePoint sites to display different types of information, from the location of customers to advance business intelligence information. A common task that needs to be carried out at some point is geocoding the location data so it can be displayed on a map. I’ve seen many different implementations, some good and others not so much. Whenever possible you should try and geocode the data ahead of time and store the coordinates. This will improve performance and generate a lot less transactions against your Bing Maps account.
In this blog post we are going to create a SharePoint List and add the functionality to geocode an item while also updating the coordinates when editing. This will allow us to store the coordinate information right in the SharePoint List.
Creating the SharePoint List
Before we start diving into the geocoding logic we will first need to create a SharePoint List and add a geocoding button to the item form. To do this, follow these steps:
1) Create a custom list called MyLocations.
2) Once the new list should appears go to the “List Tools” tab and select “List Settings”.
3) In the settings page go to the Column section and use the “Create Column” option to create the following columns:
| Column Name | Column Type |
| Location | Single line of text |
| Latitude | Number (1, 1.0, 100) |
| Longitude | Number (1, 1.0, 100) |
4) When you are done the columns section of the list settings should look like this:
5) Now go back to the List and under the “List Tools” tab select the “Customize Form” button.
6) This will open up the item input form with Microsoft InfoPath and will look something like this:
7) Next, select the area just right of the textbox for the Location field. Expand the controls menu from the tool bar and insert a button. This will insert a button right under the textbox.
8) Right click the button and go to “Button properties”. Change the label to “Geocode”. Under the Advance tab set the ScreenTip property to “Geocode”
9) Edit the properties for the Location, Latitude and Longitude textboxes and under the advance tab give the ScreenTip property the same name as the textbox. Then publish the form. Go back to the list and open the form to add a new item. It will look like the image below. Note that clicking on the geocode button won’t do anything right now.
Adding in the Geocoding functionality
To make things easy, we are going to handle all the geocoding functionality from JavaScript. To make this reusable, we will create a JavaScript file that we can pull into the item forms for any list that has the required fields.
1) Open up notepad or the text editor of your choice and copy the following code into it. Be sure to add your Bing Maps key. Save the file as ListGeocoder.js. This code uses the Bing Maps REST geocoding service to geocode the Location field and populates coordinates of the first result into the latitude and longitude fields of our item.
var GeocodeTools = {
BingMapsKey : "YOUR_BING_MAPS_KEY",
getField : function (fieldType,fieldTitle) {
var docTags = document.getElementsByTagName(fieldType);
for (var i=0; i < docTags.length; i++) {
if (docTags[i].title == fieldTitle) {
return docTags[i]
}
}
},
GeocodeLocation : function() {
var locField = GeocodeTools.getField('input','Location');
if(locField && locField.value && locField.value != ''){
var geocodeRequest = "https://dev.virtualearth.net/REST/v1/Locations?query=" + encodeURI(locField.value) + "&output=json&jsonp=GeocodeTools.UpdateFields&key=" + GeocodeTools.BingMapsKey;
GeocodeTools.CallRestService(geocodeRequest);
}else{
alert('Invalid \'Location\' value.');
}
},
UpdateFields : function(result){
if (result &&
result.resourceSets &&
result.resourceSets.length > 0 &&
result.resourceSets[0].resources &&
result.resourceSets[0].resources.length > 0)
{
var latField = GeocodeTools.getField('input','Latitude');
var lonField = GeocodeTools.getField('input','Longitude');
if(latField && lonField){
latField.value = result.resourceSets[0].resources[0].point.coordinates[0] + '';
lonField.value = result.resourceSets[0].resources[0].point.coordinates[1] + '';
}
}
},
CallRestService : function (request)
{
var script = document.createElement("script");
script.setAttribute("type", "text/javascript");
script.setAttribute("src", request);
document.body.appendChild(script);
}
};
window.onload = function(){
setTimeout(function(){
var geocodeBtn = GeocodeTools.getField('input','Geocode');
if(geocodeBtn != null){
geocodeBtn.onclick = function(){ GeocodeTools.GeocodeLocation(); };
}
},500);
};
2) Upload this to your SharePoint site by going to Site Actions -> View All Site Content. Under the Document Libraries section choose “Site Assets” and add the ListGeocoder.js file there.
3) Once the file is uploaded, right click on it and select “Copy Shortcut”. This will get the URL to the file which you will need later.
4) Go back to your SharePoint list and under the List Tools click on the “Form Web Parts” dropdown and select “(Item) New Form”.
5) This will open up a page where we can further edit the form. Under the Page tools tab choose the option to insert a Web Part. From the panel that appears select the “Forms” category and the “HTML Form Web Part”
6) The new web part will appear above the item form and consist of a textbox and a button. Drag this web part so that it is below the input form. Then choose the option to edit this web part.
7) Press the Source Editor button. This will open up a panel that has some HTML markup. Remove all the HTML and add the following script tag information, be sure to insert the proper URL to the ListGeocoder.js file you uploaded earlier.
| https://...../SiteAssets/ListGeocoder.js |
8) Go back to the SharePoint List and repeat steps 4 to 7 for the “(Item) Edit Form” web part.
Try it out
At this point we have all the functionality we need to geocode a list item when adding or editing and item. To test it out go to the SharePoint list and select “Add new item”. In the form that opens give the item a title and set the Location property to “New York” and press the geocode button. The Latitude and Longitude values will become filled with numbers and look something like this:
Now save the item and choose the item you just created from the list and edit it. Change the location to “London” and press the geocode button. The latitude and longitude fields will update with new coordinates. If they don’t, ensure you added the HTML form web part to the Edit form.
This method of geocoding data in SharePoint is useful for scenarios where your users are manually entering data through the SharePoint portal. There may be situations where you want to geocode a large quantity of locations at once, perhaps a database of locations. In this case you will want to use the batch geocoding functionality in the Bing Spatial Data Services. This allows you to geocode up to 200,000 addresses in a single request and will take a fraction of the time it would take if you made multiple requests to the standard REST geocoding service. As an additional bonus, the batch geocoding service is a free service to all Bing Maps Enterprise customers. If you have a large number of entries being added to a list, you can leverage another approach to geocode the list by creating a workflow that runs after the list has been fully updated and batch geocodes all the ungeocoded locations in the list.
Comments
Anonymous
December 07, 2013
Does this work for 2010 versions? or just for 2013?Anonymous
January 01, 2014
I believe it works on both. I think the original version I created was for 2010.Anonymous
March 12, 2014
For fast responses to questions try using the Bing Maps forums: social.msdn.microsoft.com/.../home If you are creating a Windows Store app then take a look at my free eBook: rbrundritt.wordpress.com/my-book