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.
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.
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
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:
After you do that you must add the using:
using System.Data.Entity.Spatial; to your class.
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
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]