Countries database (PostGIS): spatial tables and SQL exercises

Countries is a PostGIS database for learning spatial SQL: world countries and capitals, plus New York City layers with census blocks, neighborhoods, streets and subway stations. On SQLtest.online you can query it right in your browser: solve exercises with automatic checking and run your own queries in the playground, with nothing to install.

• 7 spatial tables
• 246 countries
• 491 subway stations
• 11 SQL exercises

What is Countries

PostGIS is the PostgreSQL extension that adds geometry types and hundreds of spatial functions: distances, areas, intersections, coordinate transformations. This database lets you try them on familiar data.

The New York City tables come from the well-known PostGIS workshop "Introduction to PostGIS", and the world tables hold country borders and capitals. Together they cover points, lines and polygons in two coordinate systems.

What's inside

The tables fall into two groups.

The key thing to remember: the world tables store degrees, the New York tables store meters. Distances and areas in the New York layers come out in meters directly; for the world tables, cast to geography or transform the geometry first.

How much data the tables hold:

Table structure

Click a table to see its columns, a sample row and its keys.

Sample queries

These queries show how the data is connected. Copy any of them and run it in the playground.

A capital in its country: point coordinates with ST_X / ST_Y and a spatial check with ST_Contains.

SELECT c.name AS capital, co.name AS country,
       round(ST_Y(c.location)::numeric, 2) AS lat,
       round(ST_X(c.location)::numeric, 2) AS lon,
       ST_Contains(co.border, c.location) AS inside_border
FROM capitals c
JOIN countries co ON co.id = c.country_id
ORDER BY c.name
LIMIT 3;

Subway stations and their SRID: New York layers use the projected system 26918.

SELECT s.name AS station, s.borough, s.routes, ST_SRID(s.geom) AS srid
FROM nyc_subway_stations s
ORDER BY s.gid
LIMIT 3;

SQL exercises by topic

There are 11 PostGIS exercises on this database: distances, areas, lengths, conversions to text and JSON, and spatial joins. Solutions are checked automatically on a real PostgreSQL with PostGIS. The number on the right is how many exercises a topic has; the colored dots show its difficulty range.

• Geospatial data (PostGIS) 11

Where to start

The first exercises on the Countries database:

1. Extract Geometry as Text
2. Extract Geometry as JSON
3. Distance between cities
4. Country Area
5. Manhattan Subway Stations
6. Area of ​​the Neighborhood
7. Area of ​​the Neighborhood
8. Neighborhood Average Area
9. Length of New York Streets
10. Little Italy Stations

All Countries exercises →