No problem. I'm not quite sure how to work that into the query though please?
On 24 April 2014 16:27, Hugues François <[email protected]> wrote: > Excuse me for the mistake. Let's try st_equals. > > Hug > > James David Smith <[email protected]> a écrit : > > 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
