SQL Server 2008空间:在多边形中查找点 [英] SQL Server 2008 Spatial: find a point in polygon

问题描述

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

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.

我找不到使用新空间数据类型的任何示例.目前，我有一种变通方法，该变通方法已在多年前实施，但是它有一些缺点.

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.

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

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

谢谢.

更新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

推荐答案

我认为地理方法STIntersects()可以满足您的要求:

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

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)

这篇关于SQL Server 2008空间:在多边形中查找点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！