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