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
