3

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?

Phil
  • 42,255
  • 9
  • 100
  • 100
Bahamut
  • 1,929
  • 8
  • 29
  • 51

1 Answers1

7

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.

Community
  • 1
  • 1
Morphed
  • 3,527
  • 2
  • 29
  • 55
  • looks like this is what it need. i'll check if this makes use of the spatial index i added. – Bahamut Apr 17 '12 at 18:09
  • the execution plan doesn't show that it's using the spatial index – Bahamut Apr 18 '12 at 04:36
  • @Bahamut As per (http://blogs.msdn.com/b/isaac/archive/2008/08/29/is-my-spatial-index-being-used.aspx) I've added an index hint, which works – Morphed Apr 18 '12 at 08:02
  • Hey Thanks Paddy, this worked, I verified this with actual latitude and longitude from google maps. Only question I have is, this returns me actual distance between two points, as per my understanding it is air distance...correct? – Sandesh Daddi Jul 01 '15 at 13:14
  • From [here](https://msdn.microsoft.com/en-gb/library/bb933808.aspx) `STDistance() returns the shortest LineString between two geography types. This is a close approximate to the geodesic distance. The deviation of STDistance() on common earth models from the exact geodesic distance is no more than .25%. This avoids confusion over the subtle differences between length and distance in geodesic types.` – Morphed Jul 01 '15 at 14:32