SQL Server 2008 - 使用定义国家/地区的表中的 STIntersects

发布于 2024-10-21 08:05:13 字数 740 浏览 1 评论 0原文

我有一张表，其中每一行都是新西兰地图的一部分。我真正想知道的是地图上的一个点（即国家数据）还是在水中。

我了解 StIntersects 的工作原理，但所有示例均针对单个 Polygon 或 LineString，但我有一个 LineStrings 表格- 130 行定义国家边界。

很多这样的行

LINESTRING (6252032.7308424888 -3161950.9615992079, 6252033.7275789445 -3161929.3581238855, 6252011.5227283547 -3161906.1086780191, 6251992.0438580718 -3161880.6299652755)

所以我想我需要将所有线串放在一起以使我的国家边界成为单个多边形或类似的东西，但我不知道该怎么做。

有人能给我举个例子来说明如何做到这一点吗？

原始数据来自 www.koitudes.com 中名为“新西兰海岸线”的 ShapeFile。然后，我使用 Shape2Sql 使用 Planer Geometry 导入到 SQL Server。

Shape2File 中的几何（球形）表示“数据项目或范围超出了 SqlGeography 类型支持的范围）

希望我提供了足够的信息？

干杯克里斯

原文

I have a table where each row is part of a New Zealand map. What I really want to know is is a point within the map (ie the country data) or is it in the water.

I understand how StIntersects works but all examples are for a single Polygon or LineString but I have a table of LineStrings - 130 rows that define the country border.

Lots of rows like this

LINESTRING (6252032.7308424888 -3161950.9615992079, 6252033.7275789445 -3161929.3581238855, 6252011.5227283547 -3161906.1086780191, 6251992.0438580718 -3161880.6299652755)

So I think I need to sort of put all the line strings together to make my country border a single polygon or something like that, but I do not know how to do that.

Can someone give me an example of how I could do this?

The original data was from a ShapeFile from www.koordinates.com called New Zealand Coastlines. I then used Shape2Sql to import to SQL Server using Planer Geometry.

Geometry (spheric) in Shape2File said "Data projects or extent is outside the bounds of what is supported by the SqlGeography type)

Hope I have provided enough information?

Cheers Chris

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

盗琴音 2024-10-28 08:05:13

您很可能已经不再关注此内容，但我将其发布以供将来使用。

如果我理解正确，您需要从构成多边形边界的线串表创建一个多边形？

首先，将表中的所有 LINESTRING 组合成一个几何图形，如下所示：

DECLARE @g geometry

SELECT TOP 1 @g = geom from #geom_tmp

SELECT
     @g = @g.STUnion(geom)
FROM
    #geom_tmp

SELECT @g

然后您要从单线边界创建一个多边形。单线边界字符串和多边形之间只有两点不同，首先我们将“LINESTRING”替换为“POLYGON”。然后我们添加一组括号，因为多边形的 WKT 字符串用括号分隔多个多边形，如下所示：

DECLARE @str NVARCHAR(max)
SET @str = REPLACE(REPLACE(@g.STAsText(),'(','((') + ')','LINESTRING','POLYGON')

SET @g = GEOMETRY::STGeomFromText(@str,0)

SELECT @g

这将为您提供该国家/地区的多边形。

然后，您只需查看您的点是否与多边形相交（@p 是点几何）：

SELECT @p.STIntersects(@g)

-- OUTPUT is 1 if it intersects and 0 if it does not.

或者如果您的点在表中，如果调用点表几何列，您可以选择与国家/地区相交的点列表，如下所示“观点”。

SELECT *
FROM point_table
WHERE point.STIntersects(@g) = 1

由于我们中的许多人无法很快迁移到 SQL Server 2012，因此这很方便。希望有帮助。

You have most likely moved on from this but I am posting for future use.

If I understand you correctly, you need to create one polygon from a table of LINESTRINGs that make up the boundary of the polygon?

First, combine all of the LINESTRINGs in your table into one geometry like this:

DECLARE @g geometry

SELECT TOP 1 @g = geom from #geom_tmp

SELECT
     @g = @g.STUnion(geom)
FROM
    #geom_tmp

SELECT @g

Then you want to create a polygon from your single-line boundary. There are only two things that differ between your single-line boundary string and a polygon, first we replace "LINESTRING" with "POLYGON". Then we add a set of parentheses since a polygon's WKT string separates multiple polygons with parentheses like this:

DECLARE @str NVARCHAR(max)
SET @str = REPLACE(REPLACE(@g.STAsText(),'(','((') + ')','LINESTRING','POLYGON')

SET @g = GEOMETRY::STGeomFromText(@str,0)

SELECT @g

Which will give you your polygon of the country.

Then you simply see if your point intersects the polygon (@p is the point geometry):

SELECT @p.STIntersects(@g)

-- OUTPUT is 1 if it intersects and 0 if it does not.

OR if your points are in a table, you could select the list of points that intersect the country as shown below if the point table geometry column is called "point".

SELECT *
FROM point_table
WHERE point.STIntersects(@g) = 1

Since there are many of us who cannot move to SQL server 2012 any time soon, this comes in handy. Hope it helps.

回复收藏 0 原文

千寻… 2024-10-28 08:05:13

确定两个点是否位于多边形的同一侧（内部或外部）的经典方法是计算连接两个点的线与多边形边界之间的交点数量。偶数（包括0）是同边，奇数是不同边。

因此，定义一个（任何）位于新西兰境内的点（最好是在几何中心附近），然后定义一条将其与要检查的点连接起来的线，然后计算它相交的线数。如果为 0 或偶数，则另一个点位于同一侧（即新西兰境内）。如果为奇数，则该点位于国家/地区边界之外。您无需担心排序或连接边界线。

如果该线恰好穿过两条边界线之间的连接点，情况就会变得更加棘手，但 StIntersects 不会帮助您避免这种情况，此外，如果您处理具有 17 个有效位置的实际值，发生这种情况的可能性很小。

该算法在这里很好，因为它适用于多个不相交的多边形（岛）以及单个不规则多边形。唯一的要求是所有线路都关闭。

当然，要使其正常工作，您必须将线串转换为一组单线并存储它们，因为您需要找到交点的数量，而不仅仅是事实：线与多边形相交（n 次）与 0 次。

回复收藏 0 原文

热鲨 2024-10-28 08:05:13

让我看看我是否理解你的问题。

您希望确定预定点是在新西兰境内还是在新西兰境外。现在，你已经有了新西兰的海岸线，对吧？这条海岸线大约有 130 行……但我们需要将其设为单行……然后查看该点是位于该边界之内还是之外。

如果这是正确的，那么您需要做的第一件事就是将所有线串连接到一个大型多边形中..我们需要最后的单行作为 Sql Server 2008 GEOGRAPHY 类型。

所以 - 首先一些帮助。跳转到 Codeplex 并获取此库 - sql 空间工具。在这个库中，有一个方法可以连接所有线串。我认为它叫做 GeographyUnionAggregate 。弄清楚如何运行这个存储过程，它将所有线串连接在一起（假设它们都没有混乱）。

完成此任务后，您将拥有一个代表新西兰整个海岸线的 GEOGRAPHY 行。

现在，这是一个简单的 sql 语句来查看该点是否存在/与边界（新西兰国家）相交:-

DECLARE @SomePoint GEOGRAPHY
SET @SomePoint = geography::STGeomFromText('POINT(-122.358 47.653)', 4326);
SELECT CountryId, Name
FROM Countries a
    INNER JOIN a.AdministrativeBoundary.STIntersects(@SomePoint) = 1

这应该会返回一个结果:)

Let me see if I understand your problem.

You wish to determine if a pre-determined point is either INSIDE NZ or OUTSIDE. Now, you have the coastline of NZ, right? This coastline is around 130 rows ... but we need to make this one single row .. and THEN see if the point lies inside this boundary or outside.

If this is correct, then the first thing you'll need to do is to join all the linestrings into a single massive polygon .. and we need this final single row as a Sql Server 2008 GEOGRAPHY type.

So - first some help. Jump over to Codeplex and grab this library - sql spatial tools. In this library, there is a method to JOIN all the linestrings. I think it's called GeographyUnionAggregate . Figure out how to run this stored proc which will join all the linestrings together (assuming none of them are messed up).

Once this task is done, you will have a single GEOGRAPHY row which repesents the entire coastline of NZ.

Now, it's a simple sql statement to see if the point exists/intersects the boundary (the country of NZ) or not :-

DECLARE @SomePoint GEOGRAPHY
SET @SomePoint = geography::STGeomFromText('POINT(-122.358 47.653)', 4326);
SELECT CountryId, Name
FROM Countries a
    INNER JOIN a.AdministrativeBoundary.STIntersects(@SomePoint) = 1

And that should return you one result :)

回复收藏 0 原文

~没有更多了~