Postgresql error in proximity calculation for latitude longitude

Question

I have a table of airport data imported from here in postgresql: http://ourairports.com/data/

Here's the DDL

create table flights.airports
(
id integer not null
    constraint airports_pkey
        primary key,
ident varchar(7) not null,
type varchar(14) not null,
name varchar(77) not null,
latitude_deg numeric(25,22) not null,
longitude_deg numeric(22,18) not null,
elevation_ft integer,
continent varchar(2) not null,
iso_country varchar(2) not null,
iso_region varchar(7) not null,
municipality varchar(60),
scheduled_service varchar(3) not null,
gps_code varchar(4),
iata_code varchar(3),
local_code varchar(7),
home_link varchar(128),
wikipedia_link varchar(128),
keywords varchar(256),
feed_url varchar(255),
created_at date,
input_line_num integer,
geom geometry(Point,4326)
)
;

Here's an example of the data:

id  ident   type    name    latitude_deg    longitude_deg   elevation_ft    continent   iso_country iso_region  municipality    scheduled_service   gps_code    iata_code   local_code  home_link   wikipedia_link  keywords
6523    00A heliport    Total Rf Heliport   40.07080078 -74.93360138    11  NA  US  US-PA   Bensalem    no  00A     00A         
6524    00AK    small_airport   Lowell Field    59.94919968 -151.6959991    450 NA  US  US-AK   Anchor Point    no  00AK        00AK            
6525    00AL    small_airport   Epps Airpark    34.8647995  -86.77030182    820 NA  US  US-AL   Harvest no  00AL        00AL            
6526    00AR    closed  Newport Hospital & Clinic Heliport  35.6087 -91.254898  237 NA  US  US-AR   Newport no                      00AR

I'm using the following PostgreSQL Haversine query to return the closest airports to a given Lat / Lon value:

SELECT * FROM flights.airports WHERE acos(sin(38.691055) * sin(latitude_deg) + cos(38.691055) * cos(latitude_deg) * cos(longitude_deg - (-121.233055))) * 6371 <= 1000;

source: http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates

The values that I've put into the query above (38.691055,-121.233055) are a point near MC CLELLAN AIRFIELD (Sacramento).

https://www.google.co.uk/maps/place/38°41'27.8"N+121°13'59.0"W/@38.691055,-121.3731307,11z/data=!4m5!3m4!1s0x0:0x0!8m2!3d38.691055!4d-121.233055?hl=en

However, when I run my query the select returns the following data,

MYERS FIELD,39.8849983215332,-86.50669860839844

Which is in Indiana, note the difference in the longitude.

https://www.google.co.uk/maps/dir/Myers+Field,+Lizton,+IN,+USA/38.691055,-121.233055/@39.8910608,-112.8731208,5z/data=!3m1!4b1!4m8!4m7!1m5!1m1!1s0x886cb47c5293f51b:0x29b57085edb34681!2m2!1d-86.5066666!2d39.8850438!1m0?hl=en

I've tested the CSV data and it is good, I've checked out the haversine query and it looks to be a standard approach, so I'm assuming that the problem is the way that I'm storing / presenting the latitude and longitude values, but I can't see what I'm doing wrong.

Answer 1

The formula in the link you have provided use radians. In your query, you are using degrees. Try converting the units of all your lat and long, using the radian() function. Also you swapped the last long1 and long2 in the formula.

SELECT * 
FROM flights.airports 
WHERE acos(
       sin(radians(38.691055)) 
         * sin(radians(latitude_deg)) 
       + cos(radians(38.691055)) 
         * cos(radians(latitude_deg)) 
         * cos( radians(-121.233055)
           - radians(longitude_deg))
       ) * 6371 <= 1000;