Task 211:

Distance between cities

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.

Use PostGIS (PostgreSQL) syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.

Get hint

Copy code Clear editor

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.

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)

Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu

As data floods into your company, you need to put it to work right away―and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.

Practice

Lessons

Playground

Test

Distance between cities

Login

Register

Distance between cities