get name/ds100 from db and lat/lon from dict1.28.1

result: significant performance improvement for stats generation

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