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

[James David Smith]

No problem. I'm not quite sure how to work that into the query though please?

On 24 April 2014 16:27, Hugues François <hugues.francois at irstea.fr> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users