ST_Intersection preserving overlapping edges

Question

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?

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)

Answer 1

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