Dmitry Romanoff

Posted on

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