PostgreSQL. How to calculate the distance between two cities based on their corresponding GPS data?

I will be using the postgis/postgis:15-3.3 image for running Postgres with PostGIS extensions installed.

Step 1. Create a new table named cities using the following command:

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    latitude NUMERIC,
    longitude NUMERIC
);

Step 2. Populate the table with the city data using the following SQL INSERT statements:

INSERT INTO cities (name, latitude, longitude) VALUES ('Lisbon', 38.724874, -9.139604);
INSERT INTO cities (name, latitude, longitude) VALUES ('Porto', 41.158389, -8.629163);
INSERT INTO cities (name, latitude, longitude) VALUES ('Sintra', 38.800306, -9.379136);
INSERT INTO cities (name, latitude, longitude) VALUES ('Obidos', 39.362068, -9.157140);
INSERT INTO cities (name, latitude, longitude) VALUES ('Coimbra', 40.211491, -8.429200);
INSERT INTO cities (name, latitude, longitude) VALUES ('Covilha', 40.282650, -7.503260);
INSERT INTO cities (name, latitude, longitude) VALUES ('Fatima', 39.617207, -8.652142);

Step 3. Here is an example query that calculates the distance between two cities using their GPS data:

SELECT ST_DistanceSphere(
    ST_MakePoint(city1.longitude, city1.latitude),
    ST_MakePoint(city2.longitude, city2.latitude)
) / 1000 AS distance_in_km
FROM cities AS city1
CROSS JOIN cities AS city2
WHERE city1.name = 'Lisbon'
  AND city2.name = 'Porto';

In this query, ST_DistanceSphere is a PostGIS function that calculates the distance between two points on the surface of the earth. The ST_MakePoint function creates a point geometry from the longitude and latitude values of each city. The CROSS JOIN combines every row in cities with every other row to create a cartesian product. Finally, the WHERE clause filters the results to only include the distance between Lisbon and Porto.

This query will return the distance between Lisbon and Porto in kilometers.

SELECT ST_DistanceSphere(
    ST_MakePoint(city1.longitude, city1.latitude),
    ST_MakePoint(city2.longitude, city2.latitude)
) / 1000 AS distance_in_km
FROM cities AS city1
CROSS JOIN cities AS city2
WHERE city1.name = 'Lisbon'
  AND city2.name = 'Porto';
 distance_in_km  
-----------------
 274.07008450859
(1 row)

You can replace 'Lisbon' and 'Porto' with the names of any two cities in your cities table.

SELECT ST_DistanceSphere(
    ST_MakePoint(city1.longitude, city1.latitude),
    ST_MakePoint(city2.longitude, city2.latitude)
) / 1000 AS distance_in_km
FROM cities AS city1
CROSS JOIN cities AS city2
WHERE city1.name = 'Sintra'
  AND city2.name = 'Coimbra';
 distance_in_km  
-----------------
 176.81572062631
(1 row)

SELECT city1.name "from", city2.name "to", round(ST_DistanceSphere(
    ST_MakePoint(city1.longitude, city1.latitude),
    ST_MakePoint(city2.longitude, city2.latitude)
) / 1000) AS distance_in_km
FROM cities AS city1
CROSS JOIN cities AS city2
where city1.name != city2.name
order by 1, 2;
  from   |   to    | distance_in_km 
---------+---------+----------------
 Coimbra | Covilha |             79
 Coimbra | Fatima  |             69
 Coimbra | Lisbon  |            176
 Coimbra | Obidos  |            113
 Coimbra | Porto   |            107
 Coimbra | Sintra  |            177
 Covilha | Coimbra |             79
 Covilha | Fatima  |            123
 Covilha | Lisbon  |            223
 Covilha | Obidos  |            174
 Covilha | Porto   |            136
 Covilha | Sintra  |            230
 Fatima  | Coimbra |             69
 Fatima  | Covilha |            123
 Fatima  | Lisbon  |            108
 Fatima  | Obidos  |             52
 Fatima  | Porto   |            171
 Fatima  | Sintra  |            110
 Lisbon  | Coimbra |            176
 Lisbon  | Covilha |            223
 Lisbon  | Fatima  |            108
 Lisbon  | Obidos  |             71
 Lisbon  | Porto   |            274
 Lisbon  | Sintra  |             22
 Obidos  | Coimbra |            113
 Obidos  | Covilha |            174
 Obidos  | Fatima  |             52
 Obidos  | Lisbon  |             71
 Obidos  | Porto   |            205
 Obidos  | Sintra  |             65
 Porto   | Coimbra |            107
 Porto   | Covilha |            136
 Porto   | Fatima  |            171
 Porto   | Lisbon  |            274
 Porto   | Obidos  |            205
 Porto   | Sintra  |            270
 Sintra  | Coimbra |            177
 Sintra  | Covilha |            230
 Sintra  | Fatima  |            110
 Sintra  | Lisbon  |             22
 Sintra  | Obidos  |             65
 Sintra  | Porto   |            270
(42 rows)

SELECT city1.name "from", city2.name "to", round(ST_DistanceSphere(
    ST_MakePoint(city1.longitude, city1.latitude),
    ST_MakePoint(city2.longitude, city2.latitude)
) / 1000) AS distance_in_km
FROM cities AS city1
CROSS JOIN cities AS city2
where city1.name > city2.name
order by 3 desc, 1, 2;

  from   |   to    | distance_in_km 
---------+---------+----------------
 Porto   | Lisbon  |            274
 Sintra  | Porto   |            270
 Sintra  | Covilha |            230
 Lisbon  | Covilha |            223
 Porto   | Obidos  |            205
 Sintra  | Coimbra |            177
 Lisbon  | Coimbra |            176
 Obidos  | Covilha |            174
 Porto   | Fatima  |            171
 Porto   | Covilha |            136
 Fatima  | Covilha |            123
 Obidos  | Coimbra |            113
 Sintra  | Fatima  |            110
 Lisbon  | Fatima  |            108
 Porto   | Coimbra |            107
 Covilha | Coimbra |             79
 Obidos  | Lisbon  |             71
 Fatima  | Coimbra |             69
 Sintra  | Obidos  |             65
 Obidos  | Fatima  |             52
 Sintra  | Lisbon  |             22
(21 rows)