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.

Belgae

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

Mou

Love this example, although strangely the distances seem to be out by a fair bit - maybe this is to do with my lack of knowledge regarding the spatial features of SQL Server, but even so, I would have presumed that by adding a column '.STDistance(@p) / 1609.34' it would return the distance in miles, seems to be way off though - or is that me? – Belgae

Figured it out, when creating a 'POINT' from 'STGeomFromText' it is required that lat/long be inputed the other way round, e.g. long/lat. – Belgae

Answer 2

You calculate the distance between the two points and compare this distance to the given radius.

For calculating short distances, you can use the formula at Wikipedia - Geographical distance - Spherical Earth projected to a plane, which claims to be "very fast and produces fairly accurate result for small distances".

According to the formula, you need the difference in latitudes and longitudes and the mean latitude

with geo as (select g1.id, g1.latitude as lat1, g1.longitude as long1, g1.radius,
                    g2.latitude as lat2, g2.longitude as long2
             from geography g1
             join geography g2 on g2.location = 'shurdington'
                               and g1.location <> 'shurdington')
     base as (select id,
                     (radians(lat1) - radians(lat2)) as dlat,
                     (radians(long1) - radians(long2)) as dlong,
                     (radians(lat1) + radians(lat2)) / 2 as mlat, radius
              from geo)
     dist as (select id,
                     6371.009 * sqrt(square(dlat) + square(cos(mlat) * dlong)) as distance,
                     radius
              from base)
select id, distance
from dist
where distance <= radius

I used the with selects as intermediate steps to keep the calculations "readable".

Fool

Why do it yourself, given that "using SQL Servers spatial features are an option" ? – Pastorale

@Pastorale Because it was an interesting exercise. – Fool

Answer 3

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.

Scarcely