2022 / Day 11: Red
This is an attempt to rework one of the “maps” I did two years ago for #30DayMapChallenge 2020 day 8: yellow. I’m still not really satisfied with the result (so maybe next time, right?).
This query features freshly scraped data off the Estonian passanger rail service provider Elron.
The essence of the query is to take the departure station as the point of origin and the first trip that leaves the separture station, and plot out by what time will you reach the end of the line. Every trip node is shifted using st_project from the point of origin by their respective distance from each other along the track (and multiplied by the Tambov constant) in the direction which is determined by a fraction of time on a 12h clock:
(timestamp_at_node_in_seconds * 360.0) / (12.0 * 60.0 * 60.0)
meaning that if the train reaches a station at 12:00 it will be projected exactly north from the departure station. If at 6:00 or 18:00 it will be directly south.
with
stops as (
select
oid, dir, string_to_array(stops,',') as name, g.geom
from (
values
(
0,1,
'Narva,Vaivara,Oru,Jõhvi,Kohtla-Nõmme,Püssi,Kiviõli,Sonda,Kabala,Rakvere,Kadrina,Tapa',
'[[28.20017,59.36828,28560],[27.76716,59.37305,29520],[27.57513,59.37461,30000],[27.4045,59.35578,30480],[27.18902,59.36191,31140],[27.04856,59.35857,31500],[26.96568,59.35317,31800],[26.84234,59.34946,32160],[26.67519,59.34491,32580],[26.36551,59.35782,33360],[26.14501,59.3381,33840],[25.96101,59.26547,34500]]'
),(
1,3,
'Tapa,Lehtse,Jäneda,Nelijärve,Aegviidu,Kehra,Ülemiste,Kitseküla,Tallinn',
'[[25.96101,59.26547,34500],[25.824681,59.254372,34920],[25.70714,59.25493,35340],[25.6522,59.27081,35640],[25.61338,59.28509,35940],[25.334973,59.336832,36600],[24.795462,59.423329,37740],[24.739854,59.420099,37980],[24.7363,59.440081,38220]]'
),(
2,2,
'Tapa,Tamsalu,Jõgeva,Tartu',
'[[25.96101,59.26547,34500],[26.11175,59.15999,39060],[26.39954,58.74809,40680],[26.706446,58.373803,42540]]'
),(
3,2,
'Tapa,Tamsalu,Jõgeva,Tartu,Aardla,Ropka,Nõo,Tõravere,Peedu,Elva,Palupera,Puka,Mägiste,Keeni,Sangaste,Valga',
'[[25.96101,59.26547,34500],[26.11175,59.15999,39060],[26.39954,58.74809,40680],[26.706446,58.373803,42540],[26.71458,58.35763,53160],[26.62866,58.3148,53520],[26.53675,58.2815,53820],[26.48158,58.26019,54120],[26.44847,58.23714,54300],[26.42259,58.22522,54540],[26.32556,58.12691,55080],[26.23987,58.05496,55500],[26.23118,58.01054,55740],[26.23925,57.94698,56160],[26.189,57.864,56640],[26.05316,57.77458,57240]]'
),(
4,2,
'Tapa,Tamsalu,Jõgeva,Tartu,Kirsi,Ülenurme,Uhti,Reola,Vana-Kuuste,Rebase,Vastse-Kuuste,Valgemetsa,Kiidjärve,Taevaskoja,Põlva,Holvandi,Ruusa,Veriora,Ilumetsa,Orava,Koidula',
'[[25.96101,59.26547,34500],[26.11175,59.15999,39060],[26.39954,58.74809,40680],[26.706446,58.373803,42540],[26.71531,58.3574,63240],[26.72144,58.31003,63600],[26.72897,58.289,63720],[26.77116,58.27173,63960],[26.78405,58.25508,64080],[26.80909,58.23868,64260],[26.93197,58.17395,64800],[26.96719,58.14838,64980],[26.99398,58.12894,65160],[27.029051,58.100883,65400],[27.09123,58.07005,65760],[27.19501,58.05493,66060],[27.27305,58.04251,66300],[27.35356,58.00614,66600],[27.41069,57.96799,66840],[27.4734,57.91992,67200],[27.57538,57.83705,67800]]'
),(
5,3,
'Tapa,Lehtse,Jäneda,Nelijärve,Aegviidu,Kehra,Ülemiste,Kitseküla,Tallinn,Tallinn-Väike,Liiva,Saku,Kiisa,Kohila,Rapla,Keava,Lelle,Käru,Türi,Taikse,Kärevere,Ollepa,Võhma,Olustvere,Sürgavere,Viljandi',
'[[25.96101,59.26547,34500],[25.824681,59.254372,34920],[25.70714,59.25493,35340],[25.6522,59.27081,35640],[25.61338,59.28509,35940],[25.334973,59.336832,36600],[24.795462,59.423329,37740],[24.739854,59.420099,37980],[24.7363,59.440081,38220],[24.745989,59.412921,42540],[24.723226,59.387717,42900],[24.67676,59.30133,43380],[24.68545,59.23878,43800],[24.74357,59.17096,44220],[24.83229,58.99418,45240],[24.89839,58.94317,45540],[25.00661,58.86791,46140],[25.14391,58.83413,46560],[25.42219,58.80507,47280],[25.47252,58.77146,47520],[25.51805,58.72551,47820],[25.54101,58.66599,48120],[25.56181,58.62922,48360],[25.54655,58.54546,48780],[25.53709,58.48771,49140],[25.573276,58.357218,49740]]'
),(
6,3,
'Tapa,Lehtse,Jäneda,Nelijärve,Aegviidu,Kehra,Ülemiste,Kitseküla,Tallinn,Lilleküla,Tondi,Järve,Rahumäe,Nõmme,Hiiu,Kivimäe,Pääsküla,Laagri,Urda,Padula,Saue,Valingu,Keila,Kulna,Vasalemma,Kibuna,Laitse,Riisipere,Turba',
'[[25.96101,59.26547,34500],[25.824681,59.254372,34920],[25.70714,59.25493,35340],[25.6522,59.27081,35640],[25.61338,59.28509,35940],[25.334973,59.336832,36600],[24.795462,59.423329,37740],[24.739854,59.420099,37980],[24.7363,59.440081,38220],[24.728044,59.42497,41820],[24.733239,59.410541,42000],[24.724629,59.400628,42120],[24.702654,59.388611,42240],[24.68666,59.386123,42360],[24.670245,59.382976,42480],[24.6569,59.377195,42600],[24.641552,59.369583,43080],[24.627653,59.354416,43200],[24.612282,59.343541,43320],[24.592028,59.337674,43620],[24.547484,59.324739,43800],[24.483849,59.31146,44220],[24.417281,59.306108,44700],[24.353776,59.285969,44940],[24.298755,59.238138,45360],[24.293468,59.214563,45540],[24.314916,59.195155,45660],[24.312084,59.119444,46080],[24.240111,59.076028,46380]]'
),(
7,3,
'Tapa,Lehtse,Jäneda,Nelijärve,Aegviidu,Kehra,Ülemiste,Kitseküla,Tallinn,Lilleküla,Tondi,Järve,Rahumäe,Nõmme,Hiiu,Kivimäe,Pääsküla,Laagri,Urda,Saue,Valingu,Keila,Niitvälja,Klooga,Klooga-Aedlinn,Põllküla,Laoküla,Paldiski',
'[[25.96101,59.26547,34500],[25.824681,59.254372,34920],[25.70714,59.25493,35340],[25.6522,59.27081,35640],[25.61338,59.28509,35940],[25.334973,59.336832,36600],[24.795462,59.423329,37740],[24.739854,59.420099,37980],[24.7363,59.440081,38220],[24.728044,59.42497,38760],[24.733239,59.410541,38940],[24.724629,59.400628,39060],[24.702654,59.388611,39180],[24.68666,59.386123,39300],[24.670245,59.382976,39420],[24.6569,59.377195,39540],[24.641552,59.369583,40140],[24.627653,59.354416,40260],[24.612282,59.343541,40380],[24.547484,59.324739,40800],[24.483849,59.31146,41280],[24.417281,59.306108,41760],[24.3204,59.314038,42000],[24.258023,59.320517,42360],[24.21802,59.324494,42540],[24.174381,59.329039,42720],[24.133579,59.333272,42840],[24.060732,59.344227,43140]]'
),(
8,3,
'Tapa,Lehtse,Jäneda,Nelijärve,Aegviidu,Kehra,Ülemiste,Kitseküla,Tallinn,Lilleküla,Tondi,Järve,Rahumäe,Nõmme,Hiiu,Kivimäe,Pääsküla,Laagri,Urda,Saue,Valingu,Keila,Niitvälja,Klooga,Kloogaranna',
'[[25.96101,59.26547,34500],[25.824681,59.254372,34920],[25.70714,59.25493,35340],[25.6522,59.27081,35640],[25.61338,59.28509,35940],[25.334973,59.336832,36600],[24.795462,59.423329,37740],[24.739854,59.420099,37980],[24.7363,59.440081,38220],[24.728044,59.42497,56700],[24.733239,59.410541,56880],[24.724629,59.400628,57000],[24.702654,59.388611,57120],[24.68666,59.386123,57240],[24.670245,59.382976,57360],[24.6569,59.377195,57480],[24.641552,59.369583,57960],[24.627653,59.354416,58080],[24.612282,59.343541,58200],[24.547484,59.324739,58620],[24.483849,59.31146,59100],[24.417281,59.306108,59580],[24.3204,59.314038,59820],[24.258023,59.320517,60180],[24.245914,59.343637,60420]]'
)
) as x (oid, dir, stops, xyz)
join lateral (
select
array_agg(
st_pointm(
(v.elem->>0)::numeric,
(v.elem->>1)::numeric,
(v.elem->>2)::numeric,
4326
) order by v.i
) as geom
from
jsonb_array_elements(
xyz::jsonb
) with ordinality v(elem, i)
) g on true
),
tracks as (
select
oid, st_transform(st_makeline(stops.geom), 3301) track,
stops.name[1] as name, st_m(stops.geom[1]) as start_time
from stops
),
lines as (
select
oid,
st_chaikinsmoothing(
st_makeline(
array_agg(
st_project(
start_pnt::geography,
((total_length * fr) + first_track_length) *
case when fr = 0 then 1 else dir::numeric end *
(ts/100000) *
st_azimuth(
st_transform(start_pnt::geometry,3301),
geom
),
radians(((ts * 360.0) / (12.0 * 60.0 * 60.0)))
)::geometry order by s
)
),
2, false
) as geom,
max(ts) as endtime,
(array_agg(start_pnt))[1] as start_pnt
from (
select
oid, s, st_force2d(start_pnt) as start_pnt,
st_m(geom) as ts, dir,
geom,
st_length(track) total_length,
case when oid = 0 then 0 else first_track_length end,
st_linelocatepoint(track, geom) as fr
from (
select
stops.oid, dir, s,
first_track.st_length as first_track_length,
first_track.startpoint as start_pnt,
(st_dump(st_locatealong(track, s))).geom as geom,
track
from
stops,
tracks
join lateral (
select
st_transform(
st_startpoint(track),
4326
) as startpoint,
st_length(track)
from
tracks
where
oid = 0
) first_track on true
join lateral
generate_series(0, 60*60*24, 60) s on true
where
stops.oid = tracks.oid
) f
order by
oid, s
) g
group by
oid, first_track_length
)
select
row_number() over()::int as gid,
lines.oid, lines.geom, endpoint, stops.name[array_upper(stops.name,1)] as name,
lines.start_pnt, st.name as first_name,
to_char((st.start_time|| ' seconds')::interval, 'hh24:mi') as starttime,
to_char((lines.endtime|| ' seconds')::interval, 'hh24:mi') as endtime
from
lines,
stops
join lateral
st_endpoint(lines.geom) endpoint on true
join lateral (
select name, start_time
from tracks
where oid = 0
) st on true
where
lines.oid = stops.oid
;