Wednesday 24 September 2014

WebAPI Odata v3 Geo.Distance Spatial Function


WebAPI 2.2 Blog:

Background:


As we all know OData offers many functions to be used as part of your $filter system query. Example
In above example substring is a function used on $filter system query. Substring is part of String Functions and then there are more like Date/Math/Type Functions.

In OData v4 spec a new bunch of Geo Spatial functions were introduced.

Problem:


My Client was capturing Geo co-ordinates as part of their information collection and wanted me to look into providing all objects when a particular Lat Long with a radius is passed to the WebAPI OData. 

At that time I was using the following:

  •      SQL server 2008R2
  • Entity Framework 6
  • WebAPI v2.1
  • OData v3

The SQL Server already had columns storing decimal Lat/Long. Therefore I introduced a new column of type Geography. This new column was populated while the data was coming via OData post request. This conversion was quite easy using the LINQ and is not part of this blog discussion.

Client was interested in enabling the Geo spatial functions. I found out that this is supported as part of new OData v4 spec. This news was shared with the client and was enough to start investigating and upgrading to WebAPI 2.2 with OData v4 support. I thought it would be a straight forward upgrade. After moving from hurdles of upgrading to new version in Development environment. I started applying the new geo spatial functions using the browser an example is below:

http://localhost:3665/OData/Resturants()?$filter=geo.Distance(Position,geography' Point(-122.03547668457 47.6316604614258)') lt 900  

Consider a database with all restaurants with their geographic info stored in database column named ‘Position’. So the above example should get me all restaurants with

Longitude: -122.03547668457
Latitude: 47.63166046128
Radius: Less than 900 meters

However this simple function “geo.Distance”  did not yield any results. This was confirmed by Microsoft by rasing a support ticket.

Solution:


Despite of unsuccessful venture of upgrading from Odata v3 to Odata v4 , I did not give up and was adamant to find a solution . Therefore I thought about a work around to at least support “geo.Distance” function and wait for upgrade later when Microsoft is ready. Here are two easy steps I did.


  • Inherit the EntitySetRoutingConvention class
  • Activate the controller to act as soon as it encounters geo.distance in uri

Inherit the EntitySetRoutingConvention class

I had to intercept the incoming url by overriding the SelectAction of helper class.  This helper class was inherited from EntitySetRoutingConvention class. This SelectAction is called before the url is handed over to the controller.
So in essence my code was reading the incoming request and looking for “geo.distance” function when a HTTP GET is performed.


Public class helperInterceptor : EntitySetRoutingConvention
{
Public override string SelectAction(ODataPath odataP, HttpControllerContext context, Ilookup<> actionMap){

If (context.request.requesturi.query.contains(“geo.distance”) && (context.Request.Method == HTTPMethod.Get)){
                Context.Request.RequestUri = cleanTheURI(context.request.requesturi.query);
}
}
}

If such condition was satisfied then the function above “cleanTheURI” will do the following:


  • This action method would rip the “geo.Distance” from $filter query
  • Remove the $filter function and add the “geo.distance” as a parameter at the end of the url.

URL Before:

http://localhost:3665/OData/Resturants()?$filter=geo.Distance(Position,geography' Point(-122.03547668457 47.6316604614258)') lt 900&$Select=ResturantName

URL After:

http://localhost:3665/OData/Resturants()?$Select=ResturantName&geo.Distance(Position,geography' Point(-122.03547668457 47.6316604614258)') lt 900

This cleansing is a necessary step for Odata v3, otherwise if “$filter=geo.Distance...” is passed to controller then it would throw an exception. The controller refuses to let the url go as this new function is not part of Odatav3 dictionary of $filter query functions.

Note: Make sure if the $filter query has other functions with logical operators then keep them there after cleansing example

URL Before:

http://localhost:3665/OData/Resturants()?$filter=startswith(ResturantName,’cafe’) eq true and geo.Distance(Position,geography' Point(-122.03547668457 47.6316604614258)') lt 900&$Select=ResturantName

URL After:

http://localhost:3665/OData/Resturants()?$filter=startswith(ResturantName,’cafe’) eq true&$Select=ResturantName&geo.Distance(Position,geography' Point(-122.03547668457 47.6316604614258)') lt 900


Activate the controller to act as soon as it encounters geo.distance in uri

Next step is very simple i.e on the Restaurants Controller override the GET method and again listen for url. If URL contains the geo.Distance function then parse the following information from the url

Longitude: -122.03547668457  
Latitude: 47.6316604614258
Radius: 900


Create a point object using Lat/Long

Var loc = dbGeorgraphy.PointFromText( “Point(-122.03547668457  47.6316604614258 )” ,4326)

And apply the linq distance function, example:

From a in db.Resturnats
Orderby a.Position.Distance(loc)
Select a

If the radius is known then use the following instead:

From a in db.Resturnats
orderby a.Position.Distance(loc)
where a.Position.Distance <= 900
Select a

Obviously the query has potential of returning a lot of results so make that as list and return as queryable :

List<Resturants> r = New List<Resturnats>();
R = (
From a in db.Resturnats
orderby a.Position.Distance(loc)
where a.Position.Distance <= 900
Select a ).toList<Resturants>

return r.asQueryable()

This will enable you return the geographic specific results and will also allow you to use rest of Odata queries like $select, $format,$orderby etc.


Hope this helps