[postgis-users] Grouping by geom with count?

[James David Smith]

Thanks guys, I'll give that a crack later.

//JDS
On 25 Apr 2014 08:33, "Rémi Cura" <remi.cura at gmail.com> wrote:

> Hey,
> If you just want visualisation it seem sa waste to do a lot of computing,
> (i.e if you will do no quantitative use )
> simply load the linestring in QGIS (version >=2),
> set the right width for the line in style and *use the transparency
> options selecting the "darkening" rule for object vs object transparency*.
>
> Cheers,
> Rémi-C
>
>
> 2014-04-24 23:50 GMT+02:00 Åsmund Tokheim <asmundto at gmail.com>:
>
>> 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
>>>
>>
>>
>> _______________________________________________
>> 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/20140425/77bdbd24/attachment.html>