Home » Blog

Spatial data queries in LightSpeed

Tagged as LightSpeed

LightSpeed, unlike lesser object-relational mappers, supports properties of SQL Server 2008 spatial data types. You can drag a table with geography or geometry columns into the LightSpeed designer, and party on the generated SqlGeography and SqlGeometry properties to your heart’s content.

But what if you want to perform spatial queries on those columns? For example, what if you want to find all the walruses within a given distance of a particular penguin? (Because examples involving restaurants are boring. That’s why.) Well, LightSpeed supports that too, but you’ll need to teach it about the spatial methods you want to use.

As we’ve discussed before, you can use the ServerFunctionDescriptor.Register method to map a CLR function to a SQL function. However, the SQL Server 2008 spatial methods aren’t normal SQL functions: they’re member functions of SQLCLR types.

Fortunately, you can still use the same technique to map the SqlGeography and SqlGeometry methods to their server-side equivalents. You just need to put a dot in front of the server-side method name to tell LightSpeed to map it as a member method instead of a T-SQL function:

MethodInfo method = typeof(SqlGeography).GetMethod("STDistance");
ServerFunctionDescriptor.Register(method, ".STDistance");  // note the all-important dot!

And now you can write the following LINQ query:

var menaces = from w in unitOfWork.Walrus
              where w.Location.STDistance(penguin.Location).Value < distance
              select w;

And it will get translated into a server-side query just as you’d hope!

Add your comments
Posted by Ivan Towlson on 27 September 2010

Leave a Reply