Basic Spatial Data with SQL Server and Entity Framework 5.0
- by Rick Strahl
In my most recent project we needed to do a bit of geo-spatial referencing. While spatial features have been in SQL Server for a while using those features inside of .NET applications hasn't been as straight forward as could be, because .NET natively doesn't support spatial types. There are workarounds for this with a few custom project like SharpMap or a hack using the Sql Server specific Geo types found in the Microsoft.SqlTypes assembly that ships with SQL server. While these approaches work for manipulating spatial data from .NET code, they didn't work with database access if you're using Entity Framework. Other ORM vendors have been rolling their own versions of spatial integration. In Entity Framework 5.0 running on .NET 4.5 the Microsoft ORM finally adds support for spatial types as well. In this post I'll describe basic geography features that deal with single location and distance calculations which is probably the most common usage scenario. SQL Server Transact-SQL Syntax for Spatial Data Before we look at how things work with Entity framework, lets take a look at how SQL Server allows you to use spatial data to get an understanding of the underlying semantics. The following SQL examples should work with SQL 2008 and forward. Let's start by creating a test table that includes a Geography field and also a pair of Long/Lat fields that demonstrate how you can work with the geography functions even if you don't have geography/geometry fields in the database. Here's the CREATE command:CREATE TABLE [dbo].[Geo](
[id] [int] IDENTITY(1,1) NOT NULL,
[Location] [geography] NULL,
[Long] [float] NOT NULL,
[Lat] [float] NOT NULL
)
Now using plain SQL you can insert data into the table using geography::STGeoFromText SQL CLR function:insert into Geo( Location , long, lat ) values
( geography::STGeomFromText ('POINT(-121.527200 45.712113)', 4326), -121.527200, 45.712113 )
insert into Geo( Location , long, lat ) values
( geography::STGeomFromText ('POINT(-121.517265 45.714240)', 4326), -121.517265, 45.714240 )
insert into Geo( Location , long, lat ) values
( geography::STGeomFromText ('POINT(-121.511536 45.714825)', 4326), -121.511536, 45.714825)
The STGeomFromText function accepts a string that points to a geometric item (a point here but can also be a line or path or polygon and many others). You also need to provide an SRID (Spatial Reference System Identifier) which is an integer value that determines the rules for how geography/geometry values are calculated and returned. For mapping/distance functionality you typically want to use 4326 as this is the format used by most mapping software and geo-location libraries like Google and Bing.
The spatial data in the Location field is stored in binary format which looks something like this:
Once the location data is in the database you can query the data and do simple distance computations very easily. For example to calculate the distance of each of the values in the database to another spatial point is very easy to calculate.
Distance calculations compare two points in space using a direct line calculation. For our example I'll compare a new point to all the points in the database.
Using the Location field the SQL looks like this:-- create a source point
DECLARE @s geography
SET @s = geography:: STGeomFromText('POINT(-121.527200 45.712113)' , 4326);
--- return the ids
select ID, Location as Geo ,
Location .ToString() as Point ,
@s.STDistance( Location) as distance
from Geo
order by distance
The code defines a new point which is the base point to compare each of the values to. You can also compare values from the database directly, but typically you'll want to match a location to another location and determine the difference for which you can use the geography::STDistance function.
This query produces the following output:
The STDistance function returns the straight line distance between the passed in point and the point in the database field. The result for SRID 4326 is always in meters. Notice that the first value passed was the same point so the difference is 0. The other two points are two points here in town in Hood River a little ways away - 808 and 1256 meters respectively.
Notice also that you can order the result by the resulting distance, which effectively gives you results that are ordered radially out from closer to further away. This is great for searches of points of interest near a central location (YOU typically!).
These geolocation functions are also available to you if you don't use the Geography/Geometry types, but plain float values. It's a little more work, as each point has to be created in the query using the string syntax, but the following code doesn't use a geography field but produces the same result as the previous query.--- using float fields
select ID,
geography::STGeomFromText ('POINT(' + STR (long, 15,7 ) + ' ' + Str(lat ,15, 7) + ')' , 4326),
geography::STGeomFromText ('POINT(' + STR (long, 15,7 ) + ' ' + Str(lat ,15, 7) + ')' , 4326). ToString(),
@s.STDistance( geography::STGeomFromText ('POINT(' + STR(long ,15, 7) + ' ' + Str(lat ,15, 7) + ')' , 4326)) as distance
from geo
order by distance
Spatial Data in the Entity Framework
Prior to Entity Framework 5.0 on .NET 4.5 consuming of the data above required using stored procedures or raw SQL commands to access the spatial data. In Entity Framework 5 however, Microsoft introduced the new DbGeometry and DbGeography types. These immutable location types provide a bunch of functionality for manipulating spatial points using geometry functions which in turn can be used to do common spatial queries like I described in the SQL syntax above.
The DbGeography/DbGeometry types are immutable, meaning that you can't write to them once they've been created. They are a bit odd in that you need to use factory methods in order to instantiate them - they have no constructor() and you can't assign to properties like Latitude and Longitude.
Creating a Model with Spatial Data
Let's start by creating a simple Entity Framework model that includes a Location property of type DbGeography: public class GeoLocationContext : DbContext
{
public DbSet<GeoLocation> Locations { get; set; }
}
public class GeoLocation
{
public int Id { get; set; }
public DbGeography Location { get; set; }
public string Address { get; set; }
}
That's all there's to it. When you run this now against SQL Server, you get a Geography field for the Location property, which looks the same as the Location field in the SQL examples earlier.
Adding Spatial Data to the Database
Next let's add some data to the table that includes some latitude and longitude data. An easy way to find lat/long locations is to use Google Maps to pinpoint your location, then right click and click on What's Here. Click on the green marker to get the GPS coordinates.
To add the actual geolocation data create an instance of the GeoLocation type and use the DbGeography.PointFromText() factory method to create a new point to assign to the Location property:[TestMethod]
public void AddLocationsToDataBase()
{
var context = new GeoLocationContext();
// remove all
context.Locations.ToList().ForEach( loc => context.Locations.Remove(loc));
context.SaveChanges();
var location = new GeoLocation()
{
// Create a point using native DbGeography Factory method
Location = DbGeography.PointFromText(
string.Format("POINT({0} {1})", -121.527200,45.712113)
,4326),
Address = "301 15th Street, Hood River"
};
context.Locations.Add(location);
location = new GeoLocation()
{
Location = CreatePoint(45.714240, -121.517265),
Address = "The Hatchery, Bingen"
};
context.Locations.Add(location);
location = new GeoLocation()
{
// Create a point using a helper function (lat/long)
Location = CreatePoint(45.708457, -121.514432),
Address = "Kaze Sushi, Hood River"
};
context.Locations.Add(location);
location = new GeoLocation()
{
Location = CreatePoint(45.722780, -120.209227),
Address = "Arlington, OR"
};
context.Locations.Add(location);
context.SaveChanges();
}
As promised, a DbGeography object has to be created with one of the static factory methods provided on the type as the Location.Longitude and Location.Latitude properties are read only. Here I'm using PointFromText() which uses a "Well Known Text" format to specify spatial data. In the first example I'm specifying to create a Point from a longitude and latitude value, using an SRID of 4326 (just like earlier in the SQL examples).
You'll probably want to create a helper method to make the creation of Points easier to avoid that string format and instead just pass in a couple of double values. Here's my helper called CreatePoint that's used for all but the first point creation in the sample above:public static DbGeography CreatePoint(double latitude, double longitude)
{
var text = string.Format(CultureInfo.InvariantCulture.NumberFormat,
"POINT({0} {1})", longitude, latitude);
// 4326 is most common coordinate system used by GPS/Maps
return DbGeography.PointFromText(text, 4326);
}
Using the helper the syntax becomes a bit cleaner, requiring only a latitude and longitude respectively. Note that my method intentionally swaps the parameters around because Latitude and Longitude is the common format I've seen with mapping libraries (especially Google Mapping/Geolocation APIs with their LatLng type).
When the context is changed the data is written into the database using the SQL Geography type which looks the same as in the earlier SQL examples shown.
Querying
Once you have some location data in the database it's now super easy to query the data and find out the distance between locations. A common query is to ask for a number of locations that are near a fixed point - typically your current location and order it by distance.
Using LINQ to Entities a query like this is easy to construct:[TestMethod]
public void QueryLocationsTest()
{
var sourcePoint = CreatePoint(45.712113, -121.527200);
var context = new GeoLocationContext();
// find any locations within 5 kilometers ordered by distance
var matches =
context.Locations
.Where(loc => loc.Location.Distance(sourcePoint) < 5000)
.OrderBy( loc=> loc.Location.Distance(sourcePoint) )
.Select( loc=> new { Address = loc.Address, Distance = loc.Location.Distance(sourcePoint) });
Assert.IsTrue(matches.Count() > 0);
foreach (var location in matches)
{
Console.WriteLine("{0} ({1:n0} meters)", location.Address, location.Distance);
}
}
This example produces:
301 15th Street, Hood River (0 meters)The Hatchery, Bingen (809 meters)Kaze Sushi, Hood River (1,074 meters)
The first point in the database is the same as my source point I'm comparing against so the distance is 0. The other two are within the 5 mile radius, while the Arlington location which is 65 miles or so out is not returned. The result is ordered by distance from closest to furthest away.
In the code, I first create a source point that is the basis for comparison. The LINQ query then selects all locations that are within 5km of the source point using the Location.Distance() function, which takes a source point as a parameter. You can either use a pre-defined value as I'm doing here, or compare against another database DbGeography property (say when you have to points in the same database for things like routes).
What's nice about this query syntax is that it's very clean and easy to read and understand. You can calculate the distance and also easily order by the distance to provide a result that shows locations from closest to furthest away which is a common scenario for any application that places a user in the context of several locations. It's now super easy to accomplish this.
Meters vs. Miles
As with the SQL Server functions, the Distance() method returns data in meters, so if you need to work with miles or feet you need to do some conversion. Here are a couple of helpers that might be useful (can be found in GeoUtils.cs of the sample project):/// <summary>
/// Convert meters to miles
/// </summary>
/// <param name="meters"></param>
/// <returns></returns>
public static double MetersToMiles(double? meters)
{
if (meters == null)
return 0F;
return meters.Value * 0.000621371192;
}
/// <summary>
/// Convert miles to meters
/// </summary>
/// <param name="miles"></param>
/// <returns></returns>
public static double MilesToMeters(double? miles)
{
if (miles == null)
return 0;
return miles.Value * 1609.344;
}
Using these two helpers you can query on miles like this:[TestMethod]
public void QueryLocationsMilesTest()
{
var sourcePoint = CreatePoint(45.712113, -121.527200);
var context = new GeoLocationContext();
// find any locations within 5 miles ordered by distance
var fiveMiles = GeoUtils.MilesToMeters(5);
var matches =
context.Locations
.Where(loc => loc.Location.Distance(sourcePoint) <= fiveMiles)
.OrderBy(loc => loc.Location.Distance(sourcePoint))
.Select(loc => new { Address = loc.Address, Distance = loc.Location.Distance(sourcePoint) });
Assert.IsTrue(matches.Count() > 0);
foreach (var location in matches)
{
Console.WriteLine("{0} ({1:n1} miles)", location.Address, GeoUtils.MetersToMiles(location.Distance));
}
}
which produces:
301 15th Street, Hood River (0.0 miles)The Hatchery, Bingen (0.5 miles)Kaze Sushi, Hood River (0.7 miles)
Nice 'n simple.
.NET 4.5 Only
Note that DbGeography and DbGeometry are exclusive to Entity Framework 5.0 (not 4.4 which ships in the same NuGet package or installer) and requires .NET 4.5. That's because the new DbGeometry and DbGeography (and related) types are defined in the 4.5 version of System.Data.Entity which is a CLR assembly and is only updated by major versions of .NET. Why this decision was made to add these types to System.Data.Entity rather than to the frequently updated EntityFramework assembly that would have possibly made this work in .NET 4.0 is beyond me, especially given that there are no native .NET framework spatial types to begin with.
I find it also odd that there is no native CLR spatial type. The DbGeography and DbGeometry types are specific to Entity Framework and live on those assemblies. They will also work for general purpose, non-database spatial data manipulation, but then you are forced into having a dependency on System.Data.Entity, which seems a bit silly. There's also a System.Spatial assembly that's apparently part of WCF Data Services which in turn don't work with Entity framework. Another example of multiple teams at Microsoft not communicating and implementing the same functionality (differently) in several different places. Perplexed as a I may be, for EF specific code the Entity framework specific types are easy to use and work well.
Working with pre-.NET 4.5 Entity Framework and Spatial Data
If you can't go to .NET 4.5 just yet you can also still use spatial features in Entity Framework, but it's a lot more work as you can't use the DbContext directly to manipulate the location data. You can still run raw SQL statements to write data into the database and retrieve results using the same TSQL syntax I showed earlier using Context.Database.ExecuteSqlCommand().
Here's code that you can use to add location data into the database:[TestMethod]
public void RawSqlEfAddTest()
{
string sqlFormat =
@"insert into GeoLocations( Location, Address) values
( geography::STGeomFromText('POINT({0} {1})', 4326),@p0 )";
var sql = string.Format(sqlFormat,-121.527200, 45.712113);
Console.WriteLine(sql);
var context = new GeoLocationContext();
Assert.IsTrue(context.Database.ExecuteSqlCommand(sql,"301 N. 15th Street") > 0);
}
Here I'm using the STGeomFromText() function to add the location data.
Note that I'm using string.Format here, which usually would be a bad practice but is required here. I was unable to use ExecuteSqlCommand() and its named parameter syntax as the longitude and latitude parameters are embedded into a string. Rest assured it's required as the following does not work:string sqlFormat =
@"insert into GeoLocations( Location, Address) values
( geography::STGeomFromText('POINT(@p0 @p1)', 4326),@p2 )";context.Database.ExecuteSqlCommand(sql, -121.527200, 45.712113, "301 N. 15th Street")
Explicitly assigning the point value with string.format works however.
There are a number of ways to query location data. You can't get the location data directly, but you can retrieve the point string (which can then be parsed to get Latitude and Longitude) and you can return calculated values like distance.
Here's an example of how to retrieve some geo data into a resultset using EF's and SqlQuery method:[TestMethod]
public void RawSqlEfQueryTest()
{
var sqlFormat =
@"
DECLARE @s geography
SET @s = geography:: STGeomFromText('POINT({0} {1})' , 4326);
SELECT
Address,
Location.ToString() as GeoString,
@s.STDistance( Location) as Distance
FROM GeoLocations
ORDER BY Distance";
var sql = string.Format(sqlFormat, -121.527200, 45.712113);
var context = new GeoLocationContext();
var locations = context.Database.SqlQuery<ResultData>(sql);
Assert.IsTrue(locations.Count() > 0);
foreach (var location in locations)
{
Console.WriteLine(location.Address + " " + location.GeoString + " " + location.Distance);
}
}
public class ResultData
{
public string GeoString { get; set; }
public double Distance { get; set; }
public string Address { get; set; }
}
Hopefully you don't have to resort to this approach as it's fairly limited. Using the new DbGeography/DbGeometry types makes this sort of thing so much easier. When I had to use code like this before I typically ended up retrieving data pks only and then running another query with just the PKs to retrieve the actual underlying DbContext entities. This was very inefficient and tedious but it did work.
Summary
For the current project I'm working on we actually made the switch to .NET 4.5 purely for the spatial features in EF 5.0. This app heavily relies on spatial queries and it was worth taking a chance with pre-release code to get this ease of integration as opposed to manually falling back to stored procedures or raw SQL string queries to return spatial specific queries. Using native Entity Framework code makes life a lot easier than the alternatives. It might be a late addition to Entity Framework, but it sure makes location calculations and storage easy. Where do you want to go today? ;-)
Resources
Download Sample Project© Rick Strahl, West Wind Technologies, 2005-2012Posted in ADO.NET Sql Server .NET
Tweet
!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src="//platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs");
(function() {
var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;
po.src = 'https://apis.google.com/js/plusone.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);
})();
