SQL code copied to buffer

SQLtest.online
Task 213: 
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.

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

Use PostGIS (PostgreSQL) syntax to write your answer. Descriptions of tables are provided at the bottom of the screen.

Get hint Copy code Clear editor

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)
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
If you’re wondering whether you should learn SQL, the answer is simple: absolutely. SQL (Structured Query Language) is the language of data, and data powers every modern business. Whether you’re a data analyst, data scientist, business analyst, software engineer, financial analyst, product manager, or marketing professional, SQL helps you retrieve, analyze, and understand the information that drives decisions.