[postgis-users] ST_Collect

[Nicolas Ribot]

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 <carsten.hogertz at gmail.com> 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 <nicolas.ribot at gmail.com>
>>
>> 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 <carsten.hogertz at gmail.com>
>> 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 <hugues.francois at irstea.fr>
>> >>
>> >> 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: postgis-users-bounces at lists.osgeo.org
>> >> [mailto:postgis-users-bounces at lists.osgeo.org] 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
>> >> postgis-users at lists.osgeo.org
>> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >
>> >
>> >
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at lists.osgeo.org
>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users