[postgis-users] ST_Collect

[Carsten Hogertz]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131011/8f8e0cba/attachment.html>