SQL慢的空间数据

Question

我有两个表，一个叫做Point，另一个叫做Poly。Poly表中有大约400个多边形，而Point表中有大约20万个点。我想知道某个多边形中有多少个点。我使用的查询语句是：

select COUNT(*) from point p
inner join Poly pl  on pl.GeoDataID = 101 and p.geopoint.STIntersects(pl.Poly)=1 

我还在GeoPoint和Poly列上创建了空间索引，这两列都是地理类型。

编辑：使用的是 SQL Server 2008。

问题：上述查询返回结果需要约1分钟到40秒左右，这对于我来说太慢了，因为我想实时在Google地图上显示它们。我的解决方法正确吗？还是有更好的方法来实现这一点？

- Shoaib Shaikh

---

哦，我在想PostGIS，但我认为应该是ST_Intersects而不是STIntersects。 - Paul Tomblin

抱歉，我忘了提到它是SQL Server 2008。 - Shoaib Shaikh

请执行以下查询语句：SELECT MAX(geopoint.stx), MAX(geopoint.sty), MIN(geopoint.stx), MIN(geopoint.sty) FROM point。 - Quassnoi

-73.0453 61.5297 -165.3198 33.3989。这是我从查询中得到的结果。 - Shoaib Shaikh

Answer 1

首先检查您的空间索引。您可以使用类似以下的 SQL 语句来创建它们：

CREATE SPATIAL INDEX MyIndexName ON MyTable(GeomFieldName) USING GEOMETRY_GRID
WITH (
BOUNDING_BOX =(-1493907.5664457313, 6128509.51667404, -578861.3521250226, 7703103.135644257),
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)

一个需要更改的关键参数是BOUNDING_BOX。它应该设置为您的数据的边界框，或预期数据的最大边界框。因此，如果您的数据仅限于北美，请将边界框设置为北美范围而不是全球范围。

您可以添加计算字段以显示每个要素的边界，如http://geographika.co.uk/sql-server-spatial-sql中所述，并且可以使用标准查询来查看最大范围。

还要检查使用分析器以查看索引是否实际使用。有时，您必须使用WITH（INDEX（MyIndexName））语句强制使用它们。

SELECT *
FROM MyTable
WITH (INDEX (MyIndexName))
WHERE (geometry::Point(@x,@y,3785).STWithin(MyGeomField) = 1)

我发现有时候通过点查询时，不使用空间索引可能更快，因此值得尝试。

替代方案

最后一个选择是创建触发器，当添加新记录时会在创建或更新时分配一个多边形ID。这将允许对数据进行几乎即时的查询。

最后

在最新的Denali SQL Server版本中，可以自动创建空间索引，而不是手动创建。据说STWithin和STIntersects也有性能提升。

- geographika

---

我已经尝试使用索引，但仍然很慢。性能没有改善。顺便问一下，将在多边形和点表之间使用连接，显式索引可以解决它吗？ - Shoaib Shaikh

我已经在点和多边形列上创建了SPindex，并使用默认参数（每个对象的单元格数为16，所有级别都设置为中等）。虽然我的数据仅限于美国西北部州。 - Shoaib Shaikh

尝试将边界框限制在数据范围内，以查看是否可以提高性能。 - geographika

我已经使用正确的最小和最大边界重新创建了索引。现在使用STWithin大约需要29秒，并且给出了11,000条记录的结果，而对于较少的（缩小的）记录，时间约为2-3秒。难道它不能更快吗？ - Shoaib Shaikh

以上的替代方案将为您提供即时结果。如果边界情况的准确性不是关键，您也可以尝试将多边形形状进行概括（简化）。 - geographika