How to search by radius depending on latitude and longitude?

Question

I want to show data based on latitude and longitude with a given radius.

Example:

I have a record with latitude 55.0628 and longitude -162.3056 without the specified state.

How can I show only records that are in the same state using an object for linq?

If I have the state of Florida, to show only records that are in Florida.

Table Data id item latitude longitude 1 townhome 55.0628 -162.3056 Table postal codes id state city latitude longitude 1 alaska Akutan 54.143 -165.7854 2 Alabama Huntsville 34.7448 -86.6704 

Answer 1

I would execute the query as close as possible to the actual data (which probably means traversing LINQ and calling the stored procedure).

Here is the user-defined SQL function that I use to calculate the distance between two locations. It uses the new geography functionality introduced in SQL Server 2008.

 CREATE FUNCTION [dbo].[GetDistanceBetween] ( @Lat1 float, @Long1 float, @Lat2 float, @Long2 float ) RETURNS float AS BEGIN DECLARE @RetVal float; SET @RetVal = ( SELECT geography::Point(@Lat1, @Long1, 4326).STDistance(geography::Point(@Lat2, @Long2, 4326)) / 1609.344 ); RETURN @RetVal; END 

The function returns the distance in miles and very quickly in my experience (this will obviously depend on how many comparisons you need to make).

You could call it using something like:

 DECLARE @StartingLatitude FLOAT, @StartingLongitude FLOAT; DECLARE @MaxDistance FLOAT = 50; SELECT * FROM PostalCodes WHERE dbo.GetDistanceBetween(@StartingLatitude, @StartingLongitude, latitude, longitude) <= @MaxDistance; 

Answer 2

To find out that the destination is in a given larger region, you need not just to have the coordinates of the point in a larger area, but the coordinates that display its entire border.

If you have this data, this becomes a Point in Polygon problem. I found that ray tracing is pretty easy to implement in both SQL and C #, so it's good to have the function you create Linq, although I only did Linq2SQL, not Linq2Entities.

If you have only the coordinates of the center points, you can find the nearest such point using STDistance . This, however, can easily misidentify the location near the boundary of the larger state as being in the smaller state, since it is close to the center of the smaller state than the larger one.

If you are done with it, but you are not using SQL 2008 (so it is not STDistance), you can get a rough approximation by ordering at ((lat1 - lat2) * (lat1 - lat2)) + ((lng1 - lng2) * (lng1 - lng2)) . This gives a relative distance, as if the world were indeed a rectangle, as the Mercator projection suggests. Inaccuracy becomes worse the farther from the equator, so this may be almost acceptable for the US states, but not with the Canadian provinces.

Answer 3

The right and fast way is to start with your zip code password pair and find the points of East, West, North and South for a given distance (radius). Follow this link for the relevant formulas. Then use these points to retrieve zip codes from the database; sort of:

 select * from zipcodes where lat >= South and lat <= North and lon >= West and lon <= East -- and state = 'FL' -- use it optionally 

Now that all the zip entries in the square defined by the east, north, west and south points (as lines) continue with distance calculations (use the Tim code) and save only the entries in the radius. (If all the points in the square where they are written, then something like 22% will be outside the radius).
Obviously, if you use CTE, you will execute the entire sql transaction with only one query.