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
