Is a point within a geographical radius - SQL Server 2008

Question

Given the following data, would it be possible, and if so which would be the most efficient method of determining whether the location 'Shurdington' in the first table is contained within the given radius's of any of the locations in the second table.

The GeoData column is of the 'geography' type, so using SQL Servers spatial features are an option as well as using latitude and longitude.

Location      GeoData       Latitude    Longitude
===========================================================
Shurdington   XXXXXXXXXX    51.8677979  -2.113189

ID  Location            GeoData     Latitude    Longitude   Radius
==============================================================================
1000    Gloucester      XXXXXXXXXX  51.8907127  -2.274598   10
1001    Leafield        XXXXXXXXXX  51.8360519  -1.537438   10
1002    Wotherton       XXXXXXXXXX  52.5975151  -3.061798   5
1004    Nether Langwith XXXXXXXXXX  53.2275276  -1.212108   20
1005    Bromley         XXXXXXXXXX  51.4152069  0.0292294   10

Any assistance is greatly apprecieded.

Answer 1

Create Data

CREATE TABLE #Data (
    Id int,
    Location nvarchar(50),
    Latitude decimal(10,5),
    Longitude decimal(10,5),
    Radius int
)

INSERT #Data (Id,Location,Latitude,Longitude,Radius) VALUES 
(1000,'Gloucester', 51.8907127 ,-2.274598  , 20), -- Increased to 20
(1001,'Leafield', 51.8360519 , -1.537438  , 10),
(1002,'Wotherton', 52.5975151,  -3.061798  , 5),
(1004,'Nether Langwith', 53.2275276 , -1.212108  , 20),
(1005,'Bromley', 51.4152069 , 0.0292294  , 10)

Test

Declare your point of interest as a POINT

DECLARE @p GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-2.113189 51.8677979)', 4326);

To find out if it is in the radius of another point:

-- First create a Point.
DECLARE @point GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-2.27460 51.89071)', 4326);
-- Buffer the point (meters) and check if the 1st point intersects
SELECT @point.STBuffer(50000).STIntersects(@p)

Combining it all into a single query:

select  *,
        GEOGRAPHY::STGeomFromText('POINT('+ 
            convert(nvarchar(20), Longitude)+' '+
            convert( nvarchar(20), Latitude)+')', 4326)
        .STBuffer(Radius * 1000).STIntersects(@p) as [Intersects]
from    #Data  

Gives:

Id      Location        Latitude    Longitude   Radius  Intersects
1000    Gloucester      51.89071    -2.27460    20      1
1001    Leafield        51.83605    -1.53744    10      0
1002    Wotherton       52.59752    -3.06180    5       0
1004    Nether Langwith 53.22753    -1.21211    20      0
1005    Bromley         51.41521    0.02923     10      0

Re: Efficiency. With some correct indexing it appears SQL's spatial indexes can be very quick

Answer 2

If you want to do the maths yourself, you could use Equirectangular approximation based upon Pythagoras. The formula is:

var x = (lon2-lon1) * Math.cos((lat1+lat2)/2); var y = (lat2-lat1); var d = Math.sqrt(x*x + y*y) * R;

In terms of SQL, this should give those locations in your 2nd table that contain your entry in the 1st within their radius:

SELECT *
FROM Table2 t2
WHERE EXISTS (
 SELECT 1 FROM Table1 t1
 WHERE 
  ABS (
  SQRT (
    (SQUARE((RADIANS(t2.longitude) - RADIANS(t1.longitude)) * COS((RADIANS(t2.Latitude) + RADIANS(t1.Latitude))/2))) +
    (SQUARE(RADIANS(t1.Latitude) - RADIANS(t2.Latitude)))
    ) * 6371 --Earth radius in km, use 3959 for miles
    )
    <= t2.Radius
)

Note that this is not the most accurate method available but is likely good enough. If you are looking at distances that stretch across the globe you may wish to Google 'haversine' formula.

It may be worth comparing this with Paddy's solution to see how well they agree and which performs best.