Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
postgis split polygons with polygons
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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.