CODEWITHSUNDEEP
sqloptimization
Dav7538
Dav7538

Reputation: 63

Optimizing geographical SQL query

I wrote a SQL Query that gets me the names of cities with airports on specific longitudes and latitudes. Is it possible to write this more efficiently (for example without Union or the subqueries I have used)? 

SELECT DISTINCT 
    C.Name 
FROM 
    Cities C 
WHERE 
    C.CityID IN (SELECT A.CityID FROM Airports A 
                 WHERE A.Latitude < 10 AND A.Longtitude < 5 
                   AND C.CityID = A.CityID 
                 UNION ALL 
                 SELECT A.CityID FROM Airports A 
                 WHERE A.Latitude > 50 AND A.Longtitude > 100 
                   AND C.CityID = A.CityID)

The schemas I am using for the cities is:

CREATE TABLE Cities
(
     CityID SERIAL PRIMARY KEY,
     Name VARCHAR(256) NOT NULL, 
     UNIQUE Name
);

and the schema for the airports are:

CREATE TABLE Airports
(
     IATA CHAR(3) PRIMARY KEY,
     Name VARCHAR(256),
     CityID INT NOT NULL REFERENCES Cities(CityID),
     Latitude DECIMAL NOT NULL,
     Longtitude DECIMAL NOT NULL,
     Altitude SMALLINT,
     UNIQUE (Name, CityID)
);

Upvotes: 1

Views: 62

Answers (2)

Jason W
Jason W

Reputation: 13179

This is an approach to avoid sub-queries, but I agree that Gordon's approach with EXISTS is the most efficient query.

SELECT DISTINCT C.Name
FROM Cities C
    INNER JOIN Airports A
        ON a.CityId = c.CityId
WHERE A.Latitude < 10 AND a.Longtitude < 5
    OR a.Latitude > 50 AND a.Longtitude > 100

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use exists. For efficiency, I would recommend EXISTS:

SELECT C.Name 
FROM Cities C 
WHERE EXISTS (SELECT 1
              FROM Airports a
              WHERE a.CityId = c.CityId AND
                    ((a.latitude < 10 AND a.longitude < 5) or
                     (a.latitude > 50 AND a.longitude > 100)
                    )
             );

You want an index on Airports(CityId, latitude, longitude) for this query.

Upvotes: 2

Related Questions