Telerik blogs

Quering Spatial Data with OpenAccess in Q1 2011

March 17, 2011 Release 0 Comments


Starting with the 2011 Q1 release, OpenAccess supports development of applications that involve geometric or geographic data. For the sake of simplicity, this blog will focus on the use of SqlGeography with MSSQL 2008. Other databases (like Oracle) provide support for geometric and geographic data as well, but this shall be the topic of another blog post.

Let's start with a simple model that has only two domain classes City and Country. Both classes should contain geographic information: the location of the City (as a point) and the polygon shape of the Country. Both domain classes then need to declare a persistent field of type SqlGeography, which can be found in the Microsoft.SqlServer.Types namespace. In order to use the SqlGeography type, download and install the Microsoft SQL Server System CLR Types from the Microsoft SQL Server 2008 Feature Pack. This adds an assembly that is present both in the database server and now also on the client side.

The SqlGeometry CLR type is not mapped automatically to a SQL geography column; this is something that has to be done manually. The OpenAccess .rlinq Model Schema Explorer / Table Editor will present you with a pick list of the available SQL types, just select geography. Another way to specify the column type is to use the fluent API's HasColumnType("geography") method for the column. The column type specification step is of course not necessary when reverse-engineering is used and the domain model gets generated from the database schema.

After this setup the domain model could look like this:

using Microsoft.SqlServer.Types;
 
public class City
{
    public Guid Id { get; set; }
    public SqlGeography Location { get; set; }
    public string Name { get; set; }
 
    City() {}
 
    public City(double longitude, double latitude, string name)
    {
        Location = SqlGeography.Point(latitude, longitude, 4326);
        Name = name; Id = Guid.NewGuid();
    }
}
 
public class Country
{
    public Guid Id { get; set; }
    public SqlGeography Shape { get; set; }
    public string Name { get; set; }
 
    Country() { }
 
    public Country(string name, string bounds)
    {
        Shape = SqlGeography.STGeomFromText(
                        new System.Data.SqlTypes.SqlChars(bounds.ToCharArray()), 4326);
        Name = name; Id = Guid.NewGuid();
    }
}
 

In the Country constructor we have used a conversion routine which is present in the SqlGeometry type that allows specifing the shape of the Country through a string value. We have found this to be easier on the eyes than using decimal arrays to construct instances.

What can we do with such fields in the database? You can store and load values of those fields, but you can also perform server side queries on them. The way to express them is through LINQ, as the methods of the SqlGeography CLR type will be detected and transformed by the OpenAccess runtime. Let's see an example - finding all cities within a distance of 1500km around Munich:

// ctx is our OpenAccessContext
SqlGeography Munich = SqlGeography.Point(48.136944, 11.575278, 4326);
double distanceMeters = 1500*1000.0;
var q = from x in ctx.Cities
        where (double)x.Location.STDistance(Munich) < distanceMeters
        orderby x.Location.STDistance(Munich)
        select new { Name = x.Name, Distance = x.Location.STDistance(Munich) };
foreach(var c in q)
    Console.WriteLine(c);
 

Here, the STDistance method is pushed to the database server, and the query result will be ordered. A point worth mentioning here is that the STDistance method is declared as returning CLR type SqlDouble, but for the filter we are not especially interested in that type, so we cast it to System.Double; that will allow us to refer to the double operator <, which returns a bool value - perfect for a LINQ filter expression! SqlDouble instances will be returned by the projection part of the query however.

Another interesting query is to find out in which country Paris is located (of course, you will know that already):

var q3 = from x in ctx.Countries
         from y in ctx.Cities where y.Name == "Paris"
         where x.Shape.STIntersects(y.Location).Equals(1)
         select x.Name;
foreach(var c3 in q3)
    Console.WriteLine(c3);
 

Here, the STIntersects method is performed on the database server. Why testing with .Equals(1)? Because the STIntersects CLR method is declared as returning a SqlBoolean, but in fact it is returning a SQL bit value, which can be compared most easily with 1.

As stated in the beginning, this article focuses on SqlGeography, but OpenAccess supports also SqlGeometry in the same way. One of the next blog posts will focus on how to execute the same queries with spatial types on Oracle.

The example code for this article including fluent type definition and more queries will be included in the upcoming SDK.

Release, Telerik Data Access

Comments

Comments are disabled in preview mode.