Add lat/lon to pre-2.7.8 journeys; add eva where missing

This speeds up the map significantly and makes coordinates_by_station obsolete

3 files changed, 153 insertions, 12 deletions

diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index fb5ee80..ae17f64 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -1948,7 +1948,7 @@ my @migrations = (
 	},
 
 	# v51 -> v52
-	# Explicitly encode backend type; preparation for multiple hAFAS backends
+	# Explicitly encode backend type; preparation for multiple HAFAS backends
 	sub {
 		my ($db) = @_;
 		$db->query(
@@ -2050,6 +2050,9 @@ my @migrations = (
 			}
 		);
 	},
+
+	# v52 -> v53
+	# Extend train_id to be compatible with more recent HAFAS versions
 	sub {
 		my ($db) = @_;
 		$db->query(
@@ -2166,6 +2169,147 @@ my @migrations = (
 			}
 		);
 	},
+
+	# v53 -> v54
+	# Retrofit lat/lon data onto routes logged before v2.7.8; ensure
+	# consistent name and eva entries as well.
+	sub {
+		my ($db) = @_;
+
+		say
+'Adding lat/lon to routes of journeys logged before v2.7.8 and improving consistency of name/eva data in very old route entries.';
+		say 'This may take a while ...';
+
+		my %legacy_to_new;
+		if ( -r 'share/old_station_names.json' ) {
+			%legacy_to_new = %{ JSON->new->utf8->decode(
+					scalar read_file('share/old_station_names.json')
+				)
+			};
+		}
+
+		my %latlon_by_eva;
+		my %latlon_by_name;
+		my $res = $db->select( 'stations', [ 'name', 'eva', 'lat', 'lon' ] );
+		while ( my $row = $res->hash ) {
+			$latlon_by_eva{ $row->{eva} }   = $row;
+			$latlon_by_name{ $row->{name} } = $row;
+		}
+
+		my $total
+		  = $db->select( 'journeys', 'count(*) as count' )->hash->{count};
+		my $count           = 0;
+		my $total_no_eva    = 0;
+		my $total_no_latlon = 0;
+
+		my $json = JSON->new;
+
+		$res = $db->select( 'journeys_str', [ 'route', 'journey_id' ] );
+		while ( my $row = $res->expand->hash ) {
+			my $no_eva    = 0;
+			my $no_latlon = 0;
+			my $changed   = 0;
+			my @route     = @{ $row->{route} };
+			for my $stop (@route) {
+				my $name = $stop->[0];
+				my $eva  = $stop->[1];
+
+				if ( not $eva and $stop->[2]{eva} ) {
+					$eva = $stop->[1] = 0 + $stop->[2]{eva};
+				}
+
+				if ( $stop->[2]{eva} and $eva and $eva == $stop->[2]{eva} ) {
+					delete $stop->[2]{eva};
+				}
+
+				if ( $stop->[2]{name} and $name eq $stop->[2]{name} ) {
+					delete $stop->[2]{name};
+				}
+
+				if ( not $eva ) {
+					if ( $latlon_by_name{$name} ) {
+						$eva     = $stop->[1] = $latlon_by_name{$name}{eva};
+						$changed = 1;
+					}
+					elsif ( $legacy_to_new{$name}
+						and $latlon_by_name{ $legacy_to_new{$name} } )
+					{
+						$eva = $stop->[1]
+						  = $latlon_by_name{ $legacy_to_new{$name} }{eva};
+						$stop->[2]{lat}
+						  = $latlon_by_name{ $legacy_to_new{$name} }{lat};
+						$stop->[2]{lon}
+						  = $latlon_by_name{ $legacy_to_new{$name} }{lon};
+						$changed = 1;
+					}
+					else {
+						$no_eva = 1;
+					}
+				}
+
+				if ( $stop->[2]{lat} and $stop->[2]{lon} ) {
+					next;
+				}
+
+				if ( $eva and $latlon_by_eva{$eva} ) {
+					$stop->[2]{lat} = $latlon_by_eva{$eva}{lat};
+					$stop->[2]{lon} = $latlon_by_eva{$eva}{lon};
+					$changed        = 1;
+				}
+				elsif ( $latlon_by_name{$name} ) {
+					$stop->[2]{lat} = $latlon_by_name{$name}{lat};
+					$stop->[2]{lon} = $latlon_by_name{$name}{lon};
+					$changed        = 1;
+				}
+				elsif ( $legacy_to_new{$name}
+					and $latlon_by_name{ $legacy_to_new{$name} } )
+				{
+					$stop->[2]{lat}
+					  = $latlon_by_name{ $legacy_to_new{$name} }{lat};
+					$stop->[2]{lon}
+					  = $latlon_by_name{ $legacy_to_new{$name} }{lon};
+					$changed = 1;
+				}
+				else {
+					$no_latlon = 1;
+				}
+			}
+			if ($no_eva) {
+				$total_no_eva += 1;
+			}
+			if ($no_latlon) {
+				$total_no_latlon += 1;
+			}
+			if ($changed) {
+				$db->update(
+					'journeys',
+					{
+						route => $json->encode( \@route ),
+					},
+					{ id => $row->{journey_id} }
+				);
+			}
+			if ( $count++ % 10000 == 0 ) {
+				printf( "    %2.0f%% complete\n", $count * 100 / $total );
+			}
+		}
+		say '    done';
+		if ($total_no_eva) {
+			printf( "    (%d of %d routes still lack some EVA IDs)\n",
+				$total_no_eva, $total );
+		}
+		if ($total_no_latlon) {
+			printf( "    (%d of %d routes still lack some lat/lon data)\n",
+				$total_no_latlon, $total );
+		}
+
+		$db->query(
+			qq{
+				update schema_version set version = 54;
+			}
+		);
+	},
+
 );
 
 sub sync_stations {
diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm
index f9bc2d5..2b86688 100755
--- a/lib/Travelynx/Controller/Traveling.pm
+++ b/lib/Travelynx/Controller/Traveling.pm
@@ -1323,8 +1323,6 @@ sub commute {
 sub map_history {
 	my ($self) = @_;
 
-	my $location = $self->app->coordinates_by_station;
-
 	if ( not $self->param('route_type') ) {
 		$self->param( route_type => 'polybee' );
 	}
diff --git a/lib/Travelynx/Model/Journeys.pm b/lib/Travelynx/Model/Journeys.pm
index 8c29e7d..b067d78 100755
--- a/lib/Travelynx/Model/Journeys.pm
+++ b/lib/Travelynx/Model/Journeys.pm
@@ -1120,9 +1120,8 @@ sub get_travel_distance {
 	my $distance_beeline      = 0;
 	my $skipped               = 0;
 	my $geo                   = GIS::Distance->new();
-	my @stations              = map { $_->[0] } @{$route_ref};
-	my @route                 = after_incl { $_ eq $from } @stations;
-	@route = before_incl { $_ eq $to } @route;
+	my @route                 = after_incl { $_->[0] eq $from } @{$route_ref};
+	@route = before_incl { $_->[0] eq $to } @route;
 
 	if ( @route < 2 ) {
 
@@ -1144,16 +1143,16 @@ sub get_travel_distance {
 		$prev_station = $station;
 	}
 
-	$prev_station = $self->{latlon_by_station}->{ shift @route };
-	if ( not $prev_station ) {
+	if ( not( defined $route[0][2]{lat} and defined $route[0][2]{lon} ) ) {
 		return ( $distance_polyline, 0, 0 );
 	}
 
-	for my $station_name (@route) {
-		if ( my $station = $self->{latlon_by_station}->{$station_name} ) {
+	$prev_station = shift @route;
+	for my $station (@route) {
+		if ( defined $station->[2]{lat} and defined $station->[2]{lon} ) {
 			$distance_intermediate += $geo->distance_metal(
-				$prev_station->[0], $prev_station->[1],
-				$station->[0],      $station->[1]
+				$prev_station->[2]{lat}, $prev_station->[2]{lon},
+				$station->[2]{lat},      $station->[2]{lon}
 			);
 			$prev_station = $station;
 		}