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 <[email protected]>: > 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 < > [email protected]> 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 <[email protected]> >> 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 : [email protected] [mailto: >> [email protected]] 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 >> > [email protected] >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > _______________________________________________ >> > postgis-users mailing list >> > [email protected] >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
