<div dir="ltr"><div>It is a guess,<br>but it could solve a numerical-precision problem (<a href="http://en.wikipedia.org/wiki/Rounding_error">http://en.wikipedia.org/wiki/Rounding_error</a>).<br><br></div><div>Basically when computing with classical computer, you have a limited number of digits to express a number.<br>
</div><div>For instance the number 10/3 would be represented as <br></div><div>0.3333334 as a float, or 0.333333333334 as a double (conceptually).<br></div><div>So if you do operations on big numbers you might get unexpected result<br>
</div><div>(conceptually : the two points <br></div><div>(-530000,005 ,-178000,005) and (-530000,006 ,-178000,006) may be seen identical by some functions and different by others.).<br><br></div><div>So it may be worth to give a try to <a href="http://postgis.net/docs/ST_Translate.html">http://postgis.net/docs/ST_Translate.html</a><br>
</div><div>just use it before doing any actual computation (ie after spatial filtering like dwithin or like).<br></div><div>This way when computing your points will have less digits.<br></div><div>After computing just translate it back.<br>
</div><div><br></div><div>Cheers,<br>Rémi-C<br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-04-28 11:40 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 Remi,<br>
<br>
I don't understand what you mean? Why do I need to translate my data?<br>
<br>
Thanks<br>
<span class="HOEnZb"><font color="#888888"><br>
James<br>
</font></span><div class="HOEnZb"><div class="h5"><br>
On 28 April 2014 10:25, Rémi Cura <<a href="mailto:remi.cura@gmail.com">remi.cura@gmail.com</a>> wrote:<br>
> I'm obsessed with precision,<br>
> so I would say translate your data t = ( -530000 ,-178000)<br>
> Cheers,<br>
> Rémi-C<br>
><br>
><br>
> 2014-04-28 10:54 GMT+02:00 James David Smith <<a href="mailto:james.david.smith@gmail.com">james.david.smith@gmail.com</a>>:<br>
><br>
>> 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>
>> 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 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>
>> ),<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))<br>
>> the_geom<br>
>> FROM stage a, inter_line b<br>
>> WHERE a.ssid=b.ssid AND NOT st_equals(a.the_geom, b.the_geom)d<br>
>> 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>
>> GROUP BY the_geom)<br>
>><br>
>> Best wishes<br>
>><br>
>> James<br>
>><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>><br>
>> 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<br>
>> >> 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<br>
>> >> 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<br>
>> >>> QGIS,<br>
>> >>> you could perhaps just play around with the opacity settings for the<br>
>> >>> 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<br>
>> >>>> > query<br>
>> >>>> > could be something like the first draft below but could be<br>
>> >>>> > improved. The<br>
>> >>>> > principle is to find intersections from geometry from a self join<br>
>> >>>> > of your<br>
>> >>>> > original table and use this output to get the difference with your<br>
>> >>>> > 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))<br>
>> >>>> > 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<br>
>> >>>> > 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<br>
>> >>>> > to<br>
>> >>>> > use them in QGIS now to show the most used roads by making them a<br>
>> >>>> > darker<br>
>> >>>> > colour. So to do this I feel I need to do some sort of grouping of<br>
>> >>>> > the<br>
>> >>>> > geometries with a count column too - so that I can use that count<br>
>> >>>> > 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<br>
>> >>>> > goes from A<br>
>> >>>> > to B to C. When I group the geometries, these won't group as they<br>
>> >>>> > aren't the<br>
>> >>>> > same. However I would want the result to be that linestring A to B<br>
>> >>>> > is given<br>
>> >>>> > a value of 2 and the bit of the line from B to C would be given a<br>
>> >>>> > 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><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>