SQL Server 2008 Spatial：在多边形中查找点

I am using SQL Server 2008 spatial data types. I have a table with all States (as polygons) as data type GEOMETRY. Now I want to check if a point's coordinates (latitudes, longitudes) as data type GEOGRAPHY, is inside that State or not.

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

I could not find any example using the new spatial data types. Currently, I have a workaround which was implemented many years ago, but it has some drawbacks.

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

I've both SQL Server 2008 and 2012. If the new version has some enhancements, I can start working in it too.

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

Thanks.

谢谢。

UPDATE 1:

更新1：

I am adding a code sample for a bit more clarity.

我正在添加一个代码示例，以便更清晰。

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

4 个解决方案

#1

I think the geography method STIntersects() will do what you want:

我认为地理方法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)

#2

If you cannot change the data-type for the stored polygons to GEOGRAPHY then you can convert the input latitude and longitude to GEOMETRY and use the STContains or STIntersects against the converted value.

如果无法将存储多边形的数据类型更改为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);

Going the opposite direction -- trying to convert the GEOMETRY polygons to GEOGRPAHY -- is error-prone and likely to fail from my experience.

走向相反的方向 - 尝试将GEOMETRY多边形转换为GEOGRPAHY - 容易出错，并且可能会失败。

And note that if you try to create the GEOMETRY point directly from the latitude and longitude values then the STContains (or STIntersects) won't work (i.e. won't give a match when they should).

请注意，如果您尝试直接从纬度和经度值创建GEOMETRY点，则STContains（或STIntersects）将不起作用（即，它们应该不会给出匹配）。

#3

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);

#4

You shouldn't be mixing Geometry and Geography. Geometry is for FLAT PLANES, Geography is for SPHEROIDS (like Earth).
你不应该混合几何和地理。几何用于平面，地理用于SPHEROIDS（如地球）。
You "should" reconcile the SRIDs to deal with this. Each SRID (e.g. 2913 = NZG2000) describes a transformation relationship. Each SRID can be used to map to/from a uniform sphere, which is how you get from one to another.
您“应该”协调SRID以解决此问题。每个SRID（例如2913 = NZG2000）描述了变换关系。每个SRID都可用于映射到统一球体/从统一球体映射，这是您从一个球体到另一个球体的方式。
Until you get to a "same" SRID on both values, many for the .STxXX functions will return NULL (you might have default 0 in both cases)
在两个值上达到“相同”的SRID之前，.STxXX函数的许多将返回NULL（在这两种情况下，您可能都有默认值0）
If they are not the same but you pretend they are, you may have errors on the edge cases.
如果它们不相同但你假装它们是相同的，那么边缘情况可能会有错误。
If you spend some "precalc" time, you can determine the top/left and bottom/right points for the bounding rects involved (and store them), and use those values in indexes to limit the records to check. Unless A T/L < B B/R and A B/R > B T/L they cannot overlap, which means a simple 4 AND numeric check in your WHERE will limit your STWithin checks
如果您花费一些“prealc”时间，则可以确定所涉及的边界反应的顶部/左侧和底部/右侧点（并存储它们），并在索引中使用这些值来限制要检查的记录。除非A T / L B T / L它们不能重叠，这意味着WHERE中的简单4 AND数字检查将限制您的STWithin检查

Here's an example I used in SRID 2193. All roads within a 3km radius of a given point, and inside a specific school zone

这是我在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

#1