SQLize Online / PHPize Online  /  SQLtest Online

WITH flight_info AS ( SELECT f.flight_no, f.actual_departure, f.actual_arrival, ad1.coordinates AS departure_coordinates, ad2.coordinates AS arrival_coordinates, ad1.coordinates[1] AS departure_latitude, ad1.coordinates[0] AS departure_longitude, ad2.coordinates[1] AS arrival_latitude, ad2.coordinates[0] AS arrival_longitude, EXTRACT(EPOCH FROM (f.actual_arrival - f.actual_departure)) AS total_flight_time_sec FROM flights f JOIN airports_data ad1 ON f.departure_airport = ad1.airport_code JOIN airports_data ad2 ON f.arrival_airport = ad2.airport_code WHERE f.flight_id = '475' ) SELECT JSON_AGG( departure_longitude + (arrival_longitude - departure_longitude) * (1800 / total_flight_time_sec), departure_latitude + (arrival_latitude - departure_latitude) * (1800 / total_flight_time_sec) ) AS current_coordinates FROM flight_info;