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

SELECT ST_TRANSFORM(unnest(join_touching(the_geom)),3857) the_geom_webmercator 
FROM eschbacher.moran_clusters_sample
WHERE quads='HH' and SIGNIFICANCE < 0.05

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(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='{}');