[postgis-users] ST_Collect

[Carsten Hogertz]

Thanks alot. Now it is working!

--
Carsten


2013/10/11 Nicolas Ribot <nicolas.ribot at gmail.com>

> 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
> _______________________________________________
> 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/5bfea600/attachment.html>