Representing a route that doubles back on itself in BigQuery with a LineString

Question

I'm trying to represent routes in BigQuery, but running into issues for routes that double back on themselves. Consider the following LineString that proceeds from the southwest to the northeast of the following image, and then loops back on itself before continuing further north (the red arrows were added by me manually):

When we attempt to represent this route in BigQuery using a few different methods, the result is always the same:

when constructing the points individually:

select st_makeline([
    st_geogpoint(0, 0),
    st_geogpoint(0, 1),
    st_geogpoint(1, 1),
    st_geogpoint(2, 2),
    st_geogpoint(1, 1),
    st_geogpoint(0, 1),
    st_geogpoint(0, 2)
])

when constructing the segments individually:

select st_makeline([
    st_makeline(st_geogpoint(0, 0), st_geogpoint(0, 1)),
    st_makeline(st_geogpoint(0, 1), st_geogpoint(1, 1)),
    st_makeline(st_geogpoint(1, 1), st_geogpoint(2, 2)),
    st_makeline(st_geogpoint(2, 2), st_geogpoint(1, 1)),
    st_makeline(st_geogpoint(1, 1), st_geogpoint(0, 1)),
    st_makeline(st_geogpoint(0, 1), st_geogpoint(0, 2))
])

even when decoding a WKT string:

select st_geogfromtext('linestring(0 0,0 1,1 1,2 2,1 1,0 1,0 2)')

Each time, the result is the same MultLineString:

MULTILINESTRING((0 0, 0 1, 1 1, 2 2), (0 1, 0 2))

Which is the following route:

So BigQuery is not inserting the doubled-over portion into separate line strings; it's just entirely skipping segments that it feels it has already covered. Fine for representing a shape, but not a path. I assume this is something related to BQ attempting to construct an OGC-compliant LineString, or perhaps just trying to minimize the amount of data it needs to store. Is there any way around this?

Answer 1

There's an answer to a similar question on Stack Overflow. In summary:

[I]f two edges exactly overlap - the overlapping section is discarded. ... There is currently no way to avoid this behavior - BigQuery Geography represents a point set, and as sets go, when you union two identical sets you get just one copy of this set.