[postgis-users] Grouping by geom with count?

[Rémi Cura]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140425/0d644101/attachment.html>