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.geom
s that spatially overlap with reference.geom
s, 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).
(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.explain (buffers, analyze)
plans, and the query/ies you have used