Hi Remi, Ok. I kind of understand what you're saying. But isn't translating all 250,000 of my linestrings just going to recreate the problem, but at a different coordinate?
The snaptogrid thing sounds like it might be the better way to go. James On 28 April 2014 11:12, Rémi Cura <remi.c...@gmail.com> wrote: > It is a guess, > but it could solve a numerical-precision problem > (http://en.wikipedia.org/wiki/Rounding_error). > > Basically when computing with classical computer, you have a limited number > of digits to express a number. > For instance the number 10/3 would be represented as > 0.3333334 as a float, or 0.333333333334 as a double (conceptually). > So if you do operations on big numbers you might get unexpected result > (conceptually : the two points > (-530000,005 ,-178000,005) and (-530000,006 ,-178000,006) may be seen > identical by some functions and different by others.). > > So it may be worth to give a try to > http://postgis.net/docs/ST_Translate.html > just use it before doing any actual computation (ie after spatial filtering > like dwithin or like). > This way when computing your points will have less digits. > After computing just translate it back. > > Cheers, > Rémi-C > > > 2014-04-28 11:40 GMT+02:00 James David Smith <james.david.sm...@gmail.com>: > >> Hey Remi, >> >> I don't understand what you mean? Why do I need to translate my data? >> >> Thanks >> >> James >> >> On 28 April 2014 10:25, Rémi Cura <remi.c...@gmail.com> wrote: >> > I'm obsessed with precision, >> > so I would say translate your data t = ( -530000 ,-178000) >> > Cheers, >> > Rémi-C >> > >> > >> > 2014-04-28 10:54 GMT+02:00 James David Smith >> > <james.david.sm...@gmail.com>: >> > >> >> Hey all, >> >> >> >> I decided to push ahead with trying to make a table for this >> >> mini-project, rather than rely on QGIS styles. I can explain why if >> >> anyone is interested. However when I run the query that Hugues hepled >> >> with, I get the error: >> >> >> >> ERROR: GEOSUnaryUnion: TopologyException: found non-noded >> >> intersection between LINESTRING (530395 178004, 530396 178004) and >> >> LINESTRING (530396 178004, 530396 178004) at 530395.54888857342 >> >> 178004.12613484744 >> >> ********** Error ********** >> >> >> >> The query I am using is below. Any ideas? >> >> >> >> CREATE TABLE lambeth_unique AS (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 a.spid IN (SELECT ppid FROM person WHERE phaboro::integer = 13 >> >> AND bad_flag IS NULL) >> >> AND b.spid IN (SELECT ppid FROM person WHERE phaboro::integer = 13 AND >> >> bad_flag IS NULL) >> >> ), >> >> 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 NOT st_equals(a.the_geom, b.the_geom)d >> >> 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) >> >> >> >> Best wishes >> >> >> >> James >> >> >> >> >> >> >> >> On 25 April 2014 08:56, James David Smith <james.david.sm...@gmail.com> >> >> wrote: >> >> > Thanks guys, I'll give that a crack later. >> >> > >> >> > //JDS >> >> > >> >> > On 25 Apr 2014 08:33, "Rémi Cura" <remi.c...@gmail.com> 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 <asmun...@gmail.com>: >> >> >>> >> >> >>> 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 >> >> >>> <james.david.sm...@gmail.com> 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 >> >> >>>> <hugues.franc...@irstea.fr> >> >> >>>> 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 : postgis-users-boun...@lists.osgeo.org >> >> >>>> > [mailto:postgis-users-boun...@lists.osgeo.org] 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 >> >> >>>> > postgis-users@lists.osgeo.org >> >> >>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >>>> > _______________________________________________ >> >> >>>> > postgis-users mailing list >> >> >>>> > postgis-users@lists.osgeo.org >> >> >>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >>>> _______________________________________________ >> >> >>>> postgis-users mailing list >> >> >>>> postgis-users@lists.osgeo.org >> >> >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >>> >> >> >>> >> >> >>> >> >> >>> _______________________________________________ >> >> >>> postgis-users mailing list >> >> >>> postgis-users@lists.osgeo.org >> >> >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >> >> >> >> >> >> >> >> >> >> _______________________________________________ >> >> >> postgis-users mailing list >> >> >> postgis-users@lists.osgeo.org >> >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> _______________________________________________ >> >> postgis-users mailing list >> >> postgis-users@lists.osgeo.org >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > >> > >> > >> > _______________________________________________ >> > postgis-users mailing list >> > postgis-users@lists.osgeo.org >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> _______________________________________________ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users