Thanks guys, I'll give that a crack later. //JDS On 25 Apr 2014 08:33, "Rémi Cura" <[email protected]> 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 <[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 >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
