Data Access has been discontinued. Please refer to this page for more information.

How to: Work with Spatial Data

Spatial data, also known as geospatial data or geographic information is that data or information, which identifies the geographic location of features and boundaries on Earth, such as natural features, oceans, and more. Spatial data is usually stored as coordinates and topology, and is data that can be mapped. Spatial data is often accessed, manipulated or analyzed through Geographic Information Systems (GIS). Telerik Data Access supports development of applications that involve geometric or geographic data.

For this article a sample database that has only two tables will be used. Both tables contain geographic information.

The City table has a column of type geography. The geography data type represents data in a round-earth coordinate system. The Country table has a column of type geometry. The geometry data type represents data in a Euclidean (flat) coordinate system.

Using Spatial Data in Your Applications

In order to be able to work with spatial data types, you need to download and install the Microsoft SQL Server 2008 R2 Feature Pack. This adds an assembly that is present both in the database server and on the client side. It allows you to use the SqlGeography and SqlGeometry CLR types from the Microsoft.SqlServer.Types namespace.

Creating a Model

Make sure to add a reference in your model project to the Microsoft.SqlServer.Types assembly. At this point, when you create the persistent classes you can use the SqlGeography/SqlGeometry CLR types for the definition of the spatial data columns. In your metadatasource class, you can map them as demonstrated below:

configuration.HasProperty(x => x.Location).
    HasFieldName("_location").
    WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("Location").IsNotNullable().HasColumnType("geography");
configuration.HasProperty(x => x.Location).
    HasFieldName("_location").
    WithDataAccessKind(DataAccessKind.ReadWrite).
    ToColumn("Location").IsNotNullable().HasColumnType("geography");

Telerik Data Access supports only version 10.0 of the Microsoft.SqlServer.Types assembly.

What Can You Do Spatial Data ?

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/SqlGeometry CLR types will be detected and transformed by the Telerik Data Access runtime. The following example demonstrates how to find all cities within a distance of 1500km around Munich.

using (FluentModel dbContext = new FluentModel())
{
   SqlGeography Munich = SqlGeography.Point(48.136944, 11.575278, 4326);
   const double DistanceMeters = 1500 * 1000.0;

   var query = from x in dbContext.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 city in query)
   {
       Console.WriteLine(city);
   }
}
Using dbContext As New FluentModel()
 Dim Munich As SqlGeography = SqlGeography.Point(48.136944, 11.575278, 4326)
 Const DistanceMeters As Double = 1500 * 1000.0

 Dim query = From x In dbContext.Cities
    Where CDbl(x.Location.STDistance(Munich)) < DistanceMeters
    Order By x.Location.STDistance(Munich)
    Select New With 
    {
        Key .Name = x.Name, 
        Key .Distance = x.Location.STDistance(Munich)
    }

 For Each city In query
  Console.WriteLine(city)
 Next city
End Using

The STDistance method is pushed to the database server, and the query result will be ordered.