Skip to content

Instantly share code, notes, and snippets.

@mgiraldo
Last active August 29, 2015 14:01
Show Gist options
  • Save mgiraldo/3f810f49c09d3311e964 to your computer and use it in GitHub Desktop.
Save mgiraldo/3f810f49c09d3311e964 to your computer and use it in GitHub Desktop.
given a PostGIS table `lname` with `lat` and `lon` columns and primay key `gid`, make a cluster filtering by `filtercol` column with `filterval` value and excluding `excludecol` column with `excludeval` value
CREATE OR REPLACE FUNCTION get_domains_n(lname varchar, lat varchar, lon varchar, gid varchar, radius numeric, filtercol varchar, filterval varchar, excludecol varchar, excludeval varchar)
RETURNS SETOF record AS
$$
DECLARE
lid_new integer;
dmn_number integer := 1;
outr record;
innr record;
r record;
BEGIN
DROP TABLE IF EXISTS tmp;
EXECUTE format('CREATE TEMPORARY TABLE tmp AS SELECT %I, ST_GeometryFromText('''||Concat('POINT(''||','%I','||'' ''||','%I','||'')')||''') AS geom FROM %I WHERE %I != %L AND %I = %L ', gid, lat, lon, lname, excludecol, excludeval, filtercol, filterval);
ALTER TABLE tmp ADD COLUMN dmn integer;
ALTER TABLE tmp ADD COLUMN chk boolean DEFAULT FALSE;
EXECUTE 'UPDATE tmp SET dmn = '||dmn_number||', chk = FALSE WHERE '||gid||' = (SELECT MIN('||gid||') FROM tmp)';
LOOP
LOOP
FOR outr IN EXECUTE 'SELECT '||gid||' AS gid, geom FROM tmp WHERE dmn = '||dmn_number||' AND NOT chk' LOOP
FOR innr IN EXECUTE 'SELECT '||gid||' AS gid, geom FROM tmp WHERE dmn IS NULL' LOOP
IF ST_DWithin(ST_Transform(ST_SetSRID(outr.geom, 4326), 3785), ST_Transform(ST_SetSRID(innr.geom, 4326), 3785), radius) THEN
EXECUTE 'UPDATE tmp SET dmn = '||dmn_number||', chk = FALSE WHERE '||gid||' = '||innr.gid;
END IF;
END LOOP;
EXECUTE 'UPDATE tmp SET chk = TRUE WHERE '||gid||' = '||outr.gid;
END LOOP;
SELECT INTO r dmn FROM tmp WHERE dmn = dmn_number AND NOT chk LIMIT 1;
EXIT WHEN NOT FOUND;
END LOOP;
SELECT INTO r dmn FROM tmp WHERE dmn IS NULL LIMIT 1;
IF FOUND THEN
dmn_number := dmn_number + 1;
EXECUTE 'UPDATE tmp SET dmn = '||dmn_number||', chk = FALSE WHERE '||gid||' = (SELECT MIN('||gid||') FROM tmp WHERE dmn IS NULL LIMIT 1)';
ELSE
EXIT;
END IF;
END LOOP;
RETURN QUERY EXECUTE 'SELECT ST_ConvexHull(ST_Collect(geom)) FROM tmp GROUP by dmn';
RETURN;
END
$$
LANGUAGE plpgsql;
@mgiraldo
Copy link
Author

mgiraldo commented May 8, 2014

updated the gist to use format in the main EXECUTE function so it is less vulnerable

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment