[postgis-users] Grouping by geom with count?

[Åsmund Tokheim]

Hi

Try changing "a.the_geom != b.the_geom" to "not st_equals(a.the_geom,
b.the_geom)". By the way, if all that you want is darker colours in QGIS,
you could perhaps just play around with the opacity settings for the lines.

Åsmund


On Thu, Apr 24, 2014 at 5:22 PM, James David Smith <
james.david.smith at gmail.com> wrote:

> Thanks for the reply Hugues. Much appreciated. I've replaced the
> various bit of the query with my own table names etc, but get an
> error. Any thoughts/ideas guys?
>
> ERROR:  operator is not unique: geometry <> geometry
> LINE 15:         WHERE a.ssid=b.ssid and a.the_geom != b.the_geom
>
> WITH inter AS (
>         SELECT DISTINCT a.ssid,
> ST_CollectionExtract(st_intersection(a.the_geom, b.the_geom),2)
> the_geom
>         FROM stage a, stage b
>         WHERE st_intersects(a.the_geom, b.the_geom)
>         ),
> inter_line AS (
>         SELECT ssid, ST_UNION (the_geom) the_geom
>         FROM inter
>         GROUP BY ssid),
> diff_line AS (
>         SELECT a.ssid,
> st_union(st_collectionextract(st_difference(a.the_geom,
> b.the_geom),2)) the_geom
>         FROM stage a, inter_line b
>         WHERE a.ssid=b.ssid and a.the_geom != b.the_geom
>         GROUP BY a.ssid
> ),
> all_lines AS  (
>         SELECT * FROM inter_line
>
>         UNION ALL
>
>         SELECT * FROM diff_line
> )
>
> SELECT the_geom, count(*) FROM all_lines
> GROUP BY the_geom
>
>
>
>
> On 24 April 2014 11:37, Hugues François <hugues.francois at irstea.fr> wrote:
> > Hello,
> >
> > I think the use of st_intersection / st_difference may help you to
> achieve that you will have to take care of duplicates. I think the query
> could be something like the first draft below but could  be improved. The
> principle is to find intersections from geometry from a self join of your
> original table and use this output to get the difference with your original
> table. Maybe someone else will have a better idea !
> >
> > HTH
> >
> > Hug
> >
> > WITH inter AS (
> >         SELECT DISTINCT a.gid,
> ST_CollectionExtract(st_intersection(a.geom, b.geom),2) geom
> >         FROM yourtable a, yourtable b
> >         WHERE st_intersects(a.geom, b.geom)
> >         ),
> >
> > inter_line AS (
> >         SELECT gid, ST_UNION (geom) geom
> >         FROM line_inter
> >         GROUP BY gid),
> >
> > diff_line AS (
> >         SELECT a.gid,
> st_union(st_collectionextract(st_difference(a.geom, b.geom),2)) geom
> >         FROM yourtable a, inter_line b
> >         WHERE a.gid=b.gid and a.geom != b.geom
> >         GROUP BY a.gid
> > ),
> >
> > all_lines AS  (
> >         SELECT * FROM inter_line
> >
> >         UNION ALL
> >
> >         SELECT * FROM diff_line
> > )
> >
> > SELECT geom, count(*) FROM all_lines
> > GROUP BY geom
> >
> >
> >
> >
> > -----Message d'origine-----
> > De : postgis-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] De la part de James David Smith
> > Envoyé : jeudi 24 avril 2014 11:11
> > À : PostGIS Users Discussion
> > Objet : [postgis-users] Grouping by geom with count?
> >
> > Hi all,
> >
> > A bit of advice please. I have a table of about 250,000 linestring.
> > They represent peoples routes on roads around London. I would like to
> use them in QGIS now to show the most used roads by making them a darker
> colour. So to do this I feel I need to do some sort of grouping of the
> geometries with a count column too - so that I can use that count column to
> define the darkness of the line on my map.
> >
> > How could I go about doing this please?
> >
> > The problem I can see in my head is that let's say I have one linestring
> which goes from A to B. Then another linestring that goes from A to B to C.
> When I group the geometries, these won't group as they aren't the same.
> However I would want the result to be that linestring A to B is given a
> value of 2 and the bit of the line from B to C would be given a value of 1.
> >
> > I think I'm perhaps overcomplicating this...
> >
> > Thanks
> >
> > James
> > _______________________________________________
> > 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/20140424/fdadb117/attachment.html>