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