[postgis-users] Grouping by geom with count?

[Hugues François]

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-bounces at lists.osgeo.org [mailto:postgis-users-bounces at 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 at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users