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.
CREATE TABLE JOINED_GEN_Z AS
SELECT
unnest(join_touching(geom)) As the_geom,
substr(quads, 1, 1)
FROM gen_z_moran_w_geom
WHERE significance < 0.05
GROUP BY 2;| 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(g) | |
| from unnest(clusters) a(g) | |
| where ST_TOUCHES(g, new_geom)); | |
| unjoined := (select array_agg(g) | |
| from unnest(clusters) a(g) | |
| where ST_TOUCHES(g, new_geom) = false); | |
| raise notice 'joins is of dimensions %', array_dims(joins); | |
| raise notice 'unjoined is of dimensions %', array_dims(unjoined); | |
| 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, | |
| STYPE=geometry[], | |
| FINALFUNC=final_merge_touching, | |
| INITCOND='{}' | |
| ); | |
| CREATE TABLE JOINED_GEN_Z AS | |
| SELECT | |
| unnest(join_touching(geom)) As the_geom, | |
| substr(quads, 1, 1) | |
| FROM gen_z_moran_w_geom | |
| WHERE significance < 0.05 | |
| GROUP BY 2; |