Separate isolated polygons using PostGIS

Question

I am new to PostgreSQL and have been trying to separate polygon features in a PostGIS Table into 2 different Tables based on a condition. All polygons which touch or overlap with one another needs to be added to one table; this I have achieved with the following query:

CREATE TABLE public.table_touches AS
(SELECT a.gid 
     , a.geom
  FROM public.sample_data a
  JOIN public.sample_data b ON a.geom && b.geom AND ST_Touches(a.geom, b.geom) or ST_Overlaps(a.geom,b.geom)
WHERE a.gid <> b.gid )
;

The initial sample data looks like this:

The output table is as follows:

This all seems to work well and efficient enough for me.

However, what I now need to do is to add the "removed" polygons to a separate table. My desired output for this sample data should look like this:

I have tried the following query with no luck:

CREATE TABLE public.table_isolated AS
(SELECT a.gid 
     , a.geom
  FROM public.sample_data a
  JOIN public.sample_data b ON a.geom && b.geom AND NOT (ST_Touches(a.geom, b.geom) OR ST_Overlaps(a.geom,b.geom) OR ST_Intersects(a.geom,b.geom))
 WHERE a.gid <> b.gid )
 ;

Any ideas on how I can create this "table_isolated" Table?

The input table contains around 600 000 polygons, I suspect approximately 5-10% of these will be "isolated". I am also using a spatial index on the input Table.

Answer 1

If you've already got the touching polygons in a new table, then the isolated polygons are those that are in sample_data and not in table_touches.

i.e. (assuming gid is a unique key)

CREATE TABLE table_isolated AS
SELECT s.gid,s.geom from sample_data s
LEFT JOIN table_touches t ON t.gid = s.gid
WHERE t.gid IS NULL;

Put indexes on the gid columns...