[postgis-users] st_clusterwithin on postgis 2

[Birgit Laggner]

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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160921/5d16498a/attachment.html>