summaryrefslogtreecommitdiff
path: root/lib/Travelynx
diff options
context:
space:
mode:
authorDaniel Friesel <derf@finalrewind.org>2022-12-07 22:46:04 +0100
committerDaniel Friesel <derf@finalrewind.org>2022-12-07 22:46:04 +0100
commitafedcef2663ceda6d4a9b88af702c04eb4eb0f4a (patch)
treeecd536a4c8678efca7a448485d173861ba40ed49 /lib/Travelynx
parent73dd23e422397aeb4a0538e99b641c250f4e6250 (diff)
get name/ds100 from db and lat/lon from dict1.28.1
result: significant performance improvement for stats generation
Diffstat (limited to 'lib/Travelynx')
-rw-r--r--lib/Travelynx/Command/database.pm67
-rwxr-xr-xlib/Travelynx/Model/Journeys.pm39
2 files changed, 81 insertions, 25 deletions
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 );