diff options
Diffstat (limited to 'lib')
| -rwxr-xr-x | lib/Travelynx.pm | 16 | ||||
| -rw-r--r-- | lib/Travelynx/Command/database.pm | 67 | ||||
| -rwxr-xr-x | lib/Travelynx/Model/Journeys.pm | 39 |
3 files changed, 89 insertions, 33 deletions
diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm index 8c98f15..8d661d2 100755 --- a/lib/Travelynx.pm +++ b/lib/Travelynx.pm @@ -323,12 +323,13 @@ sub startup { journeys => sub { my ($self) = @_; state $journeys = Travelynx::Model::Journeys->new( - log => $self->app->log, - pg => $self->pg, - in_transit => $self->in_transit, - stats_cache => $self->journey_stats_cache, - renamed_station => $self->app->renamed_station, - stations => $self->stations, + log => $self->app->log, + pg => $self->pg, + in_transit => $self->in_transit, + stats_cache => $self->journey_stats_cache, + renamed_station => $self->app->renamed_station, + latlon_by_station => $self->app->coordinates_by_station, + stations => $self->stations, ); } ); @@ -424,8 +425,7 @@ sub startup { my @unknown_stations; for my $station (@stations) { - my $station_info - = $self->stations->get_by_name( $station ); + my $station_info = $self->stations->get_by_name($station); if ( not $station_info ) { push( @unknown_stations, $station ); } diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm index d3a5006..256820b 100644 --- a/lib/Travelynx/Command/database.pm +++ b/lib/Travelynx/Command/database.pm @@ -1145,6 +1145,73 @@ my @migrations = ( } ); }, + +# v27 -> v28 +# add ds100, name, and lat/lon from stations table to journeys_str / in_transit_str + sub { + my ($db) = @_; + $db->query( + qq{ + drop view journeys_str; + drop view in_transit_str; + create view journeys_str as select + journeys.id as journey_id, user_id, + train_type, train_line, train_no, train_id, + extract(epoch from checkin_time) as checkin_ts, + extract(epoch from sched_departure) as sched_dep_ts, + extract(epoch from real_departure) as real_dep_ts, + checkin_station_id as dep_eva, + dep_station.ds100 as dep_ds100, + dep_station.name as dep_name, + dep_station.lat as dep_lat, + dep_station.lon as dep_lon, + extract(epoch from checkout_time) as checkout_ts, + extract(epoch from sched_arrival) as sched_arr_ts, + extract(epoch from real_arrival) as real_arr_ts, + checkout_station_id as arr_eva, + arr_station.ds100 as arr_ds100, + arr_station.name as arr_name, + arr_station.lat as arr_lat, + arr_station.lon as arr_lon, + polylines.polyline as polyline, + cancelled, edited, route, messages, user_data, + dep_platform, arr_platform + from journeys + left join polylines on polylines.id = polyline_id + left join stations as dep_station on checkin_station_id = dep_station.eva + left join stations as arr_station on checkout_station_id = arr_station.eva + ; + create view in_transit_str as select + user_id, + train_type, train_line, train_no, train_id, + extract(epoch from checkin_time) as checkin_ts, + extract(epoch from sched_departure) as sched_dep_ts, + extract(epoch from real_departure) as real_dep_ts, + checkin_station_id as dep_eva, + dep_station.ds100 as dep_ds100, + dep_station.name as dep_name, + dep_station.lat as dep_lat, + dep_station.lon as dep_lon, + extract(epoch from checkout_time) as checkout_ts, + extract(epoch from sched_arrival) as sched_arr_ts, + extract(epoch from real_arrival) as real_arr_ts, + checkout_station_id as arr_eva, + arr_station.ds100 as arr_ds100, + arr_station.name as arr_name, + arr_station.lat as arr_lat, + arr_station.lon as arr_lon, + polylines.polyline as polyline, + cancelled, route, messages, user_data, + dep_platform, arr_platform, data + from in_transit + left join polylines on polylines.id = polyline_id + left join stations as dep_station on checkin_station_id = dep_station.eva + left join stations as arr_station on checkout_station_id = arr_station.eva + ; + update schema_version set version = 28; + } + ); + }, ); sub sync_stations { diff --git a/lib/Travelynx/Model/Journeys.pm b/lib/Travelynx/Model/Journeys.pm index 3706916..d9e17f0 100755 --- a/lib/Travelynx/Model/Journeys.pm +++ b/lib/Travelynx/Model/Journeys.pm @@ -473,7 +473,7 @@ sub get { my @select = ( - qw(journey_id train_type train_line train_no checkin_ts sched_dep_ts real_dep_ts dep_eva checkout_ts sched_arr_ts real_arr_ts arr_eva cancelled edited route messages user_data) + qw(journey_id train_type train_line train_no checkin_ts sched_dep_ts real_dep_ts dep_eva dep_ds100 dep_name dep_lat dep_lon checkout_ts sched_arr_ts real_arr_ts arr_eva arr_ds100 arr_name arr_lat arr_lon cancelled edited route messages user_data) ); my %where = ( user_id => $uid, @@ -524,10 +524,16 @@ sub get { line => $entry->{train_line}, no => $entry->{train_no}, from_eva => $entry->{dep_eva}, + from_ds100 => $entry->{dep_ds100}, + from_name => $entry->{dep_name}, + from_latlon => [ $entry->{dep_lat}, $entry->{dep_lon} ], checkin_ts => $entry->{checkin_ts}, sched_dep_ts => $entry->{sched_dep_ts}, rt_dep_ts => $entry->{real_dep_ts}, to_eva => $entry->{arr_eva}, + to_ds100 => $entry->{arr_ds100}, + to_name => $entry->{arr_name}, + to_latlon => [ $entry->{arr_lat}, $entry->{arr_lon} ], checkout_ts => $entry->{checkout_ts}, sched_arr_ts => $entry->{sched_arr_ts}, rt_arr_ts => $entry->{real_arr_ts}, @@ -541,15 +547,6 @@ sub get { $ref->{polyline} = $entry->{polyline}; } - if ( my $station = $self->{stations}->get_by_eva( $ref->{from_eva} ) ) { - $ref->{from_ds100} = $station->{ds100}; - $ref->{from_name} = $station->{name}; - } - if ( my $station = $self->{stations}->get_by_eva( $ref->{to_eva} ) ) { - $ref->{to_ds100} = $station->{ds100}; - $ref->{to_name} = $station->{name}; - } - if ( $opt{with_datetime} ) { $ref->{checkin} = epoch_to_dt( $ref->{checkin_ts} ); $ref->{sched_departure} @@ -935,8 +932,10 @@ sub get_travel_distance { my $from = $journey->{from_name}; my $from_eva = $journey->{from_eva}; + my $from_latlon = $journey->{from_latlon}; my $to = $journey->{to_name}; my $to_eva = $journey->{to_eva}; + my $to_latlon = $journey->{to_latlon}; my $route_ref = $journey->{route}; my $polyline_ref = $journey->{polyline}; @@ -979,32 +978,22 @@ sub get_travel_distance { $prev_station = $station; } - $prev_station = $self->{stations}->get_by_name( shift @route ); + $prev_station = $self->{latlon_by_station}->{ shift @route }; if ( not $prev_station ) { return ( $distance_polyline, 0, 0 ); } - my $from_station_beeline; - my $to_station_beeline; - for my $station_name (@route) { - if ( my $station = $self->{stations}->get_by_name($station_name) ) { - $from_station_beeline //= $prev_station; - $to_station_beeline = $station; + if ( my $station = $self->{latlon_by_station}->{$station_name} ) { $distance_intermediate += $geo->distance_metal( - $prev_station->{lat}, $prev_station->{lon}, - $station->{lat}, $station->{lon} + $prev_station->[0], $prev_station->[1], + $station->[0], $station->[1] ); $prev_station = $station; } } - if ( $from_station_beeline and $to_station_beeline ) { - $distance_beeline = $geo->distance_metal( - $from_station_beeline->{lat}, $from_station_beeline->{lon}, - $to_station_beeline->{lat}, $to_station_beeline->{lon} - ); - } + $distance_beeline = $geo->distance_metal( @{$from_latlon}, @{$to_latlon} ); return ( $distance_polyline, $distance_intermediate, $distance_beeline, $skipped ); |