SQL Server SQL中多个多边形的公共点

r8xiu3jd 于 2023-01-20 发布在其他

关注(0)|答案(2)|浏览(115)

我有两个包含列表几何数据的表例如（0xE6100000010CFB24190B88E44A40AADDAB69817F3740）我在两个表之间求了形状的交集，现在我尝试在所有相交的形状中找到一个公共点
我试图找到每个形状的STCentroid（），但我找不到如何找到所有形状的公共点

select  p1.shape_data.STIntersection(p2.shape_data).STCentroid() as inter_geometry
    from map_shapes p1
    inner join areas_map_shapes p2 on p2.shape_data.STIntersects(p1.shape_data) = 1

    where p2.shape_data.STIntersects(p1.shape_data) = 1
    and p2.shape_id = 206

我也试着把所有相交的图形

SELECT

       geometry::UnionAggregate(ss.shape_data),
       geometry::STGeomFromText( geometry::UnionAggregate(ss.shape_data).STCentroid().ToString(), 0).STY as lat,
       geometry::STGeomFromText( geometry::UnionAggregate(ss.shape_data).STCentroid().ToString(), 0).STX as lon

FROM areas_map_shapes T
         inner join map_shapes SS on SS.shape_data.STIntersects(T.shape_data) = 1

WHERE SS.shape_data.STIntersects(T.shape_data) = 1

  AND T.shape_id = 206
  and T.status = 1
  and SS.status = 1
   and T.country_id = 4

我的问题是我需要在所有相交的图形中找到唯一的一个公共点
添加图像来表示我到目前为止得到的，这显示了所有的形状与主要形状的交叉，我需要找到一个共同点，在所有这些

sql-server

来源：https://stackoverflow.com/questions/75151741/common-point-in-multiple-polygons-in-sql

2条答案

按热度按时间

kknvjkwl1#

从你的例子中很难判断，因为（正如@nbk所指出的）很难重现你所要求的。也就是说，看起来你正在寻找STIntersection函数。

DECLARE @GeometryTable TABLE(
  ID INT,
  geom GEOMETRY
)

INSERT INTO @GeometryTable (ID, Geom) VALUES (1, GEOMETRY::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0))
INSERT INTO @GeometryTable (ID, Geom) VALUES (2, GEOMETRY::STGeomFromText('POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))', 0))
INSERT INTO @GeometryTable (ID, Geom) VALUES (3, GEOMETRY::STGeomFromText('POLYGON((0 1, 0 3, 2 3, 2 1, 0 1))', 0))

SELECT 
  G1.geom.STIntersection(G2.geom).STIntersection(G3.geom)
FROM
  @GeometryTable G1
INNER JOIN
  @GeometryTable G2
ON
  G1.geom.STIntersects(G2.geom) = 1
INNER JOIN
  @GeometryTable G3
ON
  G1.geom.STIntersects(G3.geom) = 1
  AND G2.geom.STIntersects(G3.geom) = 1
WHERE
  G1.ID = 1
  AND G2.ID = 2
  AND G3.ID = 3

赞(0）回复(0）举报 2023-01-20

slhcrj9b2#

不确定是否有简单/快速的方法来实现。一个想法是使用STIntersection在递归CTE中创建所有区域的相交多边形：

drop table #t_geoms
create table #t_geoms (geom geometry, row_id int identity)

-- create some random data
insert into #t_geoms
select top 30 GEOMETRY::Point(ROW_NUMBER() OVER(ORDER BY object_id) * 0.01 + 10,ROW_NUMBER() OVER(ORDER BY object_id) * 0.01 + 10, 4326).STBuffer(3) x
from sys.objects 

;with cte as (
    select geom, row_id
    from #t_geoms
    where row_id = 1
    union all
    select g.geom.STIntersection(c.geom), g.row_id
    from cte c
    inner join #t_geoms g
        ON  g.row_id = c.row_id + 1
)
select top 1 geom, geom.STCentroid() AS centerPointOfIntersection
from cte
order by row_id desc
option(MAXRECURSION 0)

请注意，如果不是所有面实际相交，则会得到一个空几何

赞(0）回复(0）举报 2023-01-20

我来回答

SQL Server SQL中多个多边形的公共点

2条答案

相关问题

热门标签

最新问答