Distance and Bearing

Distance and Bearing
You are encouraged to solve this task according to the task description, using any language you may know.

It is very important in aviation to have knowledge of the nearby airports at any time in flight.

Task

Determine the distance and bearing from an Airplane to the 20 nearest Airports whenever requested. Use the non-commercial data from openflights.org airports.dat as reference.

A request comes from an airplane at position ( latitude, longitude ): ( 51.514669, 2.198581 ).

Your report should contain the following information from table airports.dat (column shown in brackets):

Name(2), Country(4), ICAO(6), Distance and Bearing calculated from Latitude(7) and Longitude(8).

Distance is measured in nautical miles (NM). Resolution is 0.1 NM.

Bearing is measured in degrees (°). 0° = 360° = north then clockwise 90° = east, 180° = south, 270° = west. Resolution is 1°.

See

•   openflights.org/data:  Airport, airline and route data
•   Movable Type Scripts:  Calculate distance, bearing and more between Latitude/Longitude points

Phix

without js -- file i/o
enum Airport_ID, Name, City, Country, IATA, ICAO, Latitude, Longitude, Altitude, Timezone, DST, Tz_Olson, Type, Source
function tabulate(sequence s)
    s = split(trim(s),"\n")
    for i,ri in s do
        ri = split(ri,',')
        if length(ri)!=14 then
            ri[2] &= ","&ri[3]
            ri[3..3] = {}
        end if
        for j in {Airport_ID, Latitude, Longitude, Altitude} do
            ri[j] = to_number(ri[j])
        end for
        for j in {Name,City,Country,ICAO} do
            ri[j] = trim(ri[j],`"`)
        end for
        s[i] = ri
    end for
    return s    
end function

constant airports = tabulate(get_text("airports.dat"))

function distance(atom lat1, lon1, lat2, lon2, integer units)
    atom dist = 0
    if lat1!=lat2 or lon1!=lon2 then
        atom radlat1 = lat1*PI/180,
             radlat2 = lat2*PI/180,
             radtheta = (lon1-lon2)*PI/180
        dist = sin(radlat1)*sin(radlat2) + cos(radlat1)*cos(radlat2)*cos(radtheta)
        dist = arccos(min(dist,1))*180/PI * 60*1.1515576 -- in Statute Miles
        if units = 'K' then dist *= 1.609344 end if     -- in Kilometres
        if units = 'n' then dist *= 0.868976 end if    -- in Nautical Miles
    end if
    return dist
end function

function bearing(atom lat1, lon1, lat2, lon2)
    atom bear = NULL
    if lat1!=lat2 or lon1!=lon2 then
        atom radlat1 = lat1*PI/180,
             radlat2 = lat2*PI/180,
             raddlon = (lon2-lon1)*PI/180,
             y = sin(raddlon)*cos(radlat2),
             x = cos(radlat1)*sin(radlat2) - sin(radlat1)*cos(radlat2)*cos(raddlon)
        bear = remainder(atan2(y, x)*180/PI+360,360)
    end if
    return bear;
end function

procedure query(atom lat,lon, integer rows=20)
    sequence r = {}
    for a in airports do
        atom lat2 = a[Latitude],
             lon2 = a[Longitude],
             dist = round(distance(lat,lon,lat2,lon2,'N'),10),
             bear = round(bearing(lat,lon,lat2,lon2))
        r = append(r,{a[Name],a[Country],a[ICAO],dist,bear})
    end for
    r = sort_columns(r,{4})[1..rows]
    printf(1,"                Name                 |    Country     | ICAO | Distance in NM | Bearing in ° \n")
    printf(1,"-------------------------------------+----------------+------+----------------+--------------\n")
    printf(1,"%s\n",{join(r,"\n",fmt:=" %-36s| %-15s| %4s |           %4.1f |          %3d")})
    printf(1,"(%d rows)",rows)
end procedure

query(51.514669, 2.198581)

Output:

                Name                 |    Country     | ICAO | Distance in NM | Bearing in ° 
-------------------------------------+----------------+------+----------------+--------------
 Koksijde Air Base                   | Belgium        | EBFN |           35.3 |          146
 Ostend-Bruges International Airport | Belgium        | EBOS |           36.0 |          127
 Kent International Airport          | United Kingdom | EGMH |           38.6 |          252
 Calais-Dunkerque Airport            | France         | LFAC |           39.6 |          196
 Westkapelle heliport                | Belgium        | EBKW |           49.0 |          105
 Lympne Airport                      | United Kingdom | EGMK |           59.4 |          240
 Ursel Air Base                      | Belgium        | EBUL |           60.8 |          114
 Southend Airport                    | United Kingdom | EGMC |           64.7 |          274
 Merville-Calonne Airport            | France         | LFQT |           64.9 |          163
 Wevelgem Airport                    | Belgium        | EBKT |           65.0 |          137
 Midden-Zeeland Airport              | Netherlands    | EHMZ |           65.9 |           90
 Lydd Airport                        | United Kingdom | EGMD |           66.8 |          235
 RAF Wattisham                       | United Kingdom | EGUW |           67.9 |          309
 Beccles Airport                     | United Kingdom | EGSM |           68.2 |          339
 Lille/Marcq-en-Baroeul Airport      | France         | LFQO |           68.7 |          146
 Lashenden (Headcorn) Airfield       | United Kingdom | EGKH |           71.6 |          250
 Le Touquet-C¶te d'Opale Airport     | France         | LFAT |           73.3 |          200
 Rochester Airport                   | United Kingdom | EGTO |           73.9 |          262
 Lille-Lesquin Airport               | France         | LFQQ |           76.2 |          149
 Thurrock Airfield                   | United Kingdom | EGMT |           78.7 |          272
(20 rows)

SQL/PostgreSQL

Create table and copy from URL.

-- create table airports with 14 columns
CREATE TABLE airports (
    Airport_ID serial PRIMARY KEY,
    Name VARCHAR NOT NULL,
    City VARCHAR,
    Country VARCHAR NOT NULL,
    IATA VARCHAR,
    ICAO VARCHAR,
    Latitude double precision NOT NULL,
    Longitude double precision NOT NULL,
    Altitude SMALLINT,
    Timezone VARCHAR,
    DST VARCHAR,
    Tz_Olson VARCHAR,
    Type VARCHAR,
    Source VARCHAR
);   

-- copy CSV airports.dat from URL 
COPY airports FROM 
PROGRAM 'curl "https://raw.githubusercontent.com/jpatokal/openflights/master/data/airports.dat"' 
WITH (FORMAT csv);

Functions for distance and bearing.

-- calculate distance
CREATE OR REPLACE FUNCTION calculate_distance(lat1 float, lon1 float, lat2 float, lon2 float, units varchar)
RETURNS numeric AS $dist$
    DECLARE
        dist float = 0;
        radlat1 float;
        radlat2 float;
        theta float;
        radtheta float;
    BEGIN
        IF lat1 = lat2 AND lon1 = lon2
            THEN RETURN dist;
        ELSE
            radlat1 = pi() * lat1 / 180;
            radlat2 = pi() * lat2 / 180;
            theta = lon1 - lon2;
            radtheta = pi() * theta / 180;
            dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);

            IF dist > 1 THEN dist = 1; END IF;

            dist = acos(dist);
            dist = dist * 180 / pi();

            -- Distance in Statute Miles
            dist = dist * 60 * 1.1515576; 
            
            -- Distance in Kilometres
            IF units = 'K' THEN dist = dist * 1.609344; END IF;

            -- Distance in Nautical Miles
            IF units = 'N' THEN dist = dist * 0.868976; END IF;

            dist = dist::numeric;
            RETURN dist;
        END IF;
    END;
$dist$ LANGUAGE plpgsql;


-- calculate bearing
CREATE OR REPLACE FUNCTION calculate_bearing(lat1 float, lon1 float, lat2 float, lon2 float)
RETURNS numeric AS $bear$
    DECLARE
        bear float = NULL;
        radlat1 float;
        radlat2 float;
        raddlon float;
        y float;
        x float;
        
    BEGIN
        IF lat1 = lat2 AND lon1 = lon2
            THEN RETURN bear;
        ELSE
            radlat1 = pi() * lat1 / 180;
            radlat2 = pi() * lat2 / 180;
            raddlon = pi() * (lon2 - lon1) / 180;

            y = sin(raddlon) * cos(radlat2);
            x = cos(radlat1) * sin(radlat2) - sin(radlat1) * cos(radlat2) * cos(raddlon);
            
            bear = atan2(y, x) * 180 / pi();
            bear = (bear::numeric + 360) % 360;
       
            RETURN bear;
        END IF;
    END;
$bear$ LANGUAGE plpgsql;

Request from airplane at position ( 51.514669, 2.198581 ).

Select 
   Name "Name",
   Country "Country",
   ICAO "ICAO",
   ROUND(calculate_distance(51.514669, 2.198581, Latitude, Longitude, 'N'), 1) "Distance in NM",
   ROUND(calculate_bearing(51.514669, 2.198581, Latitude, Longitude), 0) "Bearing in °"
From 
    airports
ORDER BY "Distance in NM"
LIMIT 20;

Output:

                Name                 |    Country     | ICAO | Distance in NM | Bearing in ° 
-------------------------------------+----------------+------+----------------+--------------
 Koksijde Air Base                   | Belgium        | EBFN |           30.7 |          146
 Ostend-Bruges International Airport | Belgium        | EBOS |           31.3 |          127
 Kent International Airport          | United Kingdom | EGMH |           33.5 |          252
 Calais-Dunkerque Airport            | France         | LFAC |           34.4 |          196
 Westkapelle heliport                | Belgium        | EBKW |           42.6 |          105
 Lympne Airport                      | United Kingdom | EGMK |           51.6 |          240
 Ursel Air Base                      | Belgium        | EBUL |           52.8 |          114
 Southend Airport                    | United Kingdom | EGMC |           56.2 |          274
 Merville-Calonne Airport            | France         | LFQT |           56.4 |          163
 Wevelgem Airport                    | Belgium        | EBKT |           56.5 |          137
 Midden-Zeeland Airport              | Netherlands    | EHMZ |           57.3 |           90
 Lydd Airport                        | United Kingdom | EGMD |           58.0 |          235
 RAF Wattisham                       | United Kingdom | EGUW |           59.0 |          309
 Beccles Airport                     | United Kingdom | EGSM |           59.3 |          339
 Lille/Marcq-en-Baroeul Airport      | France         | LFQO |           59.7 |          146
 Lashenden (Headcorn) Airfield       | United Kingdom | EGKH |           62.2 |          250
 Le Touquet-Côte d'Opale Airport     | France         | LFAT |           63.7 |          200
 Rochester Airport                   | United Kingdom | EGTO |           64.2 |          262
 Lille-Lesquin Airport               | France         | LFQQ |           66.2 |          149
 Thurrock Airfield                   | United Kingdom | EGMT |           68.4 |          272
(20 rows)

Wren

Translation of: SQL

Library: Wren-dynamic

Library: Wren-fmt

However, rather than use Wren-sql, we program it so that it can be run from Wren-CLI. Runtime is about 0.24 seconds.

import "io" for File
import "./dynamic" for Tuple
import "./fmt" for Fmt

var airportFields = [
    "airportID",
    "name",
    "city",
    "country",
    "iata",
    "icao",
    "latitude",
    "longitude",
    "altitude",
    "timezone",
    "dst",
    "tzOlson",
    "type",
    "source"
]
var Airport = Tuple.create("Airport", airportFields)

var fileName = "airports.dat" // local copy
var lines = File.read(fileName).trimEnd().split("\n")
var lc = lines.count
var airports = List.filled(lc, null)
for (i in 0...lc) {
    var fields    = lines[i].split(",").map { |f| f.replace("\"", "") }.toList
    if (fields.count == 15) {  // airport name has an embedded comma
        fields[1] = fields[1] + "," + fields[2]
        for (i in 2..13) fields[i] = fields[i+1]
    }
    var airportID = Num.fromString(fields[0])
    var name      = fields[1]
    var city      = fields[2]
    var country   = fields[3]
    var iata      = fields[4]
    var icao      = fields[5]
    var latitude  = Num.fromString(fields[6])
    var longitude = Num.fromString(fields[7])
    var altitude  = Num.fromString(fields[8])
    var timezone  = fields[9]
    var dst       = fields[10]
    var tzOlson   = fields[11]
    var type      = fields[12]
    var source    = fields[13]
    airports[i]   = Airport.new(airportID, name, city, country, iata, icao, latitude, longitude,
                                altitude, timezone, dst, tzOlson, type, source)
}

var calculateDistance = Fn.new { |lat1, lon1, lat2, lon2, units|
    if (lat1 == lat2 && lon1 == lon2) return 0
    var radlat1 = Num.pi * lat1 / 180
    var radlat2 = Num.pi * lat2 / 180
    var theta = lon1 - lon2
    var radtheta = Num.pi * theta / 180
    var dist = radlat1.sin * radlat2.sin + radlat1.cos * radlat2.cos * radtheta.cos
    if (dist > 1) dist = 1
    dist = dist.acos * 180 / Num.pi * 60 * 1.1515576  // distance in statute miles
    if (units == "K") dist = dist * 1.609344          // distance in kilometers
    if (units == "N") dist = dist * 0.868976          // distance in nautical miles
    return dist
}

var calculateBearing = Fn.new { |lat1, lon1, lat2, lon2|
    if (lat1 == lat2 && lon1 == lon2) return 0
    var radlat1 = Num.pi * lat1 / 180
    var radlat2 = Num.pi * lat2 / 180
    var raddlon = Num.pi * (lon2 - lon1) / 180
    var y = raddlon.sin * radlat2.cos
    var x = radlat1.cos * radlat2.sin - radlat1.sin * radlat2.cos * raddlon.cos
    var bear = y.atan(x) * 180 / Num.pi
    return (bear + 360) % 360
}

// request from airplane at position (51.514669, 2.198581)
var query = List.filled(airports.count, null)
for (i in 0...airports.count) {
    var a = airports[i]    
    var dist = calculateDistance.call(51.514669, 2.198581, a.latitude, a.longitude, "N")
    dist = (dist * 10).round / 10
    var bear = calculateBearing.call(51.514669, 2.198581, a.latitude, a.longitude).round
    query[i] = [a.name, a.country, a.icao, dist, bear]
}
query.sort { |q1, q2| q1[3] < q2[3] }[0..19]
System.print("                Name                 |    Country     | ICAO | Distance in NM | Bearing in ° ")
System.print("-------------------------------------+----------------+------+----------------+--------------")
var fmt = " $-36s| $-15s| $4s |           $4.1f |          $3d"
for (i in 0..19) {
    var q = query[i]
    Fmt.print(fmt, q[0], q[1], q[2], q[3], q[4])
}
System.print("(20 rows)")

Output:

                Name                 |    Country     | ICAO | Distance in NM | Bearing in ° 
-------------------------------------+----------------+------+----------------+--------------
 Koksijde Air Base                   | Belgium        | EBFN |           30.7 |          146
 Ostend-Bruges International Airport | Belgium        | EBOS |           31.3 |          127
 Kent International Airport          | United Kingdom | EGMH |           33.5 |          252
 Calais-Dunkerque Airport            | France         | LFAC |           34.4 |          196
 Westkapelle heliport                | Belgium        | EBKW |           42.6 |          105
 Lympne Airport                      | United Kingdom | EGMK |           51.6 |          240
 Ursel Air Base                      | Belgium        | EBUL |           52.8 |          114
 Southend Airport                    | United Kingdom | EGMC |           56.2 |          274
 Merville-Calonne Airport            | France         | LFQT |           56.4 |          163
 Wevelgem Airport                    | Belgium        | EBKT |           56.5 |          137
 Midden-Zeeland Airport              | Netherlands    | EHMZ |           57.3 |           90
 Lydd Airport                        | United Kingdom | EGMD |           58.0 |          235
 RAF Wattisham                       | United Kingdom | EGUW |           59.0 |          309
 Beccles Airport                     | United Kingdom | EGSM |           59.3 |          339
 Lille/Marcq-en-Baroeul Airport      | France         | LFQO |           59.7 |          146
 Lashenden (Headcorn) Airfield       | United Kingdom | EGKH |           62.2 |          250
 Le Touquet-Côte d'Opale Airport     | France         | LFAT |           63.7 |          200
 Rochester Airport                   | United Kingdom | EGTO |           64.2 |          262
 Lille-Lesquin Airport               | France         | LFQQ |           66.2 |          149
 Thurrock Airfield                   | United Kingdom | EGMT |           68.4 |          272