Friday, May 31, 2013

How to get coordinates for geolocation field (No code solution)

Geolocation field is a great addition to SharePoint that enables us to easily give some location meaning to our data.

When we add a geolocation field to our list and want to set our location data we will see that we have two options for doing this. If our environment allows it we can use "Use my location" option or we can go with the "Specify location" option.


When we select that we want to specify location, we will get this dialog:


In order to add a specific location to our list we need to know its exact coordinates and to get them we will probably have to go to Bing Maps and search for them. It would be great if we could simple enter some search values for our location and store coordinates from search results automatically, but there is nothing like that out of the box.

So it may seem that our only option is custom development but there is a nice workaround. I will show you how you can achieve this using a SharePoint list, a new workflow feature for calling web services and Bing Maps. I will not go into details so it would be good if you would have some understanding of REST services and the new Call HTTP service action in SharePoint workflows.

First we will need a SharePoint list with a geolocation field (if you need help adding a geolocation field to your list, check out my Adding geolocation field to SharePoint Online post), I will call mine Geolocation. Also we will add one additional column to store information about our locations, I have added a text column named Place.

The idea is that when user enters location information into the Place column, Geolocation field is automatically populated with coordinates.

To achieve this we will need to create a workflow. This workflow will use Call HTTP service action to call Bing Maps Rest services to get coordinates.

This is how the workflow looks like in the end:



I will go through each step to show you how it works.

As we can see, it has two stages. In the first stage we use Bing Maps services in order to get coordinates and in second part we use them to set values in our location field.

Here is how we get our location coordinates:

1. First we need to build up the request header. For this we need to create a dictionary with two properties Accept and Content-Type, both with values application/json; odata=verbose



Save the dictionary as BingRequestHeader.

2. Bing Maps provide us with REST services we can leverage to get geolocation data. You can read more about it here Bing Maps REST Services. We will use one of this services to get coordinates based on values user inputs into the Place column.
Add the Call HTTP service action to your workflow. For the web address of the service we will use URL that looks like this:

http://dev.virtualearth.net/REST/v1/Locations?q= <lookup to your place column>&o=json&key=<Your BING Maps key>

We will set the HTTP method of the call to HTTP GET.




Next we need to set the request header of our call to the previously created BingRequestHeader dictionary. To do this, right click on the action and select properties. In the properties window you will find a place where you can add your dictionary to request headers.

Create another dictionary called GeolocationResponse. We will use this dictionary to store the response content from our call, so in the Call HTTP service method set the ResponseContent to this new variable.

3. In the response from Bing Maps service we will get a json object that contains our coordinates. We have stored this data into our GeolocationResponse dictionary. In order to get longitude and latitude we need to search this dictionary and get values from it. For this we will use Get an Item from a Dictionary action. For the path of the item  enter :
resourceSets(0)/resources(0)/point/coordinates(0)



This will give you latitude. Set the output to a new variable that we will call Latitude

4. We will get longitude the similar way like we did latitude, we will just change the path of the item  to:
resourceSets(0)/resources(0)/point/coordinates(1)
Set the output to a new variable that we will call Longitude.


With these steps we will get our coordinates from the Bing Maps. Unfortunately, there is no action that allows us to simply set our geolocation field to those coordinates from the workflow. This is what the second stage of the workflow is for. In the second stage we will also use Call HTTP service action, but in this case we will use it to post data to our geolocation field using SharePoint REST Services.

5. First we need to set up our request header as we did when we were calling Bing Maps service. Create a new dictionary SharepointRequestHeader with the same values as BingRequestHeader.

6. To be able to post data into SharePoint list we will have to create a valid object that SharePoint can process. To create this object we will need to build several dictionaries.
Since we will need to create several dictionaries I will specify its properties in this form (name| type | value). First we will build a dictionary called metadata. It contains this property :
type | String | SP.Data.LocationsListItem (note that this will depend on the title of your list)



7. Build a dictionary called GeolocationFieldMetadata with
type | String | SP.FieldGeolocationValue



8. Build a dictionary called GeolocationFieldData. It should contain following properties:

__metadata | Dictionary | (Lookup to previuosly created GeolocationFieldMetadata dictionary)
Altitude       | Number     | 0
Latitude      | Number     | (Lookup to variable Latitude)
Longitude   Number     | (Lookup to variable Longitude)
Measure      | Number     | 0



9.  Build a dictionary named body with following properties:

__metadata | Dictionary  | (Lookup to previuosly created metadata dictionary)
Geolocation | Dictionary  | (Lookup to previuosly created GeolocationFieldData dictionary)



10.  We are now ready to update our field through Call HTTP service action. To update current item we will call service from a URL that looks like this:

https://<your site>/_api/web/lists/getbytitle('Locations')/items([%Current Item:ID%])
We will use our call with POST method.



In  Call HTTP service action we need to set the request of the call to the body dictionary we created and the request header to SharepointRequestHeader dictionary


11. This step is optional. I used it to log errors and similar information from the workflow into a field I have added to the list.

You can set the workflow to start when a new item is added so you can still manually update field if you are not satisfied with the coordinates Bing Maps gave you. And thats all, just publish your workflow, insert location information in your Place column (for example : Zagreb,Croatia), wait for the workflow to finish and your geolocation field should fill up automatically.

(I plan to make a short video demo of this workflow so if I didn't explain it too good in this post check back to see if  I was able to make a video available)

13 comments:

  1. Dear Borislav,
    thank you for the great explanation. I re-built the workflow and the geodata for addresses are perfectly retrieved.
    Unfo I cannot write these to the latitude/longitude fields in the geolocation field in my list "Account".
    Please find below the variable info printed from workflow log and an error message.
    Any idea of how to to overcome this issue?
    Many thanks
    Rolf

    Retrieval result from Bing Maps:
    Latitude: 53.574462
    Longitude: 9.888506

    SharepointRequestHeader: {"Accept":"application\/json; odata=verbose","ContentType":"application\/json; odata=verbose"}

    metadata: {"type":"SP.Data.AccountListItem"}

    GeolocationFieldMetadata: {"type":"SP.FieldGeolocationValue"}

    GeolocationFieldData: {"__metadata":{"type":"SP.FieldGeolocationValue"},"Altitude":0,"Latitude":53.574462,"Longitude":9.888506,"Measure":0}

    Body: {"__metadata":{"type":"SP.Data.AccountListItem"},"Geolocation":{"__metadata":{"type":"SP.FieldGeolocationValue"},"Altitude":0,"Latitude":53.574462,"Longitude":9.888506,"Measure":0}}

    This is the SP Api call:
    https://myWebsite/_api/web/lists/getbytitle('Account')/items([7)

    ***Comment: the list is called "Account". it is strange that the item ID always has a leading "[" in the URL.

    This is the error message:
    GeolocationResponse: {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The parameter __metadata does not exist in method GetById."}}}

    ReplyDelete
  2. Hello DerKaptein,
    you are right that the a leading "[" is strange. Can you please check you Call HTTP service metod and see what exact adress you are calling?

    ReplyDelete
  3. Has anyone resolved the error DerKaptein experience? I've tried numerous workarounds, added the RequestDigest information to the Header, but still the same error persists.

    ReplyDelete
  4. I have created another post on how to get location data for your geolocation field using JavaScript, it also works on SP Online and I belive it is a better and easier way, so be sure to check it out http://mysharepointinsight.blogspot.com/2013/12/how-to-get-coordinates-for-geolocation.html

    ReplyDelete
  5. Hello,
    I found interesting your project, just gets to me.
    The first step is get lew coordinate work, but the second gives me error.
    Can you give me a hand?
    Use SharePoint 2013 Online

    Body
    {"__metadata":{"type":"SP.Data.ListaProvaListItem "},"Locazione":{"__metadata":{"type":"SP.FieldGeolocationValue"},"Altitude":0,"Latitude":0,"Longitude":0,"Measure":0}}

    https://myWebsite.sharepoint.com/_api/web/lists/getbytitle('ListaProva')/items(1)

    {"error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"it-IT","value":"Il parametro __metadata non esiste nel metodo GetById."}}}

    ReplyDelete
    Replies
    1. Hi,

      I found it hard to debug and use REST services from the workflow when it is not a custom made workflow from VS. Because of that I have abandoned this method. I did another post on how you can use Javascript to get the location coordinates, you can read it hear :http://mysharepointinsight.blogspot.com/2013/12/how-to-get-coordinates-for-geolocation.html
      I hope that will help you.

      Delete

    2. Hello,
      thanks for reply.
      I had already seen your new project.
      Your old design is ideal for me.
      I have attached the list sharepoint using Access 2013 importanto data from external software.
      Every night this list is cleared and repopulated, update it flows through the field location.
      Do you have any idea?
      I'm no expert

      Delete
    3. As I said it is hard to debug such a workflow, as it can be a number of issues.
      Are you sure that your list contains an item with the ID=1? Try hardcoding an ID value in your workflow, one for which you are sure that it exists in your list.
      Also try using some other tool to test your REST calls, for example I use POSTMAN extension for Google Chrome.

      Delete
    4. Gozilla did you find a solution? I would like to use this method to take already known coordinates and add them to the geolocation field.

      Delete
  6. Hi Borislav Grgić,

    I found one error while adding the data in REST URL using post method but while using POST method HTTP Response Code is showing Bad Request.

    Below are the log massage of Dictionary:

    Metadata list:{"Type":"SP.Data.GeolocationListItem"}
    Metadata Fields:{"Type":"SP.FieldGeolocationValue"}
    Geolocation Fields data:{"__metadata":{"Type":"SP.FieldGeolocationValue"},"Latitude":42.962735480990361,"Longitude":-85.635970489716755,"Altitude":0,"Measure":"0"}
    Body:{"__metadata":{"Type":"SP.Data.GeolocationListItem"},"Geolocation":{"__metadata":{"Type":"SP.FieldGeolocationValue"},"Latitude":42.962735480990361,"Longitude":-85.635970489716755,"Altitude":0,"Measure":"0"}}

    ReplyDelete
  7. We got it working by changing SP.Data.AccountListItem to SP.Data.AccountItem where Account is the name of your List/Library.

    ReplyDelete
    Replies
    1. We also added the following to the SharePointRequestHeader :-
      X-HTTP-Method | string | MERGE
      If-Match | string | *

      Delete
    2. re: the parameter _metadata. I missed updating the Request Headers property. "right click on the Call HTTP service" to update the Request headers to the variable (Either BingRequestHeader dictionary or SharePointRequestHeader Dictionary). It's mentioned in step 2. Great post, thank you Borislav

      Delete