<div dir="ltr"><div>I'm obsessed with precision,<br>so I would say translate your data t = ( -530000 ,-178000)<br></div>Cheers,<br>Rémi-C<br></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-04-28 10:54 GMT+02:00 James David Smith <span dir="ltr"><<a href="mailto:james.david.smith@gmail.com" target="_blank">james.david.smith@gmail.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hey all,<br>
<br>
I decided to push ahead with trying to make a table for this<br>
mini-project, rather than rely on QGIS styles. I can explain why if<br>
anyone is interested. However when I run the query that Hugues hepled<br>
with, I get the error:<br>
<br>
ERROR: GEOSUnaryUnion: TopologyException: found non-noded<br>
intersection between LINESTRING (530395 178004, 530396 178004) and<br>
LINESTRING (530396 178004, 530396 178004) at 530395.54888857342<br>
178004.12613484744<br>
********** Error **********<br>
<br>
The query I am using is below. Any ideas?<br>
<br>
CREATE TABLE lambeth_unique AS (WITH inter AS (<br>
<div class="">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>
</div>WHERE a.spid IN (SELECT ppid FROM person WHERE phaboro::integer = 13<br>
AND bad_flag IS NULL)<br>
AND b.spid IN (SELECT ppid FROM person WHERE phaboro::integer = 13 AND<br>
bad_flag IS NULL)<br>
<div class="">),<br>
inter_line AS (<br>
SELECT ssid,<br>
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, b.the_geom),2)) the_geom<br>
FROM stage a, inter_line b<br>
</div>WHERE a.ssid=b.ssid AND NOT st_equals(a.the_geom, b.the_geom)d<br>
<div class="">GROUP BY a.ssid<br>
),<br>
all_lines AS (<br>
SELECT *<br>
FROM inter_line<br>
UNION ALL<br>
SELECT * FROM diff_line<br>
)<br>
SELECT the_geom,<br>
count(*)<br>
FROM all_lines<br>
</div>GROUP BY the_geom)<br>
<br>
Best wishes<br>
<span class="HOEnZb"><font color="#888888"><br>
James<br>
</font></span><div class="HOEnZb"><div class="h5"><br>
<br>
<br>
On 25 April 2014 08:56, James David Smith <<a href="mailto:james.david.smith@gmail.com">james.david.smith@gmail.com</a>> wrote:<br>
> Thanks guys, I'll give that a crack later.<br>
><br>
> //JDS<br>
><br>
> On 25 Apr 2014 08:33, "Rémi Cura" <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:<br>
>><br>
>> Hey,<br>
>> If you just want visualisation it seem sa waste to do a lot of computing,<br>
>> (i.e if you will do no quantitative use )<br>
>> simply load the linestring in QGIS (version >=2),<br>
>> set the right width for the line in style and use the transparency options<br>
>> selecting the "darkening" rule for object vs object transparency.<br>
>><br>
>> Cheers,<br>
>> Rémi-C<br>
>><br>
>><br>
>> 2014-04-24 23:50 GMT+02:00 Åsmund Tokheim <<a href="mailto:asmundto@gmail.com">asmundto@gmail.com</a>>:<br>
>>><br>
>>> Hi<br>
>>><br>
>>> Try changing "a.the_geom != b.the_geom" to "not st_equals(a.the_geom,<br>
>>> b.the_geom)". By the way, if all that you want is darker colours in QGIS,<br>
>>> you could perhaps just play around with the opacity settings for the lines.<br>
>>><br>
>>> Åsmund<br>
>>><br>
>>><br>
>>> On Thu, Apr 24, 2014 at 5:22 PM, James David Smith<br>
>>> <<a href="mailto:james.david.smith@gmail.com">james.david.smith@gmail.com</a>> wrote:<br>
>>>><br>
>>>> 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>
>>>> ),<br>
>>>> all_lines AS (<br>
>>>> SELECT * FROM inter_line<br>
>>>><br>
>>>> UNION ALL<br>
>>>><br>
>>>> SELECT * FROM diff_line<br>
>>>> )<br>
>>>><br>
>>>> SELECT the_geom, count(*) FROM all_lines<br>
>>>> GROUP BY the_geom<br>
>>>><br>
>>>><br>
>>>><br>
>>>><br>
>>>> On 24 April 2014 11:37, Hugues François <<a href="mailto:hugues.francois@irstea.fr">hugues.francois@irstea.fr</a>><br>
>>>> wrote:<br>
>>>> > Hello,<br>
>>>> ><br>
>>>> > I think the use of st_intersection / st_difference may help you to<br>
>>>> > achieve that you will have to take care of duplicates. I think the query<br>
>>>> > could be something like the first draft below but could be improved. The<br>
>>>> > principle is to find intersections from geometry from a self join of your<br>
>>>> > original table and use this output to get the difference with your original<br>
>>>> > 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,<br>
>>>> > 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,<br>
>>>> > 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">postgis-users-bounces@lists.osgeo.org</a><br>
>>>> > [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a>] De la part de James David<br>
>>>> > 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<br>
>>>> > use them in QGIS now to show the most used roads by making them a darker<br>
>>>> > colour. So to do this I feel I need to do some sort of grouping of the<br>
>>>> > geometries with a count column too - so that I can use that count column to<br>
>>>> > 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<br>
>>>> > linestring which goes from A to B. Then another linestring that goes from A<br>
>>>> > to B to C. When I group the geometries, these won't group as they aren't the<br>
>>>> > same. However I would want the result to be that linestring A to B is given<br>
>>>> > a value of 2 and the bit of the line from B to C would be given a value of<br>
>>>> > 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">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">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">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>
>>><br>
>>><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>
>><br>
>><br>
>><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>
_______________________________________________<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></div></div></blockquote></div><br></div>