1

I have a linestring that represents a bus route, and includes some overlapping sections where the bus has traveled back on the same street.

When I ST_UnaryUnion the line, or ST_Intersection it with any other geography (such as a census block), it removes the duplicate sections. All well and good - understood. The ST_Length of the result is less than the ST_Length of the original line.

However, I am looking for a way to get the intersection with another geography where the overlapping sections are not dissolved, and are each counted. Is my only option to turn the linestring into individual edges, and intersect each individually with the other geometry?

enter image description here

LINESTRING(-123.103132 44.63031,-123.102594 44.630717,-123.102482 44.630698,-123.102397 44.630761,-123.102427 44.630832,-123.102518 44.630862,-123.102614 44.63082,-123.102902 44.630978,-123.103166 44.631223,-123.103564 44.631523,-123.103662 44.631645,-123.104108 44.633501,-123.104975 44.637434,-123.110061 44.636849,-123.109743 44.635465,-123.10971 44.635482,-123.108963 44.632141,-123.115058 44.631458,-123.114875 44.630676,-123.114715 44.629905,-123.114517 44.629131,-123.114105 44.62722,-123.114059 44.626865,-123.114013 44.624446,-123.113967 44.623104,-123.113975 44.622467,-123.11396 44.621803,-123.113929 44.621162,-123.113906 44.618679,-123.113571 44.618637,-123.11322 44.618556,-123.113571 44.617439,-123.113716 44.61684,-123.113815 44.616001,-123.1138 44.614761,-123.113792 44.614349,-123.113716 44.611679,-123.113716 44.610912,-123.113685 44.61,-123.108367 44.610076,-123.10366 44.610126,-123.101005 44.610172,-123.098159 44.610202,-123.095336 44.610233,-123.095352 44.610515,-123.095481 44.610702,-123.095634 44.610843,-123.09613 44.611019,-123.096305 44.611167,-123.096359 44.611335,-123.096343 44.611518,-123.096206 44.61185,-123.096199 44.61198,-123.096313 44.613002,-123.096336 44.613368,-123.09636177644 44.61474890937,-123.09841327976 44.614723906964,-123.098365 44.613353,-123.09835 44.612392,-123.098281 44.610656,-123.09819 44.610454,-123.098159 44.610202,-123.101005 44.610172,-123.10366 44.610126,-123.113685 44.61,-123.113655 44.608993,-123.113632 44.607772,-123.113639 44.607009,-123.113464 44.603984,-123.113395 44.601371,-123.113327 44.598335,-123.113327 44.597782,-123.113296 44.596847,-123.113159 44.595897,-123.113151 44.595851,-123.112594 44.595111,-123.110588 44.592952,-123.110359 44.592601,-123.11013 44.592159,-123.11 44.591735,-123.109886 44.59122,-123.109848 44.590244,-123.109832 44.589241,-123.10984828438 44.586539077766,-123.10989001321 44.585650078693,-123.10989826718 44.584618521416,-123.11068147221 44.58463380388,-123.11235957141 44.584644454367,-123.11234579099 44.586051060119,-123.11393028571 44.586102043137,-123.11391955687 44.586118252224,-123.11512455952 44.586124123837,-123.11512761111 44.584679495402,-123.11128228703 44.584626162648,-123.10990074205 44.584587956477,-123.10985901321 44.585316508053,-123.10980536903 44.587540037575,-123.10984263558 44.590287221141,-123.109848 44.59027,-123.109886 44.59122,-123.11 44.591735,-123.11013 44.592159,-123.110359 44.592601,-123.110588 44.592952,-123.112594 44.595111,-123.113151 44.595851,-123.113296 44.596847,-123.113327 44.597782,-123.113327 44.598335,-123.113464 44.603984,-123.113639 44.607009,-123.113632 44.607772,-123.113655 44.608993,-123.113716 44.610912,-123.113716 44.611679,-123.1138 44.614761,-123.113815 44.616001,-123.113716 44.61684,-123.113571 44.617439,-123.11322 44.618556,-123.112976 44.61911,-123.112121 44.620475,-123.110687 44.622318,-123.109909 44.62318,-123.109359 44.623878,-123.107604 44.626026,-123.105636 44.628383,-123.105361 44.628299,-123.105361 44.6282,-123.105255 44.628166,-123.105155 44.628196,-123.104904 44.628479,-123.104675 44.628776,-123.104507 44.629131,-123.104347 44.629333,-123.103668 44.629951,-123.103126 44.630313)

  • [put comment in the wrong place] – woolie Mar 20 '18 at 18:57
0

Jep, that's your best option. You can run

WITH
  line_segments AS (
    SELECT a.<id>,
           n - 1 AS seg_id,
           ST_MakeLine(ST_PointN(a.geom, n - 1), ST_PointN(a.geom, n)) AS geom
    FROM <line_table> AS a
    CROSS JOIN generate_series(2, ST_NPoints(a.geom)) AS n
  )

SELECT ST_Length(ST_Collect(ST_Intersection(a.geom, b.geom))::geography) AS length
FROM line_segments AS a
JOIN <census_table> AS b
  ON ST_Intersects(a.geom, b.geom)
GROUP BY b.<id>, a.<id>;

which will return the aggregated length in meter (via cast to geography type) for each a.<id> in <line_table> AS a over each b.<id> in <census_table> AS b.

In fact, self-intersection is usually treated as a topology error, so it´s best to avoid it. It might be worth creating a new table with the line segments (and new indexes). I added a seg_id to line_segments that increments per segment from the start of the original LINESTRING; you can use that to keep the linear reference:

CREATE TABLE <segments> AS
  SELECT ROW_NUMBER() OVER() AS uid,
         a.<id> AS line_id,
         n - 1 AS seg_id,
         ST_SetSRID(ST_MakeLine(ST_PointN(a.geom, n - 1), ST_PointN(a.geom, n)), 4326) AS geom
  FROM <line_table> AS a
  CROSS JOIN generate_series(2, ST_NPoints(a.geom)) AS n;

CREATE INDEX <segments>_geom_idx ON <segments> USING gist (geom);
VACUUM ANALYZE <segments>;
  • Thank you so much - your example worked perfectly. I was afraid this type of manipulation would be necessary but it looks like it can be added without too much fuss. – woolie Mar 20 '18 at 18:58
  • @woolie glad it helped! but, about the fuss: the line segmentation actually creates significant overhead, plus the outer query cannot use an index on the line segments. so, if you plan on adding a lot more bus routes to the project I strongly recommend to set up a new table and index for all segmentized routes. and then...the self-intersections...I tell you, that is considered topology terrorism by some GIS software...,) – ThingumaBob Mar 20 '18 at 19:52
  • In this particular case, it's in a report-type query, so not a live endpoint where latency is critical. I agree creating a new table would be best, but in this instance I want to minimize database changes. – woolie Mar 20 '18 at 23:14
  • (the issue turned out to surface in many other queries so I ended up creating the segments table :) – woolie Mar 21 '18 at 19:38

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.