Re: [postgis-users] st_clusterwithin on postgis 2

[Paolo Importuni]

Hi Birgit, thank you very much for your reply.
I'm going to test your code asap and let you know about it.
Regards,
Paolo

2016-09-22 9:47 GMT+02:00 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 

Re: [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.