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 DBGeography which 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=Int32Nullable=false />

  <Property Name=NameType=String Nullable=false />

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

</EntityType>

 

Representing in
SSDL
is very simple as well:

<EntityType Name=Stores>

  <Key>

      <PropertyRef Name=StoreId />

  </Key>

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

  <Property Name=NameType=nvarchar Nullable=falseMaxLength=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

 

Comments (24)

  1. Ivan Perez says:

    EF Team,

    Here are my thoughts to your questions…

    Question 1: I feel as long as your implementation does not dictate the design of other DB vendor's implementation, its fine.   I don't see an issue there.  I also feel as long as this closely resembles the IEEE standards it shouldn't be a problem.  Which it seems to be the case.

    Question 2: My one concern here is that if we need to do a quick calculation, we have to do a round trip to the server for this; even if the data is already on the client.  I know that we are doing this to get data from the database in the first place, but sometimes a simple calculation is needed and not another data pull, which will require a database connection, etc. etc.  So in other words, would it be possible to have some of the most common functions for geospacial be available in the .net world for less db round trips?  Or functions that would make sense outside the database world.  Such as distance functions, etc., etc…  I guess what I'm trying to say is that it would make a lot of sense to have a .Net namespace that deals with geospacial data and functions thats not necesarily specific to SQL Server's geospacial data types.

    Question 3: In this case my gut is telling me that a Point class would be useful with mapping for Bing/Google maps, etc.  Not sure if this is actually the case.  I can't speak for polygon.

  2. Rob says:

    Ummm…what about support for enums first. For that matter, why not an extensible type system so I can build my own support in?

  3. Jason Short says:

    I have to say that I think you using the type=Geometry is a bad idea.   It is not a .Net Geometry object, it is a SQL Server Geometry object.  It should say so in the type.  SqlServer.Geometry.  What if a third party vendor implements their own Geometry type?  It is not part of ado.net, so how are you going to map it?

  4. Vesel says:

    What about providing spatial functionality to the .NET Framework as first class citizen?

    I think you should implement OGC specification independently from EF and then provide mapping functionality in EF.

  5. Pablo Montilla says:

    Hello, great to hear that you are bringing support for spatial.

    My answers:

    1. Don't foresee problems (but I use only SQL Spatial so…)

    2. A wrapper for the SqlTypes would be very useful. As Ivan said, it is useful to be able to do calculations on the client, and that can be done with the SqlTypes, but the current API is dreadful (I suppose it was dictated by SQL requirements). A more .NETish wrapper would enable various use cases that are too complex now.

    3. If I get 2, I probably could live without Point or Polygon.

    4. I don't get the question…you mean Geography or Geometry that has LINESTRING and POINT in them…that probably could be useful (and it's supported in SQL Server).

    Regards,

    Pablo

  6. Chris says:

    I don't use spatial types much, so I can't provide any worthwhile feedback on your questions.  However, I do have a couple of potential points of improvement for the API itself.

    It would be nice to not have to do the metric to standard units conversion manually (there's a lot of room for error there).  I would suggest either adding a second parameter to the Distance() method like so:

    var stores = from s in db.Stores

                where s.Location.Distance(person.Address, DistanceUnit.Miles) <= 1

                select s;

    Or (my preferred option) using an extension method to provide a more fluent experience like so:

    var stores = from s in db.Stores

                where s.Location.Distance(person.Address).InMiles() <= 1

                select s;

    I would also recommend renaming the Distance() method to DistanceTo().

  7. The guy who wants Enums says:

    YES! Support for ENUMS! oh wait… I mean SPATIALS!!! WOOT! I think?

  8. The main point is that your implementation should allow third party EF-providers for other databases to use arbitrary end .NET types for working with spatial types. Otherwise, classes like DbGeometry and DbGeography should not reside in SQL Server-specific assemblies and namespaces – they should be available in base .NET/ADO.NET or EF assemblies.

  9. Peter says:

    Will DbGeography support OGC methods like STUnion?  I assume yes but thought I would ask the question.  We will want to manipulate shapes in our application before saving them to the database.  Right now, we are using Microsoft.SqlServer.Types to accomplish that.

  10. Tjipke says:

    When reading this, I was also thinking the same as Rob: "why not an extensible type system so I can build my own support in"…

    For example we use our own 'enum' types, looking a lot like this lostechies.com/…/enumeration-classes. So when a row (say Employee) would have an 'EmployeeType' column I would like to be able to map that to the correct EmployeeType class instance but also be able to query on it. The first is easy to do currenty with a partial class property exposing the EmployeeType column as a real EmployeeType, however that doesn't work in querying (from p in Persons where p.EmployeeType == EmployeeType.Manager…)

    So not to bring the focus away from spatial types (we definitly need support for that), but I hope you will be able to use spatial types support to enhance the typing system so it becomes extensible with our own types…

  11. Jason says:

    Answers:

    1. I think it would depend on what you consider to be scenarios for client-side functionality as opposed to server-side.  Certainly during the initial expression evaluation, I would want the server to do as much for me as possible.  But, once I have instances of spatial objects, I would expect that if I need to work with them in much the same way as I do today (meaning that I would include a reference to Microsoft.SQLServer.Types if I need to treat the data as something other than an opaque BLOB).

    2. I struggle with the idea of having a vendor-neutral implementation of spatial objects to which I can do anything client-side.  Instead, I would say that as long as I can get a SqlGeography from a DbGeography, then we’re fine.  Knowing what client-side scenarios you’re thinking of may help to change my mind.

    3. Having constrained types may make for better semantics, but are not absolutely necessary in my opinion – we already work with the SQL Server types in this way.  But, besides Point, LineString, and Polygon, how about the collections (MultiPoint, MultiLineString, MultiPolygon, and heterogeneous collection, etc)?

    4. I think you have to look at the backing store for the answer here.  There’s no constraint on what a Geography column in a database can contain, so I think it would be illogical to try to enforce that constraint from the data model.

    Further thoughts/questions:

    Speaking from the SQL Server point of view, EF can already serve as a pass-through of native SQLGeometry or SQLGeography objects by treating the serialized bytes as opaque BLOBs.  So simply getting an instance of a spatial object onto the client for further client-side processing is not where the current heartache exists.

    The biggest benefit that I see of spatial support in EF is the ability to include spatial types in predicates (where the expression is recognized and evaluated on the server).  Secondary to that would be the ability to transform the spatial data in the projection, having the transformation take place on the server as part of the query execution.

    Example:

    var q = from r in Roads

           where r.linestringdata.Distance(myHouse) < 1000

           select new {r.id, linestring=r.linestringdata.Reduce(5)}

    Per the blog post, r.linestringdata would be mapped to the EF primitive Geography type and implemented as a DbGeography object.  Will there be a direct cast available to the vendor-specific data type (i.e., to get an instance of SQLGeography from a DbGeography)?  At what point in the architecture does a third-party provider have the ability to inject their own spatial implementation into the DbGeography behavior?

    Also in the blog post, it states that if the Microsoft.SQLServer.Types assembly is available, then all spatial operations are performed on the client.  What does this mean in respect to the predicate expression in the above query?  Would the .Distance() operation still be evaluated on the server-side?  How about the .Reduce() operation?  What scenarios would be beneficial to use client-side execution vs. server-side?

    I’m curious what the list of common functions contains.  If someone’s favorite function is not included in the list of canonical functions, is there a way to still invoke it?  Will Spatial aggregates be included in the canonical functions? (thinking ahead to Denali: blogs.msdn.com/…/spatial-aggregates-in-sql-server-denali.aspx)

    Does DbGeography (and DbGeometry) have the ability to specify a SRID?  In the constructor call demonstrated above, the SRID is missing (so I’m assuming that, like the SQL Server implementation, 0 would be the default for Geometry, and 4326 would be the default for Geography).  

    Distance Units for Geography are defined by the spatial reference system, which requires the developer know certain details of the reference system when querying (i.e., .Distance() for EPSG 4326 is in meters, but EPSG 4241 uses Clarke’s foot, whatever that is!).  I kind of like the suggestion of having SRID-aware unit conversions built into the data type (feet, miles, meters, and kilometers are all that I see necessary).

  12. James says:

    I am building applications that deal with vast amounts of spatial data (as in dense mapping of entire earth terrain and man made structures, etc.).  

    We have the following requirements:

    *  Ability to store all sorts of spatial data, such as points, lines, polygons, homogenous collections of each type and heterogenous collections as well.

    *  Ability to store spatial data in various database platforms via EF.  Currently targeting MSSQL 2008 R2 and Oracle 10g as our minimums.

    *  Must be able to edit spatial data.

    *  Need server side spatial filtering and indexing.

    *  Performance is critical.

    *  We need all of this yesterday.  :~)

    thanks

  13. Test says:

    the comment does not work or you are hidding all comments!

  14. Pop.Catalin says:

    1. We already use Spatial Types from Sql Server so we already need SQL Clr Types installed. But this can certainly be problematic in hosted scenarios where the hosting provider does does not offer SQL Clr Types as an option. It would be a big benefit for hosted scenarios if there was a way for EF to provide spatial functionality independent of SQL Server Clr Types being available on the client.

    2. Yes, we need all the fuctionality spatial types provide on the client. For this purpose we currently use SqlGeography and SqlGeometry directly, to perform in memory manipulations of spatial data. My wish is that there will be an easy way to convert to/from SqlGeograpy/SqlGeometry to DbGeography/DbGeometry in order to easily interoperate with code that uses SqlGeography and SqlGeometry.

    3. I don't think constrained types can be terribly useful. I can't imagine any scenarios where such a design would be favorable to the default design where geometry is an abstract concept.

    4. We already use heterogeneous columns. Any other design involving homogenous columns would have serious drawbacks and complications for our model, compared to being able to use heterogeneous columns of spatial values, because our spatial data can be at any time be: points, lines, polygons or multi geometries.

    Some more thoughts:

    While adding spatial data types support is a welcome feature, I wish a more general solution to this problem is considered.

    A solution where any custom or user defined data type can be used in entity framework (IE HierachyId) ,by being be able to abstract such data types and plug the abstractions into EF.

    Such a system that is designed around an extensibility model will prevent situations like the present one where spatial data types support comes years after they were offered by Sql Server. This way the next of XXX Data Type would be easy to plugin and use, right after release.

    Catalin

  15. Patricio says:

    1. If you refer to client-side spatial functionality as having a way to express a spatial restriction in a Where statement, I have no problem with that relying in the SQL spatial implementation.

    2. I definitely feel that richer capabilities on the client side are needed.  I have been working on GIS projects for the past 7 years and I rely heavily on the GDAL libraries, which provide .NET bindings.  It would be extremely beneficial for .NET to have native spatial support aside from the entity framework.

    3. If not constrained types, at least have a way to determine whether a Geometry object contains a point, line or polyton.

    4. I do foresee the possibility of using heterogeneous columns for spatial values in the database.  Many clients (i.e. google earth and OpenLayers) do not restrict layers to a specific type of geometry.  

  16. Jaap says:

    What about the hierarchyid datatype? Looks like we need a general solution for .NET user defined types as the hierarchyid and spatial datatypes. And if you have something general you can even support .NET user defined types which I have created.

  17. BlogReader says:

    I second Jaap. What about hierarchyid data type? Is support for that on the roadmap?

  18. Larry says:

    When will this spatial support be available?

  19. Kirk Davis says:

    Like Larry asked – when will a beta or CTP be available?  Normally I never use pre-RTM libraries/updates for a production project, but in this case, lack of support for SqlGeography in EF4 is KILLING ME.  

    Any info on when something – anything – available for download would be great, so I can get rid of the assorted computed-columns, blobs with triggers, and so on that are standing in as work-arounds.

  20. Guilherme Defreitas says:

    Great feature!  I can't wait to see that working with Azure!

  21. Dennis Jonio says:

    I have been using Oracle Spatial/Locator for 5 years now. An excellent product. With _Denali_ now providing curve support and EF providing CodeFirst support, Microsoft has come a long way. In my case the only element that is lacking is WCF RIA Services support. This could very well tip-the-scales fo me. It has been a battle shoe-horning NHibernate into my the GIS world.  

    1) No. Gee, I guess as long as I can get to some _engine_ I will be fine.

    2) No. I only forsee the client as the means to add/update the specific geometry columns values. The occasional this/or/that trip to the server will not be significant for me.

    3) No. Why?

    4) Yes. Absolutely. Why not? Rumor has it even ESRI has/is fixing this shortsightedness.

    r,

  22. Mohamed says:

    Hey

    Using the latest release of EF with the Spatial support.

    I need to store Regions on a map in SQL Server and then query which Suppliers are in a given region.

    1. How would i store this shape in SQL using EF?

    2. How would I query all suppliers in a Given Region using EF and Linq?

    Lets assume a simple db structure with no relationships

    Region:

    regionID (bigint)

    Name (nvarchar255)

    Shape (not sure if I can use geography to store a multipoint shape like a geo-fence)

    Supplier:

    supplierID (bigint)

    Name (nvarchar255)

    GeoLocation (geography)

  23. Any ETA On the release date?

  24. Maria says:

    Why this error coming {"Error getting value from 'WellKnownValue' on 'System.Data.Entity.Spatial.DbGeography"}

Skip to main content