Splitting polygon by polygon using PostGIS

Question

I have a large polygon ('table_a') that contains a smaller polygon ('table_b'). Now I want to split the larger polygon into the smaller polygon.

Unfortunately st_split(table_a.geom, table_b.geom) isn't working

Error: Splitting a Polygon by a Polygon is unsupported.

Is there any way to split a polygon by a polygon using PostGIS?

Answer 1

There are several ways to do this for example, you can use the st_difference() function to get the part without an intersection:

st_difference(table_a.geom, table_b.geom)

and the st_intersection() function to get the intersection:

st_intersection(table_a.geom, table_b.geom)

Example:

poly a contains poly b:

st_difference:

st_intersection:

Added solution to split in the same layer:

If you don't want to create a new table you could loop through your polygons and split polygons where they contain other polygons from layer b:

In order to test this solution you can just create two polygon-layer and digitize some polygons. Make sure the solution works for you before you do this with your table as it deletes the original polygons from your table that should be split:

    CREATE TABLE public.testpolygons1 ( gid serial PRIMARY KEY, geom geometry(Polygon,3857));
    
    
    CREATE TABLE public.testpolygons2 ( gid serial PRIMARY KEY, geom geometry(Polygon,3857));

    
     -- NOW DIGITIZE SOME POLYGONS so polygons in layer polygons1 contain polygons in layer polygons2;

     -- NOW LOOP THROUGH all features from layer polygons1


     DO $$ DECLARE query_string TEXT; var_record RECORD; var_record2 RECORD; BEGIN
    FOR var_record IN
    SELECT gid,
           geom
    FROM testpolygons1 LOOP BEGIN IF(
                                       (SELECT count(*)
                                        FROM
                                          (SELECT var_record.gid
                                           FROM testpolygons2 AS b
                                           WHERE ST_Contains(var_record.geom, ST_Boundary(b.geom))) AS test)>0) THEN
    INSERT INTO testpolygons1(geom)
    SELECT (ST_Dump(ST_Split(var_record.geom,b.geom))).geom -- ADD MORE ATTRIBUTES IF NEEDED
    
    FROM
      (SELECT ST_Boundary(geom) AS geom
       FROM testpolygons2) AS b
    WHERE ST_Contains(var_record.geom, b.geom) ;
      DELETE
      FROM testpolygons1 WHERE gid=var_record.gid;
RAISE NOTICE '%', var_record.geom; 
ELSE RAISE NOTICE 'no need to split'; 
END IF; 
EXCEPTION WHEN OTHERS THEN RAISE WARNING 'Error: %',SQLERRM;
END;
END LOOP;
END$$;
    
     ------------------
     -- OR DO THE SAME WITH A FUNCTION:
    
    CREATE FUNCTION poly_split_poly() RETURNS void AS $$ 
DECLARE var_record RECORD;
BEGIN
    FOR var_record IN
    SELECT gid,
           geom
    FROM testpolygons1 LOOP BEGIN IF(
                                       (SELECT count(*)
                                        FROM
                                          (SELECT var_record.gid
                                           FROM testpolygons2 AS b
                                           WHERE ST_Contains(var_record.geom, ST_Boundary(b.geom))) AS test)>0) THEN
    INSERT INTO testpolygons1(geom)
    SELECT (ST_Dump(ST_Split(var_record.geom,b.geom))).geom -- ADD MORE ATTRIBUTES IF NEEDED before insert
    
    FROM
      (SELECT ST_Boundary(geom) AS geom
       FROM testpolygons2) AS b
    WHERE ST_Contains(var_record.geom, b.geom) ;
      DELETE
      FROM testpolygons1 WHERE gid=var_record.gid; 
--RAISE NOTICE '%', var_record.geom;
    RAISE NOTICE 'Polygon with gid % splitted',var_record.gid;
ELSE RAISE NOTICE 'no need to split'; 
END IF; 
EXCEPTION WHEN OTHERS THEN RAISE WARNING 'Error: %',SQLERRM;
END;
END LOOP;
END; $$ LANGUAGE plpgsql;
    
-- RUN THE FUNCTION:
    
SELECT poly_split_poly();

https://gist.github.com/neogis-de/4111323180849e5a8f9f

I'm not sure if there isn't an easier solution but this one worked for me.

Answer 2

You can't split a polygon with another polygon, but you can use ST_Boundary to extract the lines that make up a polygon, and then do the split. For example:

CREATE TEMPORARY TABLE testdata (id serial, geom geometry);
INSERT INTO testdata (geom) VALUES 
(ST_Buffer('POINT (0 0)', 1)),
(ST_Buffer('POINT (0.5 0)', 1));

SELECT ST_Split(a.geom, ST_Boundary(b.geom))
FROM testdata a, testdata b 
WHERE ST_Intersects(a.geom, b.geom) AND a.id != b.id;

Answer 3

Rename table_a and create a new table_a 'AS SELECT' seems to work:

ALTER TABLE my_schema.table_a RENAME TO table_a_delete;

CREATE TABLE my_schema.table_a AS
SELECT
row_number() over() AS gid,
subquery.*
FROM
(SELECT
    (ST_Dump(COALESCE(ST_Difference(table_a_delete.geom, ST_Union(table_b.geom)), table_a_delete.geom))).geom::geometry(polygon, SRID)
    FROM my_schema.table_a_delete, my_schema.table_b
    -- WHERE table_b.column_xy = 'value_xy'
    GROUP BY table_a_delete.gid
)
AS subquery;

DROP TABLE my_schema.table_a_delete;

Is there anything i might have overlooked?