Search nearby points from a geography column

geolocation, sql, sql-server-2008-r2, sqlgeography

Solution

When you say 'while making use of the index' - how do you mean?

I've only just started (today) experimenting with SQL 2012 and its `Geography` datatype, but maybe the following is useful - I do realise you're using `2008-R2`

Create some data:

CREATE TABLE SpatialTable 
    ( id int IDENTITY (1,1) PRIMARY KEY,
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeogCol1)
VALUES 
(geography::STGeomFromText('POINT(-122.360 47.656)',4326)),
(geography::STGeomFromText('POINT(-122.343 47.656)',4326)),
(geography::STGeomFromText('POINT(-122.358 47.660)',4326)),
(geography::STGeomFromText('POINT(-122.348 47.649)',4326)),
(geography::STGeomFromText('POINT(-122.348 47.658)',4326)),
(geography::STGeomFromText('POINT(-122.358 47.653)',4326))

Create index:

CREATE SPATIAL INDEX Spatialindex ON SpatialTable ( GeogCol1 ) USING GEOGRAPHY_GRID 
WITH (
    GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
    CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
)

Define point of interest:

DECLARE @g geography;
SET @g = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);

Find closest 2, within 750m of point

SELECT TOP 2
        @g.STDistance(st.GeogCol1) AS [DistanceFromPoint (in meters)] 
    ,   st.GeogCol2
    ,   st.id
FROM    SpatialTable st WITH(INDEX(SpatialIndex))
WHERE   @g.STDistance(st.GeogCol1) <= 750
ORDER BY @g.STDistance(st.GeogCol1) ASC

Gives:

DistanceFromPoint (in meters) GeogCol2                 id
----------------------------- ------------------------ -----------
234.715604015178              POINT (-122.348 47.649)  4
711.760044795868              POINT (-122.358 47.653)  6

The `SQL Execution Plan` suggests the index is being used, and this website suggests the distances returned are correct

Let me know if this isn't what you need, I'm trying to learn this myself so any revision is good!

*** Edit **** I've added an index table hint to force the query to use the index (as per this MSDN post). The link explains the index probably wasn't used initially as SQL determined it would be faster not to use the index. The Execution Plan of my example above shows a 97%/3% split of using/not using the index, so in this instance the SQL optimiser had the right idea.

Why queries are slower when using the index appears to be the subject of numerous blog posts and questions here on SO.

Problem

I have a geography typed column in my table with spatial index. How can I select the top N rows that is within X meters of a given Latitude/Longitude while making use of the index to improve performance?

Original source