PostgreSQL. How to calculate the distance between two cities based on their corresponding GPS data?
Tags: postgres, database
Published: 2023-04-10 15:48:51

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)
```