[postgis-users] Rép: Re: Grouping by geom with count?

[Hugues François]

Excuse me for the mistake. Let's try st_equals. 

Hug

James David Smith <james.david.smith at gmail.com> a écrit :

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