Combines and polygons which are touching in to a single polygon. Returns a geometry array so have to use unnest to get these back as geoms.
SELECT ST_TRANSFORM(unnest(join_touching(the_geom)),3857) the_geom_webmercator
FROM eschbacher.moran_clusters_sample
WHERE quads='HH' and SIGNIFICANCE < 0.05
| create or replace function final_merge_touching( joined_geoms geometry[] ) returns geometry[] as $$ | |
| BEGIN | |
| return joined_geoms; | |
| END | |
| $$ | |
| LANGUAGE plpgsql | |
| create or replace function state_merge_touching(clusters geometry[], new_geom geometry) returns geometry[] as $$ | |
| DECLARE | |
| joins geometry[] :='{}'; | |
| unjoined geometry[] :='{}'; | |
| i integer; | |
| combined geometry; | |
| BEGIN | |
| joins := (select array_agg(a) from unnest(clusters) a where ST_TOUCHES(a, new_geom)); | |
| unjoined := (select array_agg(a) from unnest(clusters) a where ST_TOUCHES(a, new_geom)= false); | |
| if array_length(joins,1) > 0 THEN | |
| joins := array_append(joins, new_geom); | |
| combined := ST_UNION(joins); | |
| ELSE | |
| combined := new_geom; | |
| END IF; | |
| unjoined := array_append(unjoined, combined); | |
| return unjoined; | |
| END | |
| $$ | |
| LANGUAGE plpgsql | |
| CREATE AGGREGATE join_touching(geometry)( sfunc= state_merge_touching , FINALFUNC= final_merge_touching ,INITCOND='{}'); |