[postgis-users] st_clusterwithin on postgis 2

[Birgit Laggner]

Hi Paolo,

sorry, I did not really think it through, but now the changed and tested 
example:

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[]; i record;
BEGIN

distance := 200;
t_schema := 'p_sam';
input_table := 'cluster_test_input';
output_table := 'cluster_test_output';


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;

LOOP

     EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) 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 || array[geom]::geometry[];

     EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

     geom := ST_ForceCollection(ST_Collect(c_geom));

     EXIT WHEN c IS NULL;

END LOOP;

EXECUTE 'INSERT INTO 
'||quote_ident(t_schema)||'.'||quote_ident(output_table)||' VALUES($1)' 
USING geom;

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;

     LOOP

         EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) 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 || array[geom]::geometry[];

         EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;

         geom := ST_ForceCollection(ST_Collect(c_geom));

         EXIT WHEN c IS NULL;

     END LOOP;

     EXECUTE 'INSERT INTO 
'||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
              VALUES ($1)' USING geom;

     EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;
     END;
END LOOP;

END $$


Regards,

Birgit



Am 21.09.2016 um 14:53 schrieb 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
>
>
>
> _______________________________________________
> 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/20160922/717099bd/attachment.html>