A gazetteer team wants to drop the hand-maintained country attribute on its places table and derive it from a country polygon layer instead. Before switching, the lead asked for the disagreement list: every place where the polygon containing it is not the country the attribute says — including the places no polygon contains at all, because a harbour city at 1:50m is often a few hundred metres out to sea. If the list is long, the polygons are too coarse for the job.
Return every place whose containing country disagrees with its attribute.
| column | meaning |
|---|---|
place | the place's name |
attribute_iso | populated_places.iso_a2 |
polygon_iso | the iso_a2 of the country polygon containing the point — NULL when none does |
polygon_name | that country's name, or NULL |
geom | the place's point, so the result draws on the map |
The tables are populated_places and countries, both SRID 4326 with a GiST index on geom; their columns are below. Use ST_Contains.
A place no polygon contains is a disagreement and must be a row with NULL in the polygon columns — an inner join would silently hide exactly the coastline problem the audit is for. Compare the codes with IS DISTINCT FROM, so that NULL counts as different. Order by place.
populated_places — 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 |
place_id | text | PL- plus the Natural Earth id |
name | text | Place name |
country | text | Country the place is in, as an attribute |
iso_a2 | text | Two-letter country code, as an attribute; -99 where Natural Earth assigns none |
featurecla | text | Admin-0 capital | Admin-1 capital | Populated place | Scientific station | … |
capital | boolean | True for a national capital |
pop_max | integer | Upper population estimate (persons) |
pop_min | integer | Lower population estimate (persons) |
lat_attr | double precision | Natural Earth's LATITUDE attribute (degrees) |
lon_attr | double precision | Natural Earth's LONGITUDE attribute (degrees) |
timezone | text | IANA time zone |
geom | geometry(Geometry, 4326) | Point |
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 |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
populated_placesCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| place_id | text |
| name | text |
| country | text |
| iso_a2 | text |
| featurecla | text |
| capital | boolean |
| pop_max | integer |
| pop_min | integer |
| lat_attr | double precision |
| lon_attr | double precision |
| timezone | text |
| geom | geometry(Geometry,4326) |
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) |
These are where the teaching is. Read them twice.
Deriving an attribute from geometry is a standard clean-up, and this audit is the step that decides whether the geometry is good enough to derive it from.
5 scored, 0 informational
Returns place, attribute_iso, polygon_iso, polygon_name
18%One row per disagreement
36%Uses an outer join from the places
18%NULL-safe comparison
18%Uses ST_Contains
9%