A water-quality programme needs an initial list of large urban footprints where a field team might inspect river crossings. It has only Natural Earth's generalized global layers, not a local stream inventory or water samples. The output must show which footprints intersect one or several mapped river lines, and which have no contact at this map scale.
In PostGIS, use urban_areas and rivers, both SRID 4326; their columns are below. Select urban footprints with area_sqkm >= 1000. Count intersecting river features where featurecla='River' (exclude lake centerlines), retaining footprints with zero mapped contacts. Return one polygon row per qualifying footprint with urban_id, area_sqkm, integer river_count, integer mapped_contact (1 when count > 0), integer multi_contact (1 when count > 1), survey_class, and geom. Set survey_class to no contact at this scale for zero, one mapped line for one, and multiple mapped lines for more. Order by river_count descending then urban_id.
The map is a reconnaissance inventory, not an assessment of water quality, flood exposure or legal river adjacency. Natural Earth's 50m rivers omit intermittent and smaller waterways and may be positionally uncertain; urban footprints derive from coarse early-2000s land-cover data. A zero means the two generalized layers do not intersect. Run previews your result and its classes; Submit grades it.
urban_areas — 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 |
urban_id | text | Footprint identifier, UA- and four digits |
area_sqkm | double precision | Natural Earth's own area of the whole footprint (km²) |
scalerank | integer | Prominence, lower is larger |
geom | geometry(Geometry, 4326) | Polygon |
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 | River identifier, RV- and four digits |
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.
urban_areasCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| urban_id | text |
| area_sqkm | double precision |
| scalerank | integer |
| 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.
UNEP GEMS/Water describes monitoring that informs water-quality action (https://www.unep.org/topics/fresh-water/water-quality/monitoring-water-quality). Natural Earth states its 50m river lines are generalized and omit intermittent rivers (https://www.naturalearthdata.com/downloads/50m-physical-vectors/50m-rivers-lake-centerlines/).
9 scored, 0 informational
Survey inventory fields
12%Preserves urban footprints
6%Uses spatial intersection
6%All large footprints remain in the screen
18%Correct large urban footprints
18%Total mapped river contacts
12%Footprints with mapped river contact
12%Footprints with multiple mapped line contacts
12%Spatial lookup uses the river index
6%