Spatial Types in the Entity Framework

One of the highly-anticipated features coming in the next
version of Entity Framework is Spatial support. The
team has been hard at work designing a compelling story for Spatial, and we
would love to get your feedback on it. In this post we will cover:

  • The basics of SQL Server’s  spatial support
  • Design goals
  • Walkthrough of design: CLR types, Metadata, Usage,
    WCF Serialization, Limitations
  • Questions (we want to hear from you!)

This entry will not cover the tools experience as we want to
focus on what is happening under the hood. 
We will blog about the tools design at a later time. For now, be sure
that we plan on shipping Spatial with Tools support.

 

The Basics
of Spatial

For those of you that are new to Spatial, let’s briefly describe
what it’s all about: There are two basic spatial types called Geometry and
Geography which allow you to perform geospatial calculations. An example of a
geospatial calculation is figuring out the distance between Seattle and New
York, or calculating the size (i.e. area) of a state, where the state has been
described as a polygon.

Geometry and Geography are a bit different. Geometry deals with planar data.
Geometry is well documented under the Open Geospatial Consortium
(OGC) Specification
. Geography
deals with ellipsoidal data, taking in mind the curvature of the earth when
performing any calculations. SQL introduced spatial support for these two types
in SQL Server 2008. The SQL implementation supports all of the standard
functions outlined in the OGC spec.

Programming Experience
in SQL

The query below returns all the stores within half a mile of the address of
a person whose ID is equal to 2. In the where clause we multiply the result of STDistance by 0.00062 to convert meters to miles.

DECLARE @dist sys.geography

SET @dist = (SELECT p.Address

       FROM dbo.People as p

       WHERE p.PersonID = 2)

SELECT [Store].[Name],

       [Store].[Location].Lat,

       [Store].[Location].Long

FROM   [dbo].[Stores] AS [Store]

WHERE (([Store].[Location].STDistance(@dist)) * cast(0.00062 as float(53))) <= .5

In the sample below, we change the
person’s address to a different coordinate. Note that STGeomFromText
takes two parameters: the point, and a Spatial Reference Identifier (SRID). The value of 4326 maps to the WGS 84 which is the standard
coordinate system for the Earth.

update [dbo].[People]

set [Address] = geography::STGeomFromText('POINT(-122.206834 57.611421)',
4326)

where [PersonID] = 2

Note that when listing a point, the longitude is listed
before the latitude.

 

Design

Goals

The goals for spatial are the following:

  • To provide a first class support for spatial in
    EDM
  • Rich Programming experience in LINQ and Entity
    SQL against Spatial types
  • Code-first, Model-first, and Database-first
    support
  • Tooling (not covered in this blog)

We have introduced two new primitive EDM Types called Geometry and Geography. This allows us to have spatial-typed properties in our
Entities and Complex Types. As with every other primitive type in EDM, Geometry
and Geography will be associated with CLR types. In this case, we have created
two new types named DBGeometry
and DBGeographywhich allows us to provide a
first-class programming experience against these types in LINQ and Entity SQL.

One can
describe these types in the CSDL in
a straightforward fashion:

<EntityType Name="Store">

  <Key>

      <PropertyRef Name="StoreId" />

  </Key>

  <Property Name="StoreId" Type="Int32" Nullable="false" />

  <Property Name="Name" Type="String" Nullable="false" />

  <Property Name="Location" Type="Geometry" Nullable="false" />

</EntityType>

 

Representingin
SSDL is very simple as well:

<EntityType Name="Stores">

  <Key>

      <PropertyRef Name="StoreId" />

  </Key>

  <Property Name="StoreId" Type="int"
Nullable="false" />

  <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />

  <Property Name="Location" Type="geometry" Nullable="false" />

</EntityType>

Be aware that spatial types cannot
be used as entity keys, cannot be used in relationships, and cannot be used as
discriminators.

Usage
Here are some scenarios and corresponding queries showing how simple
it is to write spatial queries in LINQ:

 

Query Scenario

Example

Simple Select of Entities with Spatial columns

// Store is an entity type with Location as a Geography type

var stores = from s in db.Stores

             select s;

Simple Select of Spatial values

var storeLocations = from s in db.Stores

                     select s.Location;

Query with filter, using static constructors

var store = from s in db.Stores

            where s.Location ==

                DbGeography.Parse(”POINT(-122.206834 47.611421)”)

            select s;

Query with filter using local variable

var store = from s in db.Stores

            where s.Location == loc

            select s;

Query involving Spatial methods

var distances = from s in db.Stores

                select s.Location.Distance(anotherLocation);

Find all the stores within a mile of a given address

var person = db.People.Single(p => p.PersonID == 2);

var stores = from s in db.Stores

             where s.Location.Distance(person.Address) *

                                              .00062 <= 1

             select new Location

                        {

                            Name = s.Name,

                            Latitude = s.Location.Latitude,

                            Longitude = s.Location.Longitude

                        };

 

Have in mind that spatial types are immutable, so they can’t be modified after
creation. Here is how to create a new location of type DbGeography:

s.Location = DbGeography.Parse("POINT(-122.206834
47.611421)");

db.SaveChanges();

Spatial
Functions

Our Spatial implementation relies on the underlying database
implementation of any of the spatial functions such as Distance, Intersects,
and others. To make this work, we have created the most common functions as
canonical functions on EDM. As a result, Entity Framework will defer the
execution of the function to the server.

Client-side Behavior
DbGeometry and DbGeography
internally use one of two implementations of DbSpatialServices
for client side behavior which we will make available:

One implementation relies on Microsoft.SQLServer.Types.SQLGeography
and Microsoft.SQLServer.Types.SQLGeometry being
available to the client. If these two namespaces are available, then we
delegate all spatial operations down to the SQL assemblies. Note that this
implementation introduces a dependency.

Another implementation provides limited services such as
serialization and deserialization, but does not allow performing non-trivial
spatial operations. We create these whenever you explicitly create a spatial
value or deserialize one from a web service.

DataContract Serialization

Our implementation provides a simple wrapper that is serializable, which allows spatial types to be used in
multi-tier applications. To provide maximum interoperability, we have created a
class called DbWellKnownSpatialValue which contains
the SRID, WellKnownText (WKT), and WellKnownBinary (WKB) values. We will serialize SRID, WKT
and WKB.

 

Questions

We want to hear from you. As we work through this design, it
is vital to hear what you think about our decisions, and that you chime in with
your own ideas. Here are a few questions, please take some time to answer them
in the comments:

  1. In order to have client-side spatial functionality,
    EF relies on a spatial implementation supplied by the provider. As a default,
    EF uses the SQL Spatial implementation. Do you foresee this being a problematic
    for Hosted applications which may or may not have access to a spatial
    implementation?
  2. Do you feel as though you will need richer
    capabilities on the client side?
  3. In addition to Geometry and Geography types, do
    you need to have constrained types like Point, Polygon?
  4. Do you foresee using heterogeneous columns of
    spatial values in your application?

 

Spatial data is ubiquitous now thanks to the widespread use
of GPS-enabled mobile devices. We are very excited about bringing spatial type
support to the Entity Framework. We encourage you to leave your thoughts on
this design below.

 

Cheers,

Pedro Ardila

Program Manager – Entity Framework Team