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 <[email protected]>: > 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 <[email protected]> > wrote: > > Thanks guys, I'll give that a crack later. > > > > //JDS > > > > On 25 Apr 2014 08:33, "Rémi Cura" <[email protected]> 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 <[email protected]>: > >>> > >>> 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 > >>> <[email protected]> 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 <[email protected]> > >>>> 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 : [email protected] > >>>> > [mailto:[email protected]] 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 > >>>> > [email protected] > >>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >>>> > _______________________________________________ > >>>> > postgis-users mailing list > >>>> > [email protected] > >>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >>>> _______________________________________________ > >>>> postgis-users mailing list > >>>> [email protected] > >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >>> > >>> > >>> > >>> _______________________________________________ > >>> postgis-users mailing list > >>> [email protected] > >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> > >> > >> > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
