<div dir="ltr"><div><div><div><div><div>Hey,<br></div>If you just want visualisation it seem sa waste to do a lot of computing,<br></div>(i.e if you will do no quantitative use )<br></div>simply load the linestring in QGIS (version >=2),<br>
</div>set the right width for the line in style and <b>use the transparency options selecting the "darkening" rule for object vs object transparency</b>.<br><br></div>Cheers,<br>Rémi-C<br><div class="gmail_extra">
<br><br><div class="gmail_quote">2014-04-24 23:50 GMT+02:00 Åsmund Tokheim <span dir="ltr"><<a href="mailto:asmundto@gmail.com" target="_blank">asmundto@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Hi<div><br></div><div>Try changing<span style="font-family:arial,sans-serif;font-size:13px"> "</span><span style="font-family:arial,sans-serif;font-size:13px">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.</span></div>
<span class="HOEnZb"><font color="#888888">
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">Åsmund</span></div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra">
<br><br><div class="gmail_quote">
On Thu, Apr 24, 2014 at 5:22 PM, James David Smith <span dir="ltr"><<a href="mailto:james.david.smith@gmail.com" target="_blank">james.david.smith@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Thanks for the reply Hugues. Much appreciated. I've replaced the<br>
various bit of the query with my own table names etc, but get an<br>
error. Any thoughts/ideas guys?<br>
<br>
ERROR: operator is not unique: geometry <> geometry<br>
LINE 15: WHERE a.ssid=b.ssid and a.the_geom != b.the_geom<br>
<br>
WITH inter AS (<br>
SELECT DISTINCT a.ssid,<br>
ST_CollectionExtract(st_intersection(a.the_geom, b.the_geom),2)<br>
the_geom<br>
FROM stage a, stage b<br>
WHERE st_intersects(a.the_geom, b.the_geom)<br>
),<br>
inter_line AS (<br>
SELECT ssid, ST_UNION (the_geom) the_geom<br>
FROM inter<br>
GROUP BY ssid),<br>
diff_line AS (<br>
SELECT a.ssid,<br>
st_union(st_collectionextract(st_difference(a.the_geom,<br>
b.the_geom),2)) the_geom<br>
FROM stage a, inter_line b<br>
WHERE a.ssid=b.ssid and a.the_geom != b.the_geom<br>
GROUP BY a.ssid<br>
<div>),<br>
all_lines AS (<br>
SELECT * FROM inter_line<br>
<br>
UNION ALL<br>
<br>
SELECT * FROM diff_line<br>
)<br>
<br>
</div>SELECT the_geom, count(*) FROM all_lines<br>
GROUP BY the_geom<br>
<div><div><br>
<br>
<br>
<br>
On 24 April 2014 11:37, Hugues François <<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>> wrote:<br>
> Hello,<br>
><br>
> 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 !<br>
><br>
> HTH<br>
><br>
> Hug<br>
><br>
> WITH inter AS (<br>
> SELECT DISTINCT a.gid, ST_CollectionExtract(st_intersection(a.geom, b.geom),2) geom<br>
> FROM yourtable a, yourtable b<br>
> WHERE st_intersects(a.geom, b.geom)<br>
> ),<br>
><br>
> inter_line AS (<br>
> SELECT gid, ST_UNION (geom) geom<br>
> FROM line_inter<br>
> GROUP BY gid),<br>
><br>
> diff_line AS (<br>
> SELECT a.gid, st_union(st_collectionextract(st_difference(a.geom, b.geom),2)) geom<br>
> FROM yourtable a, inter_line b<br>
> WHERE a.gid=b.gid and a.geom != b.geom<br>
> GROUP BY a.gid<br>
> ),<br>
><br>
> all_lines AS (<br>
> SELECT * FROM inter_line<br>
><br>
> UNION ALL<br>
><br>
> SELECT * FROM diff_line<br>
> )<br>
><br>
> SELECT geom, count(*) FROM all_lines<br>
> GROUP BY geom<br>
><br>
><br>
><br>
><br>
> -----Message d'origine-----<br>
> De : <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] De la part de James David Smith<br>
> Envoyé : jeudi 24 avril 2014 11:11<br>
> À : PostGIS Users Discussion<br>
> Objet : [postgis-users] Grouping by geom with count?<br>
><br>
> Hi all,<br>
><br>
> A bit of advice please. I have a table of about 250,000 linestring.<br>
> 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.<br>
><br>
> How could I go about doing this please?<br>
><br>
> 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.<br>
><br>
> I think I'm perhaps overcomplicating this...<br>
><br>
> Thanks<br>
><br>
> James<br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></div></div></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div></div>