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);
})();
