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:enter image description here

The output table is as follows:enter image description here

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:enter image description here

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.


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

Put indexes on the gid columns...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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