SQL Assessment: Distance between cities

SQL code copied to buffer

Task 2:

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

Explore over 360 diverse tasks on our platform.

Countries Database (PostGIS)

The Countries database is a PostGIS sample database designed for geographic and geospatial data analysis. It includes spatial information about countries, capitals, and New York City data such as census blocks, homicides, neighborhoods, streets, and subway stations, suitable for GIS applications and spatial queries.

Table List:

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)