2

I've a large polygon (table_a) that contains a smaller polygon (table_b). Now I want to split the larger polygon by 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?

enter image description here enter image description here enter image description here

3

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;
3

There are several ways to do this for example you can use st_difference to get the part without intersection:

st_difference(table_a.geom, table_b.geom)

and st_intersection to get the intersection:

st_intersection(table_a.geom, table_b.geom) 

Example:

poly a contains poly b:

enter image description here

st_difference:

enter image description here

st_intersection:

enter image description here

EDIT1: 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.

  • Thanks but I want to edit table_a itself not create a new table with the polygon intersection. – Lunar Sea Nov 11 '15 at 9:13
  • do you want to split just one polygon or several at a time? – Thomas B Nov 11 '15 at 9:16
  • Both table_a and table_b contain a set of polygons. I want to split all polygons of table_a where st_contains(table_a.geom, table_b.geom). – Lunar Sea Nov 11 '15 at 9:24
  • I added an idea how to solve this in my answer above – Thomas B Nov 12 '15 at 9:11
  • Thanks Thomas, in the meantime I've found a workaround. It seems to solve my problem. – Lunar Sea Nov 12 '15 at 16:56
0

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?

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.