A hydrology desk wants a one-line summary per country — how many of the mapped rivers touch it — to decide where its next data purchase goes. The countries with nothing at this scale are the ones it needs to see, so they cannot be missing from the table.
Return one row per country with the number of river features that intersect it.
| column | meaning |
|---|---|
iso_a3 | the country's code |
name | the country's name |
rivers | how many features in rivers intersect the country polygon |
geom | the country polygon, so the result draws on the map |
The tables are countries and rivers, both SRID 4326 with a GiST index on geom; their columns are below.
A river that crosses the country, runs along its border or merely touches it intersects it; count it once per country. A country with none is a row with zero. Order by rivers descending, then iso_a3.
countries — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
country_id | text | CO- plus the ISO 3166-1 alpha-3 code |
name | text | Short name |
name_long | text | Long name |
iso_a3 | text | ISO 3166-1 alpha-3; -99 where Natural Earth assigns none |
iso_a2 | text | ISO 3166-1 alpha-2; -99 where Natural Earth assigns none |
continent | text | Continent |
subregion | text | UN subregion |
pop_est | double precision | Population estimate (persons) |
pop_year | integer | Year of the estimate |
gdp_md | integer | GDP, millions of US dollars (USD m) |
gdp_year | integer | Year of the GDP figure |
economy | text | Natural Earth economy class |
income_group | text | World Bank income group |
geom | geometry(Geometry, 4326) | Polygon / MultiPolygon |
rivers — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
river_id | text | RV-0001 … RV-0462 |
name | text | Name as labelled |
name_en | text | English name |
featurecla | text | River | Lake Centerline |
scalerank | integer | Prominence, lower is more prominent |
geom | geometry(Geometry, 4326) | MultiLineString |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
countriesCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| country_id | text |
| name | text |
| name_long | text |
| iso_a3 | text |
| iso_a2 | text |
| continent | text |
| subregion | text |
| pop_est | double precision |
| pop_year | integer |
| gdp_md | integer |
| gdp_year | integer |
| economy | text |
| income_group | text |
| geom | geometry(Geometry,4326) |
riversCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| river_id | text |
| name | text |
| name_en | text |
| featurecla | text |
| scalerank | integer |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
Feature-count-per-polygon with an outer join is the most common summary query in any spatial database, and the zeros are usually the rows the requester cares about.
4 scored, 0 informational
Returns iso_a3, name, rivers
20%One row per country
40%Uses ST_Intersects
20%Uses an outer join
20%