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)
Top comments (0)