How to calculate the distance between Kyoto and Tokyo using a PostgreSQL database?

#postgres #database #sql #howto

Let's create a Docker container named "some-postgis" running PostgreSQL with PostGIS extensions enabled, and sets the password for the PostgreSQL database to "mysecretpassword".

The container will run in the background until stopped or removed.

docker run --name some-postgis -e POSTGRES_PASSWORD=mysecretpassword -d postgis/postgis

Unable to find image 'postgis/postgis:latest' locally
latest: Pulling from postgis/postgis
04e7578caeaa: Pull complete
20333c6f31ac: Pull complete
186040651c97: Pull complete
c2e240f76f8f: Pull complete
213c8153be0a: Pull complete
15c269e14d99: Pull complete
c21b8876507e: Pull complete
e397c567af1a: Pull complete
0322ead07fa8: Downloading [=========================>                         ]  50.05MB/98.65MB
c67fc509c06b: Download complete
73301da916ba: Download complete
a6c608b7db13: Download complete
ef1e37b6f7ff: Download complete
031b1749ad91: Download complete
cb6dbc5da179: Downloading [=================================>                 ]  45.75MB/67.56MB
4f4fb700ef54: Download complete
6366b59e2d4c: Download complete
b3ce1f65bc64: Download complete
Digest: sha256:01f46e1a92d6107555eb7105875f13079e006e67511eb9f6915565bb51345020
Status: Downloaded newer image for postgis/postgis:latest
889be231e05a460cdf64f1a1f80bf08c5e34ef4c11492c7fe72a202eba695fa2

docker exec -ti some-postgis psql -U postgres

dmi@dmi-VirtualBox:~/my_pg_dbs$ docker exec -ti some-postgis psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg110+2))
Type "help" for help.

postgres=#

This command creates a table named "city" with columns for an auto-incrementing ID, city name, latitude, and longitude coordinates. This table can be used to store information about cities, such as their geographical locations.

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

INSERT INTO city (name, latitude, longitude) VALUES ('Tokyo', 35.689487, 139.691711);
INSERT INTO city (name, latitude, longitude) VALUES ('Kyoto', 35.011635, 135.768036);

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

This PostgreSQL database query utilizes PostGIS functions for working with geographic data.

postgres=# SELECT ST_DistanceSphere(
    ST_MakePoint(city1.longitude, city1.latitude),
    ST_MakePoint(city2.longitude, city2.latitude)
) / 1000 AS distance_in_km
FROM city AS city1
CROSS JOIN city AS city2
WHERE city1.name = 'Kyoto'
  AND city2.name = 'Tokyo';
   distance_in_km
--------------------
 363.71986440044003
(1 row)

postgres=#

Specifically, it calculates the distance in kilometers between two cities - Kyoto and Tokyo.

Here's what the query does:

SELECT ST_DistanceSphere(...) / 1000 AS distance_in_km: This part of the query calculates the distance between two points in spherical coordinates, and then divides the result by 1000 to convert it to kilometers. The ST_DistanceSphere function calculates the distance between two points on a sphere (in this case, the Earth's surface).

ST_MakePoint(city1.longitude, city1.latitude): This function creates a geographic point with the specified longitude and latitude. In this case, it creates a point for Kyoto.

ST_MakePoint(city2.longitude, city2.latitude): Same as above, but for Tokyo.

FROM city AS city1 CROSS JOIN city AS city2: Here, each city from the city table is combined with itself, generating all possible combinations of city pairs.

WHERE city1.name = 'Kyoto' AND city2.name = 'Tokyo': Only combinations of Kyoto and Tokyo cities are filtered here to calculate the distance between them.

Thus, after executing this query, you will obtain the distance between Kyoto and Tokyo in kilometers. In this case, the distance is approximately 363.72 kilometers.