1

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
0

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.