2

I have a set of geometry in my PostgreSQL table that consists of Points, Linestrings & Polygons. From this set I need to create a subset of geometries which intersects with each other. I need a method without looping through each one of them as there can be more than 1000 geometries at once.

I need to fetch the geometries that are marked in red in this image

I need to fetch the geometries that are marked in red in this image

2
  • Which data type are we talking about?
    – Erik
    Apr 8, 2021 at 11:24
  • Geometry datatype in postgres table.
    – George V
    Apr 8, 2021 at 11:28

1 Answer 1

4

Create a spatial index for your table and make a self join with SQL:

SELECT * FROM my_table a INNER JOIN my_table b ON a.geom && b.geom
WHERE ST_Intersects(a.geom, b.geom) and ST_AsText(a.geom) <> ST_AsText(b.geom);

&& is the operator for the overlaping of the minimal boundary rectangles (MBR) and it uses spatial index. For 1000 geometry it should be fast enough, if few overlapings are among the MBR of the geometries (your figure shows that).

5
  • Thanks a lot @Zoltan . This solved my issue. Final Query that i used => SELECT * FROM my_table a INNER JOIN my_table b ON a.geom && b.geom WHERE ST_Intersects(a.geom, b.geom) and st_astext(a.geom) <> st_astext(b.geom); Let me know if i can improve this even more.
    – George V
    Apr 8, 2021 at 11:56
  • 2
    I think st_astext comparison may be constly. I would use the primary key of the table in the comparison, the fastest a integer ID primary key would be.
    – Zoltan
    Apr 8, 2021 at 14:20
  • Using the key sped it up from 12 s to 2 s with 30k polygons
    – BERA
    Apr 8, 2021 at 15:52
  • 2
    Strictly speaking && is not necessary since ST_Intersects automatically includes an index filter.
    – dr_jts
    Apr 8, 2021 at 16:31
  • 1
    Note that this will add a match (row) with a.geom for each b.geom that it intersects with; in OPs example set above, the middle polygon of those three-in-a-row will be present twice. There are better and faster solutions for this kind of task than needing to use a DISTINCT on a large and heavily computed result set with unnecessarily duplicated information - mainly using an EXISTS filter, or ST_ClusterDBSCAN with eps:=0.
    – geozelot
    Apr 8, 2021 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.