[postgis-users] ST_Collect

[Nicolas Ribot]

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