[postgis-users] Finding all polygons crossing a large polygons boundary

[Mats Taraldsvik]

Hi Paul,

Thank you for the swift response and sorry for the late reply.

I have tested your solution, but it does hit all the polygons contained
inside P as well, which I want to avoid (because it takes _a lot_ of time).

My apologies since my description was very brief and didn't give a good
description of what I want to achieve. I'll try again below :)

What I have had most success with so far is the following (envelope is an
indexed geometry column):

SELECT ... FROM ST_DumpSegments(ST_Boundary(geom)) AS x(path, geom) JOIN
... ON (envelope && geom AND ST_Intersects(envelope, geom))

For some reason, I need the envelope && geom AND before ST_Intersects - for
the query to complete quickly.

I do think it could go faster if I didn't need to split the ring-linestring
into every segment, but perhaps provide (max) number of vertices in each
segment/linestring or if postgis could help  me split the ring into an
appropriate number of segments (minimizing the resulting bboxes and number
of segments).

Ideally, the query would find polygons crossing the inner rings as well.

Mats


On Tue, Dec 5, 2023 at 11:33 PM Paul Ramsey <pramsey at cleverelephant.ca>
wrote:

> Sort of ugly but…
>
> CREATE TEMPORARY TABLE p_edges AS
>   SELECT p.id,
>   st_subdivide((st_dumprings((st_dump(geom)).geom)).geom) as geom
>   FROM p;
>
> CREATE INDEX p_edges_geom_x ON p_edges USING GIST (geom);
>
> SELECT a.id, p.id
>   FROM a
>   JOIN p_edges ON ST_Intersects(a.geom, p_edges.geom)
>
> Dump P multipolygon to polygon, polygon to rings, subdivide rings into
> shorter edges, find all members of A that intersect those edges.
>
>
>
> On Dec 5, 2023, at 2:21 PM, Mats Taraldsvik via postgis-users <
> postgis-users at lists.osgeo.org> wrote:
>
> Hi,
>
> I have a large polygon P in a query against a table A where I'm only
> interested in A's polygons that touch/crosses the boundary of P.
>
> I have tried https://postgis.net/docs/ST_DumpSegments.html but it does
> generate a lot of geometries when the polygon has 100s or 1000s of points.
>
> Are there more efficient approaches to this?
>
> Regards,
> Mats Taraldsvik
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20231218/f4b4e4d5/attachment.htm>