Cartographic tricks and tips– making text readable, PostGIS edition, take 3

A little more refinement to the SQL for building masking fill for hand-placed text on maps.

CREATE TABLE use_area_mask AS

-- We'll use the "WITH" Common Table Expression (CTE) here
WITH exploded AS (

-- we want each individual letter to get it's own mask, so we Union and Dump them to break them out
SELECT (ST_Dump(ST_Union(geom))).geom FROM use_area_labels
)

-- now we can create a temporary table that is a 5-unit buffer of the convex hull
,buffer_cvx AS (
    SELECT ST_Buffer(ST_ConvexHull(geom), 5) AS geom FROM exploded
    )

-- finally, we Dump these out to their own records
SELECT (ST_Dump(ST_Union(geom))).geom FROM buffer_cvx