# Geographically closest 2 airports between 2 cities

I have a database of all airports and need a query that finds the shortest distance between airports in 2 locations (cities for example). Here is my query.

``````SELECT
a.city,
a.iata_faa,
md.min_distance
FROM
airports as a, (
SELECT
aa.point AS pointa,
ab.point AS pointb,
min(st_distance(aa.point,ab.point)) AS min_distance
FROM
airports AS aa,
airports AS ab
WHERE
aa.city = 'Miami' AND ab.city = 'Atlanta'
GROUP BY 1,2

) AS md

WHERE
st_distance(a.pointa, md.pointb) = md.min_distance
;
``````

I'm closer and tried the <-> operator in the ORDER BY clause but what I'm getting is and ambiguous column reference:

``````ERROR:  column reference "point" is ambiguous
LINE 23:   st_distance(a.point, md.point) = md.min_distance
``````

Revised query:

``````SELECT
a.city,
b.city,
md.min_distance
FROM
airports AS a, airports AS b,
(
SELECT
aa.point,
ab.point,
min(st_distance(aa.point,ab.point)) AS min_distance
FROM
airports AS aa,
airports AS ab
WHERE
aa.city = 'Miami' AND ab.city = 'Atlanta'
GROUP BY 1,2

) AS md

WHERE
a.city = 'Miami' AND b.city = 'Atlanta' AND
st_distance(a.point, md.point) = md.min_distance

GROUP BY 1,2
ORDER BY a.point <-> b.point::geometry limit 1;
;
``````
• Variations of this question get repeatedly asked (because it is important and difficult to get right), which is essentially a k nearest neighbour problem. Probably the easiest is to point you at this blog by Paul Ramsey, carto.com/blog/lateral-joins, which show use of both the <-> operator and the use of lateral joins. – John Powell Mar 17 '17 at 14:22
• I had a go at answering a similar question a couple of weeks back: gis.stackexchange.com/questions/229505/…, which is just a minor extension of Paul's post. – John Powell Mar 17 '17 at 14:24

If I understand correctly, you're asking for this:

Given all the airports in Atlanta ("City A"), and all the airports in Miami ("City B"), which pair of airports (one from Atlanta and one from Miami) are closest to each other?

For readability, I like using `WITH` clauses for this sort of thing:

``````WITH FirstCityAirports AS (
SELECT * FROM airports
WHERE airports.city = 'Atlanta'
),
SecondCityAirports AS (
SELECT * FROM airports
WHERE airports.city = 'Miami'
),
Distances AS (
SELECT FirstCityAirports.name AS FirstAirportName,
SecondCityAirports.name AS SecondAirportName,
ST_Distance(FirstCityAirports.point, SecondCityAirports.point) AS distance
FROM FirstCityAirports, SecondCityAirports
)
SELECT * FROM Distances ORDER BY distance DESC LIMIT 1;
``````

You have to be careful with the `ST_Distance` function if your points are stored in a non-projected coordinate system (i.e. latitude and longitude). If that is the case, you'll want to use something like this instead of `ST_Distance`:

``````ST_DistanceSpheroid(FirstCityAirports.point,
SecondCityAirports.point,
'SPHEROID["WGS 84",6378137,298.257223563]')
``````
• Given the absence of any distance operators, either ST_DWithin or the more recent <-> and <#> operators, the above query would be hideously inefficient on any non-trivial sized table, as it wouldn't use any spatial indexes. You would end up calculating all distances and then doing a sort, just to select the closest. Trust me, if you did this for all points to all other points on a table of 100 million rows, you would be waiting a few days. – John Powell Mar 17 '17 at 14:45
• I know this from bitter experience :D. – John Powell Mar 17 '17 at 14:50
• @JohnBarça: In this particular case, I don't think performance is a huge concern. I'm assuming that there are only a few airports in any given city (less than 100, say), and that there is an index on the `city` column (so that the `FirstCityAirports` and `SecondCityAirports` sub-queries are index scans). But your point is valid... if either of those assumptions is not true then this query will take forever. – csd Mar 17 '17 at 15:01
• That may be true. But, in general, it is always better to write things as though it were. Our website at work was fantastic in testing. After a few weeks it was taking people 30 seconds to log in, as certain indexes had never been added, and weren't a problem in complex joins on tables with a few hundred entries. Pedantic, but you get my point :-) – John Powell Mar 17 '17 at 15:19
• I was using cities to deal with smaller datasets when I was testing. The objective was to run it on countries, though the logic is the same either way. The WITH syntax is more accessible I agree, but building it that way creates a huge sequential scan. Even the way I did it, countries with lots of airports (USA & Germany for example) could take 10+ seconds to compute. I believe the subquery and calling min(st_distance(city1, city2)) is more efficient. – shotdsherrif Mar 18 '17 at 1:20

I was able to get it to work. Here is the query:

`````` SELECT
a.city,
a.iata_faa,
b.city,
b.iata_faa,
md.min_distance
FROM
airports AS a, airports AS b,
(
SELECT
aa.point,
ab.point,
min(st_distance(aa.point,ab.point))  AS min_distance
FROM
airports AS aa,
airports AS ab
WHERE
aa.country = \$1 AND ab.country = \$2
GROUP BY 1,2
ORDER BY aa.point <-> ab.point::geometry limit 1
) AS md

WHERE
a.country = \$1 AND b.country = \$2 AND
st_distance(a.point, b.point) = md.min_distance
GROUP BY 1,2,3,4,5
;
``````

Now I just need to turn the distance into kilometers but this issue is solved. Thanks to those who commented.