Finding Unique Airline Routes with SQLite-CodePudding

I have two tables:

AirPorts (IATA_CODE TEXT, LATITUDE REAL, LONGITUDE REAL)
AirRoutes (ROUTE_FROM TEXT, ROUTE_TO TEXT)

The AirRoutes table includes routes from Airports that are not listed in the Airports table. It also includes multiple occurrences of the same route.

First of all, I wish to select all unique Routes that start and end at airports that exist in the 'AirPorts' table. I don't know if this is the best way to do it, but it seems to work:

SELECT DISTINCT AirRoutes.ROUTE_FROM, AirRoutes.ROUTE_TO FROM AirRoutes
WHERE EXISTS 
   (SELECT IATA_CODE
    FROM AirPorts_SM
    WHERE AirRoutes.ROUTE_FROM = IATA_CODE)
AND EXISTS
   (SELECT IATA_CODE
    FROM AirPorts_SM
    WHERE AirRoutes.ROUTE_TO = IATA_CODE);

Next, I would like to filter out B-A routes where A-B routes are returned, so if there is a route AMS to DUB, I don't want to return DUB to AMS as well.

How would you do that?

Finally, and I don't know if this is possible, I would like to get the positions included, so each row would be like:

ROUTE_FROM=AMS, LAT_FROM=52.3, LON_FROM=4.76, ROUTE_TO=DUB, LAT_TO=53.42, LON_TO=-6.24

How would you do that? Perhaps this is too adventurous for one query, and I should break it into three parts?

CodePudding user response：

Use a CTE to get all the distinct pairs of ROUTE_FROM and ROUTE_TO of AirRoutes and join it to 2 copies of AirPorts like this:

WITH cte AS (
  SELECT DISTINCT 
         MIN(ROUTE_FROM, ROUTE_TO) ROUTE_FROM,
         MAX(ROUTE_FROM, ROUTE_TO) ROUTE_TO
  FROM AirRoutes
)  
SELECT c.ROUTE_FROM, f.LATITUDE LAT_FROM, f.LONGITUDE LON_FROM,
       c.ROUTE_TO, t.LATITUDE LAT_TO, t.LONGITUDE LON_TO
FROM cte c
INNER JOIN AirPorts f ON f.IATA_CODE = c.ROUTE_FROM
INNER JOIN AirPorts t ON t.IATA_CODE = c.ROUTE_TO;