Common point in multiple polygons in SQL-CodePudding

i have two tables that contains a list geometry data Ex. (0xE6100000010CFB24190B88E44A40AADDAB69817F3740) i did the intersection of shapes between the two tables , now i'm trying to find a common point in all the intersected shapes

i tried to find the STCentroid() of each shape , but i can't find out how to find the common point in all of them


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

i tried also to aggregate all the intersected shapes

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

my problem is that i need to find the only one common point in all the shapes that intersects

CodePudding user response：

Its hard to tell from your example because (as @nbk pointed out) its difficult to reproduce what you're asking for. That said, it looks like you're looking for the STIntersection function.

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

CodePudding user response：

Not sure there's an easy/fast way to do it. One idea is to use STIntersection to create a intersection polygon of all your areas in a recursive 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)

Note that if not all polygons actually intersect, you get an emptry geom