Task 242:

Distance between cities

PostGIS (PostgreSQL)

Write a SQL query to calculate the distance between London and Paris. The calculation must be done using geospatial functions.
The result must be in kilometers and rounded to an integer.
Output one column:london_to_paris_distance - the distance between the cities in kilometers.

Expected result Columns: 1 · Rows: 1

london_to_paris_distance

Write your query below and click "Check it!". Tables are described in the right pane.

The query result will appear here. Ctrl+Enter - Run query

Countries Database: table structure and geospatial overview

The Countries database (PostGIS) is a sample dataset for geographic and geospatial analysis with SQL.

It includes spatial data for countries and capitals, plus New York City layers such as census blocks, homicides, neighborhoods, streets, and subway stations.

The Countries database contains 7 main tables.

More about the Countries database: schema, sample queries and all exercises →

List of tables

countries - list of countries with geometry.

idunique record identifier (PK)
namecountry name
bordercountry geometry (MultiPolygon, SRID 4326)

id	name	border
1	France	MultiPolygon(...) [SRID=4326]

PRIMARY KEY, btree (id)

capitals - list of capitals with location.

idunique record identifier (PK)
namecapital name
country_idreference to country (FK)
locationcapital location (Point, SRID 4326)

id	name	country_id	location
1	Paris	1	Point(...) [SRID=4326]

PRIMARY KEY, btree (id)
FOREIGN KEY (country_id) REFERENCES countries(id)

nyc_census_blocks - New York City census blocks with demographic data.

gidunique record identifier (PK)
blkidcensus block ID
popn_totaltotal population
popn_whitewhite population
popn_blackblack population
popn_nativnative population
popn_asianasian population
popn_otherother population
boronameborough name
geomcensus block geometry (MultiPolygon, SRID 4326)

gid	blkid	popn_total	popn_white	popn_black	popn_nativ	popn_asian	popn_other	boroname	geom
1	360050001001000	1000	500	200	50	150	100	Manhattan	MultiPolygon(...) [SRID=4326]

PRIMARY KEY, btree (gid)

nyc_homicides - New York City homicide incidents.

gidunique record identifier (PK)
incident_dincident date
boronameborough name
num_victimnumber of victims
primary_moprimary motive
idincident ID
weaponweapon used
light_darklight or dark condition
yearyear of incident
geomincident location (Point, SRID 4326)

gid	incident_d	boroname	num_victim	primary_mo	id	weapon	light_dark	year	geom
1	2003-01-01	Manhattan	1	Unknown	1	Firearm	D	2003	Point(...) [SRID=4326]

PRIMARY KEY, btree (gid)

nyc_neighborhoods - New York City neighborhoods.

gidunique record identifier (PK)
boronameborough name
nameneighborhood name
geomneighborhood geometry (MultiPolygon, SRID 4326)

gid	boroname	name	geom
1	Manhattan	Financial District	MultiPolygon(...) [SRID=4326]

PRIMARY KEY, btree (gid)

nyc_streets - New York City streets.

gidunique record identifier (PK)
idstreet ID
namestreet name
onewayone-way indicator
typestreet type
geomstreet geometry (LineString, SRID 4326)

gid	id	name	oneway	type	geom
1	1	Broadway	NO	avenue	LineString(...) [SRID=4326]

PRIMARY KEY, btree (gid)

nyc_subway_stations - New York City subway stations.

gidunique record identifier (PK)
objectidobject ID
idstation ID
namestation name
alt_namealternative name
cross_stcross street
long_namelong name
labellabel
boroughborough
nghbhdneighborhood
routesroutes
transferstransfers
colorcolor
expressexpress indicator
closedclosed indicator
geomstation location (Point, SRID 4326)

gid	objectid	id	name	alt_name	cross_st	long_name	label	borough	nghbhd	routes	transfers	color	express	closed	geom
1	1	1	Times Square	Times Sq	7th Ave	Times Square-42nd Street	Times Sq	Manhattan	Midtown	1,2,3,7,A,C,E,N,Q,R,S,W	42nd St	Red	Yes	No	Point(...) [SRID=4326]

PRIMARY KEY, btree (gid)