Smathermather's Weblog

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();