I'm having difficulty with a query. I have a network of linestrings, each with a value in the n_type column. This can be one of a handful of options. I'd like to generate a new table that groups any linestrings that are of the same type and that form a continuous line.
Here is what I've got so far. It returns results but they don't make any sense - the types don't match up and it returns too many features.
Please also note that I've defined "continuous" as any line within 5 feet of its neighbor and meeting at less than a 30 degree angle.
WITH RECURSIVE all_links (i, pk_uid, n_type, geom) AS ( SELECT 1 AS i, pk_uid, n_type, geom FROM network WHERE n_type != 'none' UNION ALL SELECT a.i + 1, b.pk_uid, b.n_type, b.geom FROM network b, all_links a WHERE b.n_type = a.n_type AND b.geom <#> a.geom <= 5 --lines are continuous if within 5 feet of neighbor AND ABS( DEGREES( 3*pi() - st_azimuth(st_startpoint(a.geom),st_endpoint(a.geom)) + st_azimuth(st_startpoint(b.geom),st_endpoint(b.geom)))::int % 360 - 180) <= 30 ) --only take links within 30 degrees of the same angle SELECT i, n_type, ST_Union(the_geom) FROM all_links GROUP BY i, n_type
I have assumed a recursive query is the way to go, but I'm happy to be proven wrong on that. Recursives are a little hard to grok.
Edit: I should also add that I've already tried aggregating using ST_Union and ST_Linemerge, and then dumping the result. This kinda works, but doesn't account for >30 degree intersections and also can't honor the five foot tolerance for connectivity.