<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body 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';<br>
<br>
<br>
CREATE TEMP TABLE clustered (gid integer) ON COMMIT DROP;<br>
<br>
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'||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>
LOOP<br>
<br>
EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as geom<br>
FROM
'||quote_ident(t_schema)||'.'||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 || array[geom]::geometry[];<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING c_gid;<br>
<br>
geom := ST_ForceCollection(ST_Collect(c_geom));<br>
<br>
EXIT WHEN c IS NULL;<br>
<br>
END LOOP;<br>
<br>
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'||quote_ident(output_table)||'
VALUES($1)' USING geom;<br>
<br>
LOOP<br>
BEGIN<br>
EXECUTE 'SELECT gid, geom FROM
'||quote_ident(t_schema)||'.'||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>
LOOP<br>
<br>
EXECUTE 'SELECT array_agg(gid) as gid, array_agg(geom) as
geom<br>
FROM
'||quote_ident(t_schema)||'.'||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 || array[geom]::geometry[];<br>
<br>
EXECUTE 'INSERT INTO clustered SELECT unnest($1)' USING
c_gid;<br>
<br>
geom := ST_ForceCollection(ST_Collect(c_geom));<br>
<br>
EXIT WHEN c IS NULL;<br>
<br>
END LOOP;<br>
<br>
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'||quote_ident(output_table)||'<br>
VALUES ($1)' USING geom;<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 class="moz-signature" cols="72">
</pre>
<div class="moz-cite-prefix">Am 21.09.2016 um 14:53 schrieb Birgit
Laggner:<br>
</div>
<blockquote cite="mid:57E282B0.2070709@thuenen.de" type="cite">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
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)||'.'||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(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;<br>
<br>
c_gid := c.gid;<br>
c_geom := c.geom;<br>
<br>
EXECUTE 'INSERT INTO
'||quote_ident(t_schema)||'.'||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)||'.'||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(ST_Collect(geom),$1)) as geom<br>
FROM
'||quote_ident(t_schema)||'.'||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)||'.'||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 class="moz-cite-prefix">Am 21.09.2016 um 07:47 schrieb Paolo
Importuni:<br>
</div>
<blockquote
cite="mid:CAGoRbyess2iC8m1K=65vAeTbJ4utyfOdx2vFOK4=wfP0Q0Wv4g@mail.gmail.com"
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 class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>