<div dir="ltr"><div><div><div>Hi Birgit, thank you very much for your reply.<br></div>I'm going to test your code asap and let you know about it.<br></div>Regards,<br></div>Paolo<br><div><div><div><div><div class="gmail_extra"><br><div class="gmail_quote">2016-09-22 9:47 GMT+02:00 Birgit Laggner <span dir="ltr"><<a href="mailto:birgit.laggner@thuenen.de" target="_blank">birgit.laggner@thuenen.de</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
Hi Paolo,<br>
<br>
sorry, I did not really think it through, but now the changed and
tested example:<br>
<br>
DO $$<br>
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;<br>
BEGIN<br>
<br>
distance := 200;<br>
t_schema := 'p_sam';<br>
input_table := 'cluster_test_input';<br>
output_table := 'cluster_test_output';<span class=""><br>
<br>
<br>
CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;<br>
<br>
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table) INTO r;<br>
<br>
gid := r.gid;<br>
geom := r.geom;<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT '||gid;<br>
<br></span>
LOOP<br>
<br>
EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as geom<span class=""><br>
FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table)||'<br>
WHERE gid NOT IN (SELECT gid FROM clustered) AND
ST_DWithin($1, geom, $2)'<br>
INTO c USING geom, distance;<br>
<br>
c_gid := c.gid;<br></span>
c_geom := c.geom || array[geom]::geometry[];<span class=""><br>
<br>
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;<br>
<br></span>
geom := ST_ForceCollection(ST_Collect(<wbr>c_geom));<br>
<br>
EXIT WHEN c IS NULL;<br>
<br>
END LOOP;<br>
<br>
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(output_table)||'
VALUES($1)' USING geom;<span class=""><br>
<br>
LOOP<br>
BEGIN<br>
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table)||'<br>
WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;<br>
<br>
gid := r.gid;<br>
geom := r.geom;<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT '||gid;<br>
<br></span>
LOOP<br>
<br>
EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as
geom<span class=""><br>
FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table)||'<br>
WHERE gid NOT IN (SELECT gid FROM clustered) AND
ST_DWithin($1, geom, $2)'<br>
INTO c USING geom, distance;<br>
<br>
c_gid := c.gid;<br></span>
c_geom := c.geom || array[geom]::geometry[];<span class=""><br>
<br>
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING
c_gid;<br>
<br></span>
geom := ST_ForceCollection(ST_Collect(<wbr>c_geom));<br>
<br>
EXIT WHEN c IS NULL;<br>
<br>
END LOOP;<span class=""><br>
<br>
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(output_table)||'<br></span>
VALUES ($1)' USING geom;<span class=""><br>
<br>
EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;<br>
END;<br>
END LOOP;<br>
<br>
END $$<br>
<br>
<br>
Regards,<br>
<br>
Birgit<br>
<pre cols="72"></pre>
</span><div><div class="h5"><div>Am 21.09.2016 um 14:53 schrieb Birgit
Laggner:<br>
</div>
<blockquote type="cite">
Hi Paolo,<br>
<br>
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.<br>
<br>
DO $$<br>
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;<br>
BEGIN<br>
<br>
distance := ??;<br>
t_schema := '??';<br>
input_table := '??';<br>
output_table := '??';<br>
<br>
<br>
CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;<br>
<br>
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table) INTO r;<br>
<br>
gid := r.gid;<br>
geom := r.geom;<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT '||gid;<br>
<br>
EXECUTE 'SELECT array_agg(gid) as gid,
ST_ForceCollection(ST_Collect(<wbr>ST_Collect(geom),$2)) as geom FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table)||' WHERE
gid != $1 AND ST_DWithin($2, geom, $3)' INTO c USING gid, geom,
distance;<br>
<br>
c_gid := c.gid;<br>
c_geom := c.geom;<br>
<br>
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(output_table)||'
VALUES($1)' USING c_geom;<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;<br>
<br>
LOOP<br>
BEGIN<br>
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table)||'<br>
WHERE gid NOT IN (SELECT gid FROM clustered)' INTO r;<br>
<br>
gid := r.gid;<br>
geom := r.geom;<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT '||gid;<br>
<br>
EXECUTE 'SELECT array_agg(gid) as gid,
ST_ForceCollection(ST_Collect(<wbr>ST_Collect(geom),$1)) as geom<br>
FROM
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(input_table)||'<br>
WHERE gid NOT IN (SELECT gid FROM clustered) AND
ST_DWithin($1, geom, $2)'<br>
INTO c USING geom, distance;<br>
<br>
c_gid := c.gid;<br>
c_geom := c.geom;<br>
<br>
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'|<wbr>|quote_ident(output_table)||'<br>
VALUES ($1)' USING c_geom;<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;<br>
<br>
EXCEPTION WHEN SQLSTATE '22004' THEN EXIT;<br>
END;<br>
END LOOP;<br>
<br>
END $$<br>
<br>
Regards,<br>
<br>
Birgit<br>
<div>Am 21.09.2016 um 07:47 schrieb Paolo
Importuni:<br>
</div>
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>
<div>Hi all,<br>
</div>
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.<br>
</div>
The question is: is there a way to aggregate geometries
like ST_ClusterWIthin does? Anybody can provide any
working examples?<br>
<br>
</div>
Thanks and regards<br>
<br>
</div>
Paolo I.<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>______________________________<wbr>_________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
<br>
<fieldset></fieldset>
<br>
<pre>______________________________<wbr>_________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div></div></div>
<br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div></div></div></div></div></div>