Hi Paolo,
here an example how it could work (mind that you have to replace the
??). You could also recode the DO block into a function if you would
like that better.
DO $$
DECLARE distance numeric; t_schema varchar; input_table varchar;
output_table varchar; r record; gid integer; geom geometry; c record;
c_gid integer[]; c_geom geometry;
BEGIN
distance := ??;
t_schema := '??';
input_table := '??';
output_table := '??';
CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'||quote_ident(input_table) INTO r;
gid := r.gid;
geom := r.geom;
EXECUTE 'INSERT INTO clustered SELECT '||gid;
EXECUTE 'SELECT array_agg(gid) as gid,
ST_ForceCollection(ST_Collect(ST_Collect(geom),$2)) as geom FROM
'||quote_ident(t_schema)||'.'||quote_ident(input_table)||' WHERE gid !=
$1 AND ST_DWithin($2, geom, $3)' INTO c USING gid, geom, distance;
c_gid := c.gid;
c_geom := c.geom;
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'||quote_ident(output_table)||' VALUES($1)'
USING c_geom;
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;
LOOP
BEGIN
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;
gid := r.gid;
geom := r.geom;
EXECUTE 'INSERT INTO clustered SELECT '||gid;
EXECUTE 'SELECT array_agg(gid) as gid,
ST_ForceCollection(ST_Collect(ST_Collect(geom),$1)) as geom
FROM '||quote_ident(t_schema)||'.'||quote_ident(input_table)||'
WHERE gid NOT IN (SELECT gid FROM clustered) AND
ST_DWithin($1, geom, $2)'
INTO c USING geom, distance;
c_gid := c.gid;
c_geom := c.geom;
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
VALUES ($1)' USING c_geom;
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;
EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;
END;
END LOOP;
END $$
Regards,
Birgit
Am 21.09.2016 um 07:47 schrieb Paolo Importuni:
Hi all,
I need to run a query on my postgres/postgis (9.3/2.0) that uses
ST_CLUSTERWITHIN function. This function is available since postgis
2.2.0 so I should do a soft upgrade on my ubuntu server. Since we
have a kind of customer demo in a few days and have no much time
left, I am not willing to change our setup right now, and I'd rather
do it after that meeting.
The question is: is there a way to aggregate geometries like
ST_ClusterWIthin does? Anybody can provide any working examples?
Thanks and regards
Paolo I.
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
