C# find all Latitude and Longitude within a mile

Question

Given a lat and long value, is there any way of finding all lat and longs that are within a specified distance? I have a db table of lat and long values which are locations of let's say street lamps, given a lat long pair how could I find all those that are within a particular distance?

I guess drawing a circle from the starting point and finding all lat and longs contained would be the best way however, I don't have the skills to do this. I am a c# developer by trade but need a few pointers in the whole geocoding world.

Answer 1

You could use the Haversine Formula (see @tdammers answer) to calculate a distance between each point (Lat, Long) in your table and the given point. You will have to iterate over the entire collection in order to evaluate each point individually.

Or, if you are using SQL Server 2008, then geospatial support is built-in. Each record would store the location as a geography type (possibly in addition to two discrete columns to hold Latitude and Longitude, if it's easier to have those values broken out), and then you can construct a simple SQL query:

DECLARE @Point geography = 'POINT(-83.12345 45.12345)' -- Note: Long Lat ordering required when using WKT

SELECT * 
FROM tblStreetLamps
WHERE location.STDistance(@point) < 1 * 1609.344  -- Note: 1 mile converted to meters

Another similar possibility is to bring the SQL Spatial types into your .NET application. The redistributable is found here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=CEB4346F-657F-4D28-83F5-AAE0C5C83D52 (under Microsoft® System CLR Types for SQL Server® 2008 R2).

Then, the querying can be done via LINQ. Note: This saves you from implementing the Haversine by yourself, otherwise the process of querying would be the same.

var yourLocation = SqlGeography.Point(Latitude, Longitude, 4326);

var query = from fac in FacilityList
            let distance = SqlGeography
                          .Point(fac.Lat, fac.Lon, 4326)
                          .STDistance(yourLocation)
                          .Value
            where distance < 1 * 1609.344
            orderby distance
            select fac;

return query.Distinct().ToList();