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.