I maintain a lot of versions of our exterior property boundary layer for our park system for different purposes, e.g. this post. What I’d like to include in this automatically managed dataset is a multi-ring buffer version of the dataset for the purpose of a careful mask with fade (also useful for showing coastal vignettes— oops, ESRI link), e.g.:
So, first we buffer:
And discover we have to union to get the result we want (dissolve in ESRI terms):
Now, I want rings, so I subtract an outer and inter buffer with a ST_Difference:
Perform this a few more times:
And drop it into my trigger statement so I never have to worry about it again:
DROP TRIGGER dissolver ON base.reservation_boundaries_public_private_cm; CREATE OR REPLACE FUNCTION dissolve_bounds() RETURNS TRIGGER AS $dissolver$ BEGIN IF(TG_OP='INSERT') THEN TRUNCATE base.reservation_boundaries_public_private_cm_dissolved; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved SELECT res, ST_Union(the_geom) AS the_geom FROM base.reservation_boundaries_public_private_cm GROUP BY res; TRUNCATE base.reservation_boundaries_cm_dissolved; INSERT INTO base.reservation_boundaries_cm_dissolved; SELECT res, ST_Union(the_geom) AS the_geom FROM base.reservation_boundaries_public_private_cm WHERE access = 1 GROUP BY res; TRUNCATE base.reservation_boundaries_cm; INSERT INTO base.reservation_boundaries_cm SELECT res, the_geom FROM base.reservation_boundaries_public_private_cm WHERE access = 1; TRUNCATE base.reservation_boundaries_private_cm; INSERT INTO base.reservation_boundaries_private_cm SELECT res, the_geom FROM base.reservation_boundaries_public_private_cm WHERE access = 0; TRUNCATE base.reservation_boundaries_public_private_cm_dissolved_mask; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask SELECT ST_Difference( ST_GeomFromText('POLYGON((2000000 860000, 2500000 860000, 2500000 460000, 2000000 460000,2000000 860000))', 102722), ST_Union(ST_Buffer(a.the_geom, 0)) ) FROM base.reservation_boundaries_public_private_cm AS a; TRUNCATE base.reservation_boundaries_public_private_cm_dissolved_mask_gradien; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 0, 50, ST_Difference( ST_GeomFromText('POLYGON((2000000 860000, 2500000 860000, 2500000 460000, 2000000 460000,2000000 860000))', 102722), ST_Union(ST_Buffer(a.the_geom, 300)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 300, 50, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 300)), ST_Union(ST_Buffer(a.the_geom, 275)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 275, 54, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 275)), ST_Union(ST_Buffer(a.the_geom, 250)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 250, 58, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 250)), ST_Union(ST_Buffer(a.the_geom, 225)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 225, 62, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 225)), ST_Union(ST_Buffer(a.the_geom, 200)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 200, 66, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 200)), ST_Union(ST_Buffer(a.the_geom, 175)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 175, 70, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 175)), ST_Union(ST_Buffer(a.the_geom, 150)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 150, 74, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 150)), ST_Union(ST_Buffer(a.the_geom, 125)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 125, 78, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 125)), ST_Union(ST_Buffer(a.the_geom, 100)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 100, 82, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 100)), ST_Union(ST_Buffer(a.the_geom, 75)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 75, 86, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 75)), ST_Union(ST_Buffer(a.the_geom, 50)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 50, 90, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 50)), ST_Union(ST_Buffer(a.the_geom, 25)) ) FROM base.reservation_boundaries_public_private_cm AS a; INSERT INTO base.reservation_boundaries_public_private_cm_dissolved_mask_gradien SELECT 25, 94, ST_Difference( ST_Union(ST_Buffer(a.the_geom, 25)), ST_Union(ST_Buffer(a.the_geom, 0)) ) FROM base.reservation_boundaries_public_private_cm AS a; END IF; RETURN NEW; END; $dissolver$ LANGUAGE plpgsql; CREATE TRIGGER dissolver AFTER INSERT ON base.reservation_boundaries_public_private_cm FOR EACH STATEMENT EXECUTE PROCEDURE dissolve_bounds();
Have a typo:
INSERT INTO base.reservation_boundaries_cm_dissolved;
SELECT res, ST_Union(the_geom)
AS the_geom
FROM base.reservation_boundaries_public_private_cm
WHERE access = 1
GROUP BY res;
Should be:
INSERT INTO base.reservation_boundaries_cm_dissolved
SELECT res, ST_Union(the_geom)
AS the_geom
FROM base.reservation_boundaries_public_private_cm
WHERE access = 1
GROUP BY res;
There is an extra semicolon at the end of the insert statement.