我在我的表中有一个带有空间索引的地理类型列。如何使用索引提高性能,选择距离给定的纬度/经度X米以内的前N行?
我在我的表中有一个带有空间索引的地理类型列。如何使用索引提高性能,选择距离给定的纬度/经度X米以内的前N行?
Geography
数据类型,但也许下面的内容有用-我知道你使用的是2008-R2
创建一些数据: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 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
)
定义兴趣点:
DECLARE @g geography;
SET @g = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
找出距离点750米以内最近的2个对象。
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
提供:
DistanceFromPoint (in meters) GeogCol2 id
----------------------------- ------------------------ -----------
234.715604015178 POINT (-122.348 47.649) 4
711.760044795868 POINT (-122.358 47.653) 6
SQL执行计划
显示索引被使用了,而该网站显示返回的距离是正确的。
STDistance() 返回两个地理类型之间的最短 LineString。这是大圆距离的近似值。在常见的地球模型上,STDistance() 的偏差与精确的大圆距离之间的差异不超过0.25%。这避免了在大圆类型中长度和距离之间微妙差异的混淆。
- Morphed