2

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?

0

2 Answers 2

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

enter image description here

1

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:

Test Example

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