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.


enter image description here


enter image description here

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,
    FROM    network
    WHERE   n_type != 'none'


    SELECT  a.i + 1,
    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.

  • 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

Your solution is, at least, missing an pre-ordering of line components as John Barça said.

Recursive queries are very, very hard to grok, I would say.

You must try to replicate ST_Linemerge behaviour in a new database function. I would try first to look at the source of an ST_Linemerge implementation and replicate, altering it to produce the 30 degree angle twitch.

To discard from the aggregation the segments that are not at an angle of < 30 deg., you must compare them INSIDE an aggregation loop.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.