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:
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:
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="Int32" Nullable="false" />
<Property Name="Name" Type="String" Nullable="false" />
<Property Name="Location" Type="Geometry" Nullable="false" />
</EntityType>
Representing in SSDL is very simple as well:
<EntityType Name="Stores">
<Property Name="StoreId" Type="int" Nullable="false" />
<Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
<Property Name="Location" Type="geometry" Nullable="false" />
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)”)
Query with filter using local variable
where s.Location == loc
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);
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:
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