The land-use commission that had its urban-share table built in Python now wants the same figure produced by the database directly, so the analysts can query it without a notebook. The number has to match the scripted one: each footprint cut at the state line, each piece measured on the ellipsoid, credited to the state it lies in.
Return one row per state with the urban footprint inside it.
| column | meaning |
|---|---|
postal | the state |
urban_km2 | area of urban footprint inside the state, km², 2 decimals |
urban_pct | that as a percentage of the state's area, 2 decimals |
geom | the state polygon, so the result draws on the map |
The tables are us_states and urban_areas, both SRID 4326 with a GiST index on geom; their columns are below.
Use ST_Intersection to cut each footprint to the state, and ST_Area on geography so the area is in square metres on the spheroid. A state with no footprint is a row with zeros. Order by urban_pct descending.
us_states — 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 |
state_id | text | Natural Earth adm1 code |
name | text | State name |
postal | text | Two-letter postal abbreviation |
region | text | Census region |
type | text | State | Federal District |
geom | geometry(Geometry, 4326) | Polygon / MultiPolygon |
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 | UA-0001 … UA-2143 |
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 |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
us_statesCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| state_id | text |
| name | text |
| postal | text |
| region | text |
| type | text |
| geom | geometry(Geometry,4326) |
urban_areasCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| urban_id | text |
| area_sqkm | double precision |
| scalerank | integer |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
Overlay-and-measure in the database is how a land-use, flood-extent or coverage figure becomes a view the whole organisation can query rather than a number one analyst can reproduce.
5 scored, 0 informational
Returns postal, urban_km2, urban_pct
17%One row per state
25%Uses ST_Intersection
25%Area on geography
25%Uses an outer join
8%