Geek asks and answers

Find nearby points from a geography column

I have a geography column in my spatial index table. How can I select the top N lines that are within X meters of a given Latitude / Longitude using an index to improve performance?

+3
source share
1 answer

When you say "when using the index" - how do you understand this?

I just started (today) experimenting with SQL 2012 and its type Geography, but maybe the following is useful: I understand that you are using2008-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
)

Determine the point of interest to you:

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

Find the nearest 2, within 750 meters of the 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

SQL Execution Plan , - ,

, , , , !

*** **** , ( MSDN). , , , , SQL , . 97%/3% / , SQL .

, , , fooobar.com/questions/910741/....

+7

More articles:


All Articles