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
