In PostGIS I have a large database of linestrings which I have obtained using ST_makeline function.
I would like to cluster similar lines based on where the lines start and end.
Here is an example of what I would like to obtain:
From this post Grouping lines together to form one line after a distance threshold is met I know that in ArcGIS Desktop there is a tool that identifies the mean direction, length, and geographic center for a set of lines.
Do you know if there is an equivalent tool in QGIS?
Do you think it would be easier to cluster start and end coordinates instead of lines?
Basing on this post How to merge connected lines with same direction (PostGis), I wrote the following query that seems to work although it is very slow:
select a.routeid as route1, b.routeid as route2,a.the_geom as geom1, b.the_geom as geom2 from prova a, prova b WHERE (ST_DWithin(st_startpoint(a.the_geom),st_startpoint(b.the_geom),0.1) and ST_DWithin(st_endpoint(a.the_geom),st_endpoint(b.the_geom),0.1) and (a.routeid <> b.routeid))
To prevent a line from being included in more than one separate cluster, I used distinct on as follows where line1 and line2 are the same tables as prova:
SELECT * FROM( SELECT DISTINCT ON (line2.routeid) line1.routeid as route1, line2.routeid as route2,line1.the_geom, line2.the_geom FROM line2 INNER JOIN line1 ON ST_DWithin(st_startpoint(line1.the_geom),st_startpoint(line2.the_geom),0.1) AND ST_DWithin(st_endpoint(line1.the_geom), st_endpoint(line2.the_geom), 0.1) ORDER BY line2.routeid) as clusters ORDER BY route1;