join_touching.sql

readme.md

Join Touching

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.

example

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;

join_touching.sql

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;