SQL Server 2008空间数据：查找多边形中的点

Question

我正在使用SQL Server 2008空间数据类型。我有一个包含所有州（作为多边形）的表格，其数据类型为GEOMETRY。现在我想检查一个点的坐标（纬度、经度）是否在该州内，它的数据类型为GEOGRAPHY。

我找不到任何使用新空间数据类型的示例。目前，我有一个替代方法，但它有一些缺点。

我同时拥有SQL Server 2008和2012。如果新版本有一些增强功能，我也可以开始使用它。

谢谢。

更新1：

我添加了一个代码示例以更清楚地说明问题。

declare @s geometry  --GeomCol is of this type too.
declare @z geography --GeogCol is of this type too.

select @s = GeomCol
from AllStates
where STATE_ABBR = 'NY'

select @z = GeogCol
from AllZipCodes
where ZipCode = 10101

- Farhan

---

如果数据库中有大量记录，它的性能会很差。我尝试在160万条记录上运行此查询，平均需要2分钟才能完成。 - Jitendra Pancholi

Answer 1

我认为地理方法STIntersects()会实现你想要的功能:

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
SET @h = geography::Point(47.653, -122.358, 4326)

SELECT @g.STIntersects(@h)

- Ben Thul

---

谢谢。但是我的多边形，States，是以GEOMETRY数据类型存储的。我应该先将其更改为GEOGRAPHY变量吗？ - Farhan

啊...糟糕。我错过了那部分内容。你的州（地理数据）存储为几何数据有什么原因吗？ - Ben Thul

这个可以正常工作：geography::STGeomFromText(cast(GeomCol as varchar(max)), 4326) - Farhan

@BenThul：如果数据库中有大量记录，它的性能就会很差。我尝试在160万条记录上运行这个查询，平均需要2分钟才能完成。 - Jitendra Pancholi

只是想补充一下，对于短距离而言，几何学是很好的选择，但对于长距离来说，如果有的话，地理学更为准确。 - phipex

Answer 2

declare @g geometry
set @g=geometry::STGeomFromText('POLYGON((-33.229869 -70.891988, -33.251124 -70.476616, -33.703094 -70.508045, -33.693931 -70.891052,-33.229869 -70.891988))',0)

DECLARE @h geometry;

SET @h = geometry::STGeomFromText('POINT(-33.3906300 -70.5725020)', 0);
SELECT @g.STContains(@h);

- Leonel

Answer 3

1. 不应该混淆几何和地理。几何是用于平面，而地理是用于球体（例如地球）。
2. 为了处理这个问题，"应该"协调SRID。每个SRID（例如2913 = NZG2000）描述了一个转换关系。每个SRID可以用于映射到/从统一的球体，这就是你从一个到另一个的方式。
3. 除非两个值都具有相同的SRID，否则许多.STxXX函数将返回NULL（在这两种情况下，您可能会有默认值0）
4. 如果它们不相同但你假装它们是相同的，你可能会在边缘情况下出现错误。
5. 如果你花一些"预计算"时间，你可以确定涉及到的边界矩形的顶部/左侧和底部/右侧点（并存储它们），并在索引中使用这些值来限制要检查的记录。 除非A T/L < B B/R且A B/R > B T/L，否则它们不能重叠，这意味着你的WHERE中的简单4个AND数字检查将限制你的STWithin检查

这是我在SRID 2193中使用的示例。所有距离给定点3公里以内，并且在特定学区内的道路。

DECLARE @g geometry

SELECT @g = GEO2193 FROM dbo.schoolzones WHERE schoolID = 319

SELECT DD.full_road_name, MIN(convert(int,  dd.address_number)), MAX(convert(int,  dd.address_number))
FROM (

select A.* from dbo.[street-address] A

WHERE (((A.Shape_X - 1566027.50505) * (A.Shape_X - 1566027.50505)) + ((A.Shape_Y - 5181211.81675) * (A.Shape_Y - 5181211.81675))) < 9250000

and a.shape_y > 5181076.1943481788

and a.shape_y < 5185097.2169968253

and a.shape_x < 1568020.2202472512

and a.shape_x > 1562740.328937705

and a.geo2193.STWithin(@g) = 1
) DD
GROUP BY DD.full_road_name
ORDER BY DD.full_road_name

- Roger Willcocks

Answer 4

如果您无法将存储的多边形数据类型更改为GEOGRAPHY，则可以将输入的纬度和经度转换为GEOMETRY并针对转换后的值使用STContains或STIntersects。

DECLARE @PointGeography GEOGRAPHY = geography::Point(43.365267, -80.971974, 4326)
DECLARE @PointGeometry GEOMETRY = geometry::STGeomFromWKB(@PointGeography.STAsBinary(), 4326);

SELECT @PolygonGeometry.STContains(@PointGeometry);

反过来尝试将GEOMETRY多边形转换为GEOGRAPHY是容易出错并且可能会失败的，根据我的经验。

请注意，如果您尝试直接从纬度和经度值创建GEOMETRY点，则STContains（或STIntersects）将无法工作（即当它们应该匹配时不会产生匹配）。

- Christopher King

---

如果我直接使用纬度和经度创建一个点，那么要如何确定这个点是否在多边形内？有什么建议吗？ - Sandip

Answer 5

如果您有一个表格（例如：SubsriberGeo），其中一个列（例如：Location）具有地理点作为值，并且您想要查找该表格中所有在多边形内的点，以下是一种方法：

 WITH polygons
 AS (SELECT 'p1' id, 
            geography::STGeomFromText('polygon ((-113.754429 52.471834 , 1 5, 5 5, -113.754429 52.471834))', 4326) poly
),
 points
 AS (SELECT [SubscriberId],[Location] as p FROM [DatabaseName].[dbo].[SubscriberGeo])
 SELECT DISTINCT 
        points.SubscriberId, 
        points.p.STAsText() as Location
 FROM polygons
      RIGHT JOIN points ON polygons.poly.STIntersects(points.p) = 1
 WHERE polygons.id IS NOT NULL;

- grabhints