1

I am trying to geom columns from multiple table and later combining with a table which only has linestrings, I am finding lines which are not intersecting OR just touching the union of geom columns. But it's very slow. The result of union is single row with size ~ 160kb. I have tried using ST_Subdivide but still the result is same. I also tried using ST_DWithin() but still no luck.

WITH union_table AS (
    SELECT ST_Union(geom) AS geom FROM (
    SELECT geom FROM table1
    UNION ALL
    SELECT geom FROM table2)
),
intersection_table AS (
    SELECT table3.geom FROM table3
    LEFT JOIN union_table ut ON ut.something = table3.something
    WHERE
    (ST_Intersects(table3.geom, ut.geom) = false OR
    ST_Touches(table3.geom, ut.geom) = true)
)
SELECT geom FROM intersection_table
16
  • 1
    Do your tables have spatial indexes?
    – Bera
    Commented Feb 6, 2023 at 17:52
  • In (ST_intersects(table3.geom, ut.geom) comparing with union feels heavy and not necessary. If table3.geom intersects with any geometry in table1 or table2 then it intersects with the union as well. But you seem still to require union for finding the geometries that touch the unioned geometry.
    – user30184
    Commented Feb 6, 2023 at 17:54
  • 1
    "slow" is very subjective. "it doesn't work" also and you still haven't explained what and how you have subdivided... if basic optimization doesn't work, you would have to provide timings, explain (buffers, analyze) plans, and the query/ies you have used
    – JGH
    Commented Feb 6, 2023 at 20:17
  • 3
    Geometry overlay performance is related to the product of the vertex count of the geometries involved. Therefore it is completely unsurprising that a large feature would process more slowly.
    – Vince
    Commented Feb 7, 2023 at 3:38
  • 2
    Unfortunately the current implementation of ST_Touches is sub-optimal, so its slow. Hopefully we can improve this in PostGIS.
    – dr_jts
    Commented Feb 7, 2023 at 20:51

1 Answer 1

2

Spatial non-predicates are tricky. Usually you want to inverse your problem definition to gain on performance and simplicity; e.g. in this case:

Find all target.geoms that spatially overlap with reference.geoms, then select all target.geoms that are not in the above set.

In PostgreSQL/PostGIS you would want to try a correlated exclusion filter query, i.e. using EXISTS like so:

SELECT
  tgt.*
FROM
  table3 AS tgt
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      table1 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )
UNION
SELECT
  tgt.*
FROM
  table3 AS tgt
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      table2 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )
;

or

SELECT
  tgt.*
FROM
  table3 AS tgt
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      table1 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )
  OR
  NOT EXISTS (
    SELECT
      1
    FROM
      table2 AS ref
    WHERE
      ST_Crosses(tgt.geom, ref.geom)
  )

It is possible that the planner actually runs the same execution plan for both queries, or that it may be able to spin up worker threads in the UNION'ed approach. The OR'ed approach is likely a tad faster by itself - but test yourself.

Depending on your geometry setup, you'd need switch from ST_Crosses to ST_Overlaps - see their docs for when either one is suitable. If none applies, use ST_Relate in conjunction with a && filter.

UNION (without ALL) will return a result set of distinct row value combinations - which doesn't come for free, but should beat other means of set distinction here (you could try an outer GROUP BY to compare).


Needless to say, eliminating the need to filter from two reference tables per query would certainly have the most significant effect - if you can, and plan to run this over and over, consider merging table1.geom and table2.geom into a separate relation (referenced from the source tables via PK).

3
  • @postgis-user which one was fastest? Can you share your PostgreSQL & PostGIS version info here in a comment, and, if you have the time, I'd also like to see the EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query> plans of both queries - would you mind posting them on depesz for me (you can obfuscate the plan to not share any confidential info)?
    – geozelot
    Commented Feb 10, 2023 at 19:54
  • The first approach is faster. Commented Feb 11, 2023 at 12:54
  • @postgis-user it really needs to be an OR, not an AND in the second query. That's been a dumb mistake, and also a potential performance improvement...
    – geozelot
    Commented Feb 13, 2023 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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