Thanks alot. Now it is working! -- Carsten
2013/10/11 Nicolas Ribot <[email protected]> > array_agg is collecting all the values during the group by a.poi_id, > a.geom. > As you link on desc_string, this will be always the same values. > You can extract the first one using the [] array notation: > > select ..., descs[1] as desc, .... > > Nicolas > > On 11 October 2013 15:10, Carsten Hogertz <[email protected]> > wrote: > > Hey that's perfect. > > The only thing when I use the array_agg is that I normaly get this: > > > > "{"Moth Theatre Company"}" > > > > but in some cases I get this: > > > > "{"Sacred Fools Theater Company","Sacred Fools Theater Company","Sacred > > Fools Theater Company"}" > > > > Any idea why this happens? > > Thanks > > Carsten > > > > > > 2013/10/11 Nicolas Ribot <[email protected]> > >> > >> Hi, > >> > >> you could store initial information in a array of values of > >> desc_string or other attribute, using array_agg function (or create a > >> concatenated string using string_agg function): > >> > >> ... > >> SELECT a.poi_id, > >> a.geom, > >> array_agg(desc_string) as descs, > >> St_collect(b.geom) AS b_geom > >> > >> > >> Nicolas > >> > >> > >> On 11 October 2013 13:16, Carsten Hogertz <[email protected]> > >> wrote: > >> > Thanks Hugues, > >> > you helped alot. I added some text to your SQL like the following: > >> > > >> > WITH myselect > >> > > >> > AS (SELECT a.poi_id, > >> > > >> > a.geom, > >> > > >> > St_collect(b.geom) AS b_geom > >> > > >> > FROM > >> > > >> > ciss_poi a, ciss_poi b > >> > > >> > WHERE > >> > > >> > St_dwithin(a.geom, b.geom, ( 0.5 / 111.111 )) > >> > > >> > AND a.poi_id != b.poi_id > >> > > >> > AND a.desc_string = b.desc_string > >> > > >> > GROUP BY > >> > > >> > a.poi_id, a.geom) > >> > > >> > SELECT ROW_NUMBER() over (order by geom) as id, > >> > > >> > St_centroid(St_convexhull(St_collect(geom, b_geom))) as geom > >> > > >> > FROM > >> > > >> > myselect m > >> > > >> > > >> > With this statement I get the centroid of the grouped point's convex > >> > hulls. > >> > Obviously only the geography. > >> > > >> > What can I do to label the information of one of the "initial" points > >> > that > >> > formed the convex hull to the new centroid? > >> > > >> > > >> > Best Regards and thank you very much > >> > > >> > -- > >> > > >> > Carsten > >> > > >> > > >> > > >> > > >> > > >> > > >> > 2013/10/11 Hugues François <[email protected]> > >> >> > >> >> 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 > >> >> > >> >> with myselect as ( > >> >> select a.id, a.geom, st_collect(b.geom) as b_geom from mytable > a, > >> >> mytable b > >> >> where st_dwithin(a.geom, b.geom, 500) > >> >> and a.id != b.id > >> >> group by a.id, a.geom > >> >> ) > >> >> > >> >> select a.id, st_collect(geom, b_geom) from myselect > >> >> > >> >> Hugues. > >> >> > >> >> ________________________________ > >> >> From: [email protected] > >> >> [mailto:[email protected]] On Behalf Of Carsten > >> >> Hogertz > >> >> Sent: Friday, October 11, 2013 9:30 AM > >> >> To: PostGIS Users Discussion > >> >> Subject: [postgis-users] ST_Collect > >> >> > >> >> Hello, > >> >> > >> >> I've got a question about using ST_Collect. > >> >> > >> >> 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. > >> >> > >> >> 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? > >> >> > >> >> And can I use the ST_DWithin inside the ST_Collect? Like > >> >> ST_Collect(ST_DWithing(geom,geom,distance))? > >> >> > >> >> Thanks for your help! > >> >> -- > >> >> Carsten > >> >> > >> >> _______________________________________________ > >> >> postgis-users mailing list > >> >> [email protected] > >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> > > >> > > >> > > >> > _______________________________________________ > >> > postgis-users mailing list > >> > [email protected] > >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
