<div dir="ltr">Thanks Hugues,<div style>you helped alot. I added some text to your SQL like the following:</div><div style><br></div><div style><p style="margin:0px">WITH myselect </p>
<p style="margin:0px"> AS (SELECT a.poi_id, </p>
<p style="margin:0px"> a.geom, </p>
<p style="margin:0px"> St_collect(b.geom) AS b_geom </p>
<p style="margin:0px"> FROM </p><p style="margin:0px"> ciss_poi a, ciss_poi b</p>
<p style="margin:0px"> WHERE </p><p style="margin:0px"> St_dwithin(a.geom, b.geom, ( 0.5 / 111.111 )) </p>
<p style="margin:0px"> AND a.poi_id != b.poi_id </p>
<p style="margin:0px"> AND a.desc_string = b.desc_string </p>
<p style="margin:0px"> GROUP BY </p><p style="margin:0px"> a.poi_id, a.geom)</p>
<p style="margin:0px">SELECT ROW_NUMBER() over (order by geom) as id, </p>
<p style="margin:0px"> St_centroid(St_convexhull(St_collect(geom, b_geom))) as geom</p>
<p style="margin:0px">FROM </p><p style="margin:0px"> myselect m </p><p style="margin:0px"><br></p><p style="margin:0px">With this statement I get the centroid of the grouped point's convex hulls. Obviously only the geography. </p>
<p style="margin:0px">What can I do to label the information of one of the "initial" points that formed the convex hull to the new centroid?</p><p style="margin:0px"><br></p><p style="margin:0px">Best Regards and thank you very much</p>
<p style="margin:0px">--</p><p style="margin:0px">Carsten</p><p style="margin:0px"><br></p><p style="margin:0px"><br></p><p style="margin:0px"><br></p></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">
2013/10/11 Hugues François <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<u></u>
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">I think the problem is a bit more complicated. The self join
is the good approach but you can't collect the boolean output of st_dwithin. I
should have tried something like</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">with myselect as (</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"> select <a href="http://a.id" target="_blank">a.id</a>, a.geom, st_collect(b.geom)
as b_geom from mytable a, mytable b</font></span></div>
<div dir="ltr" align="left"><span> <font color="#0000ff" face="Arial">where st_dwithin(a.geom, b.geom,
500)</font></span></div>
<div dir="ltr" align="left"><span> <font color="#0000ff" face="Arial">and <a href="http://a.id" target="_blank">a.id</a> != <a href="http://b.id" target="_blank">b.id</a></font></span></div>
<div dir="ltr" align="left"><span> <font color="#0000ff" face="Arial">group by <a href="http://a.id" target="_blank">a.id</a>, a.geom</font></span></div>
<div dir="ltr" align="left"><span> <font color="#0000ff" face="Arial">)</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">select <a href="http://a.id" target="_blank">a.id</a>, st_collect(geom, b_geom) from
myselect</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div><span><font face="Arial">Hugues.</font></span></div><br>
<div dir="ltr" lang="fr" align="left">
<hr>
<font face="Tahoma"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Carsten
Hogertz<br><b>Sent:</b> Friday, October 11, 2013 9:30 AM<br><b>To:</b> PostGIS
Users Discussion<br><b>Subject:</b> [postgis-users]
ST_Collect<br></font><br></div><div><div class="h5">
<div></div>
<div dir="ltr">Hello,
<div><br></div>
<div>I've got a question about using ST_Collect.</div>
<div><br></div>
<div>I have one table with hundrets of thousands of points. Within this table I
want to collect the points that are within a 500 meter radius and calculate a
convex hull around them.</div>
<div><br></div>
<div>Since the ST_DWithin needs (geom, geom, distance) and I only have one table
with points, do I first have to perform a self join to identify the points
within 500 meters and then a ST_Collect to group these points? Or can I somehow
do it without joining the one table with itself?</div>
<div><br></div>
<div>And can I use the ST_DWithin inside the ST_Collect? Like
ST_Collect(ST_DWithing(geom,geom,distance))?</div>
<div><br></div>
<div>Thanks for your help!</div>
<div>--</div>
<div>Carsten</div></div></div></div></div>
<br>_______________________________________________<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/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>