SQL code copied to buffer

SQLtest.online
Task : 
What is the total length of streets (in kilometers) in New York City?
Output the result in single column streets_length.

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 in 10 Minutes a Day by Ben Forta
SQL in 10 Minutes a Day by Ben Forta
Whether you're an application developer, database administrator, web application designer, mobile app developer, or Microsoft Office users, a good working knowledge of SQL is an important part of interacting with databases. And Sams Teach Yourself SQL in 10 Minutes offers the straightforward, practical answers you need to help you do your job.