map
SharePoint 2013 comes with new features for plotting SharePoint list items on a Bing map. However, it isn’t completely intuitive how to accomplish this, and once you figure it out, it is still a lot of work. This post will look at using PowerShell to quickly geocode a full list of SharePoint list items with address data.

You can add a geolocation field to any SharePoint list, but for some reason you can’t do it through the UI. I’ll put the code here to create the column using PowerShell, but for a full explanation and tons of other information, check out this post by Tobias Zimmergren.

1 Add-PSSnapin Microsoft.SharePoint.PowerShell 2 3 $web = Get-SPWeb "http://yourWebApplication" 4 $list = $web.Lists 5 $list.Fields.AddFieldAsXml( 6 "<Field Type='Geolocation' DisplayName='Name for your column'/>", 7 $true, 8 [Microsoft.SharePoint.SPAddFieldOptions]::AddFieldToDefaultView)

So, now you have a geolocation field in your list.  If you edit an existing item, you will notice that there are a few links you can use to get your item ready for your map. 

image

If you click the “Specify Location” link, you get two fields for you to enter the latitude and longitude.  This is not very friendly, and if you are expecting your users to know how to get this information, you have smarter users than most.  You can use the “Use My Location” link, but that isn’t likely going to work for what you need it to do.

So, how do we make this actually useful?  The PowerShell script below can be run against the list with your address data.  This will use the Bing address geocode API, so you will have to register for an API key.  There is more information on that on the Bing maps developer site.  The script below will take your address data from all items in the list and process it through the Bing API to automatically populate the latitude and longitude values for your list.  Once you have this, you can create maps out-of-the-box with SharePoint.

1 $web = get-spweb "http://path/to/your/web" 2 $list = $web.Lists 3 4 foreach($item in $list.Items){ 5 write-host "Updating Item "$item 6 $url = "http://dev.virtualearth.net/REST/v1/Locations/US/$($item)/$($item)/$($item)/$($item)?o=xml&key=YOUR-BING-API-KEY" 7 8 [xml]$result = $webclient.DownloadString($url) 9 if($result.Response.StatusCode -ne "200") { 10 write-host "API Error "$result.Response.StatusCode -ForegroundColor Red 11 Break 12 } 13 $lat = $result.Response.ResourceSets.ResourceSet.Resources.Location.Point.Latitude 14 $long = $result.Response.ResourceSets.ResourceSet.Resources.Location.Point.Longitude 15 16 $item = "Point ($($long) $($lat))" 17 $item.Update() 18 write-host $url 19 } 20 $web.dispose() 21 write-host "Done" -ForegroundColor Green

This script assumes that your address is stored in columns named Address, City, State and Zip.  It also assumes your geolocation field is named GeoLocation.  Update these names in lines 6 and 16 if your column names are different.

After you have a geolocation field in your list, when you go to create a new view, there is an option to create a map view. 

image

You can include any columns you want, but you must include your geolocation field.  Once you have the view created with your settings, you get a pretty decent looking map without having to do too much effort:

image

For more information about C5 Insight or this blog, please Contact Us.