Background
I'm writing an application which finds events within a certain radius of a zip code. You can think of this like ticketmaster, where you type in your zip code and all of the concerts in the radius of x show up.
I have a database table which has the zip code, and each zip codes' Latitude and Longitude. I also have an 'EventListings' table where each 'Event' has a ZipCode field.
Problem
Currently, I'm using the Haversine formula in a Linq-to-Entities query in my service layer to find which events are within the radius. Right now, I'm using it as a filter in the where clause. I'd also like to place it in the select clause so on the website I can show "this is 4.6 miles away", etc.
I can't move this code into a separate C# method because Linq-to-Entities will complain that it can't convert it to sql, so that leaves me with duplicating the entire formula in the select statement too. This is very ugly. I tried to fix it.
What I've Tried
I edited the Entity, and added a special scalar property of "DistanceFromOrigin". I then created a stored procedure which brought back all the entity data, plus a hard coded value (for testing purposes) for the new field "DistanceFromOrigin".
I then came to realize that I can't tell entity framework to use my sproc for its select statement on the EventListings entity... Phil suggested spatials, so thats what I went with.
Question
How can I use Spatials to search for events within a radius of zip codes?
So, Using Phil's suggestion, I re-wrote a lot of this using spatials. This worked out great, you just need .NET4.5, EF5+ and sql server (2008 and above I believe). I'm using EF6 + Sql Server 2012.
The first step was to add a Geography
column to my database EventListings
table (Right click on it -> Design). I named mine Location:
Next, since I am using the EDM with database-first, I had to update my model to use the new field that I created. Then I received an error about not being able to convert Geography to double, so what I did to fix it was select the Location property in the entity, go to its properties and change its type from double to Geography
:
Then all you have to do is query your entity collection like this:
var events = EventRepository.EventListings
.Where(x => x.Location.Distance(originCoordinates) * 0.00062 <= radiusParam);
The Distance extension method gets the distance from the current object, to the "other" object that you pass in. This other object is of type DbGeography
. You just call a static method and it creates one of these puppies, then just throw your Longitude & Latitude in it as a point:
DBGeography originCoordinates = DBGeography.fromText("Point(" + originLongitude + " " + originLatitude + ")");
This isn't how I created my originCoordinates. I downloaded a separate database that had a list of all zip codes and their Latitudes & Longitudes. I added a column of type Geography
to that as well. I'll show how at the end of this answer. I then queried the zipcode context to get a DbGeography object from the Location field in the ZipCode table.
If the user wants a more specific origin than just a zipcode, I make a call to the Google Maps API (GeoCode to be more specific) and get the latitude and longitude for the users specific address via a webservice, and create a DBGeography object from the Latitude & Longitude values in the response.
I use google APIs when I create an event also. I just set the location variable like this before adding my entity to EF:
someEventEntity.Location = DBGeography.fromText("Point(" + longitudeFromGoogle+ " " + latitudeFromGoogle + ")");
How did I get the Distance Extension Method?
To get the extension method Distance
you must add a reference to your project: System.Data.Entity
After you do that you must add the using: using System.Data.Entity.Spatial;
to your class.
The Distance
extension method returns the distance with a unit of measure of meters (You can change it I think, but this is default). Here, my radius parameter was in miles so I did some math to convert.
Note: There is a DBGeography
class in System.Data.Spatial
. This is the wrong one and it would not work for me. A lot of examples I found on the internet used this one.
How to convert Lat/Long to Geography Column
So, if you were like me and downloaded a zipcode database with all the Latitude
& Longitude
columns, and then realized it didn't have a Geography column... this might help:
1) Add a Location column to your table. Set the type to Geography.
2) Execute the following sql
UPDATE [ZipCodes]
SET Location = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
How to view the details of a Geography item
So, when you query your EventListings table in Sql Server Management Studio after you've inserted some DbGeography items, you'll see the Location
column holds a hex value like: 0x1234513462346. This isn't very helpful when you want to make sure the correct values got inserted.
To actually view the latitude & longitude off of this field you must query it like so:
SELECT Location.Lat Latitude, Location.Long Longitude
FROM [EventListings]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With