# PostGIS Recursive Query Based on Line Connectivity

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.

Before:

After:

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
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.

• Without digging to deep into this, a couple of observations. You will need to union a.geom and b.geom in your select clause. You should probably make sure that you don't try and join a line to itself as well with `a.pk_uid != b.pk_uid` – MickyT Feb 17 '15 at 19:55
• @MickyT thanks. I should have mentioned that I can do the union in the last SELECT clause but for the time being have just selected * so that I can see all results. I'll modify my code snippet to show what it should eventually look like. – spencerrecneps Feb 17 '15 at 20:05
• Recursive queries are hard to grok. Lol and +1 – John Powell Feb 17 '15 at 22:30
• A quick look suggests you might have issues with your base/anchor query. You need to be selecting the start point for each line, A, B, C etc, which you then build up in the recursive part. Possibly you need to add some kind of ordering to the anchor query (perhaps in x or y, direction -- hard to know without seeing the data). I would break this down and make sure that I was getting sensible start points first, before continuing with the recursive part. I used a recursive query to find streaks in time series recently, and once I had the anchor points correctly identified, the rest was easy(ish) – John Powell Feb 18 '15 at 8:31
• @dbaston. Good point, but you can do this with a recursive query also, and not everyone has that installed yet. – John Powell Feb 19 '15 at 6:05