Distance and Bearing: Difference between revisions
(Created page with "{{task}} 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 [https://raw.githubusercontent.com/jpatokal/openflights/master/data/airports.dat airports.dat] as reference. A Request comes from an airplane at position ( latitude, longitude ): ( '''51.514669, 2.198581''' )...") |
No edit summary |
||
Line 24: | Line 24: | ||
:* openflights.org/data: [https://openflights.org/data.html Airport, airline and route data] |
:* openflights.org/data: [https://openflights.org/data.html Airport, airline and route data] |
||
:* Movable Type Scripts: [https://www.movable-type.co.uk/scripts/latlong.html Calculate distance, bearing and more between Latitude/Longitude points] |
:* Movable Type Scripts: [https://www.movable-type.co.uk/scripts/latlong.html Calculate distance, bearing and more between Latitude/Longitude points] |
||
=={{header|SQL}}/{{header|PostgreSQL}}== |
|||
Create table and copy from URL. |
|||
<syntaxhighlight lang="sql">-- 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);</syntaxhighlight> |
|||
Functions for distance and bearing. |
|||
<syntaxhighlight lang="sql">-- 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;</syntaxhighlight> |
|||
Request from airplane at position ( 51.514669, 2.198581 ). |
|||
<syntaxhighlight lang="sql">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;</syntaxhighlight> |
|||
{{out}} |
|||
<pre> 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)</pre> |
Revision as of 16:52, 7 October 2022
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
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)