How to inverse spatial query

Question

I have two tables with polygons (CTR_daim and CTA_LOW_1111). I have this query to select all polygons from CTR_daim that is intersected by any of the polygons from table CTA_LOW_1111

WITH 
  transform_t2 AS (SELECT *, ST_transform("CTA_LOW_1111".the_geom, 4619) AS geom 
  FROM "CTA_LOW_1111"),  
  transform_t1 AS (SELECT * FROM "CTR_daim")
SELECT DISTINCT(transform_t1.*), ST_asText(transform_t1.the_geom) AS geomastext FROM 
transform_t1, transform_t2 
WHERE ST_intersects(transform_t1.the_geom, transform_t2.geom)

How can I "inverse" this query to select the polygons that are not intersected by any polygons in CTA_LOW_1111?

BERA · Answer 1 · 2024-01-29T11:50:34.307

2

Left join on st_intersects and select rows where the right table rows are NULL:

select a.*
from public.table_a as a
left join public.table_b as b
on st_intersects(a.geom, b.geom)
where b.geom is null

edited Jan 29 '24 at 11:50

answered Jan 29 '24 at 06:59

BERA

72,339
13
72
161

score 1 · Answer 2 · answered Jan 29 '24 at 06:59

This will do what you require and use spatial indexes as well:

WITH 
  transform_t2 AS (SELECT *, ST_transform("CTA_LOW_1111".the_geom, 4619) AS geom  
FROM "CTA_LOW_1111"), ,  
  transform_t1 AS (SELECT * FROM "CTR_daim")
SELECT DISTINCT(transform_t1.*), ST_asText(transform_t1.geom) AS geomastext
FROM  transform_t1
LEFT JOIN
  transform_t2  ON
  ST_Intersects(transform_t1.the_geom,transform_t2.geom)
WHERE transform_t2.id IS NULL;

Here is an example I used to test for points, green=outside / red=inside any of the the polygons:

How to inverse spatial query

2 Answers2