Database structure

Explore city names, administrative divisions, populations and coordinates, with data provenance.

The free API exposes the 5,000 cities with the highest population values in this database release. This global selection is fixed: country filters never expand it.

Widgets displayed on AffectionPlus.com pages use the entire database, including for visitors without a subscription. On another site, the widget is limited to the free selection unless the site uses a token issued under an active eligible subscription.

Database structure

Each city has one row, identified by id. Main columns are in villes; additional attributes are in caracteristiques, linked by the same id. Missing information remains null, and text codes retain leading zeros.

Population figures do not all refer to the same year or geographic area. Ranking uses population, not estimated urban population.

Identical attributes shared by multiple cities are grouped in caracteristiques_communes and linked through profil_id. These profiles do not create additional cities.

The export contains 1,371,405 cities. Allow about 140 MB for the compressed file and 1.37 GB after restoration, including indexes. PostgreSQL with the pg_trgm extension is required.

createdb world_cities
gzip -dc world-cities.sql.gz | psql -X -v ON_ERROR_STOP=1 -d world_cities
psql -d world_cities -c 'SELECT count(*) FROM villes;'

villes.id = caracteristiques.id
caracteristiques.profil_id = caracteristiques_communes.id

public_cities · city_search · export_scopes · metadata

Property	Type	SQL column	Description
`admin1`	`string`	`villes.admin1`	Name of the first-level administrative division (region, state, Land, province). Source: GeoNames admin1CodesASCII and API Géo / INSEE (France).
`admin1_code`	`string`	`villes.admin1_code`	Composite administrative code of level 1 (GeoNames or INSEE: prefix). Source: GeoNames / INSEE.
`admin1_de`	`string`	`caracteristiques_communes.admin1_de`	Name of the first-level administrative division in German.
`admin1_en`	`string`	`caracteristiques_communes.admin1_en`	Name of the first-level administrative division in English.
`admin1_es`	`string`	`caracteristiques_communes.admin1_es`	Name of the first-level administrative division in Spanish.
`admin1_fr`	`string`	`caracteristiques_communes.admin1_fr`	Name of the first-level administrative division in French.
`admin1_geonames_id`	`string`	`caracteristiques_communes.admin1_geonames_id`	GeoNames identifier of the region / first-level division. Source: GeoNames admin1CodesASCII.
`admin1_iso_code`	`string`	`caracteristiques_communes.admin1_iso_code`	Official ISO 3166-2 code of the region or federated state (e.g. FR-ARA, US-CA, DE-BY). Source: Wikidata (property P300 associated with the region's GeoNames ID).
`admin1_it`	`string`	`caracteristiques_communes.admin1_it`	Name of the first-level administrative division in Italian.
`admin1_nl`	`string`	`caracteristiques_communes.admin1_nl`	Name of the first-level administrative division in Dutch.
`admin1_pl`	`string`	`caracteristiques_communes.admin1_pl`	Name of the first-level administrative division in Polish.
`admin1_pt`	`string`	`caracteristiques_communes.admin1_pt`	Name of the first-level administrative division in Portuguese.
`admin1_tr`	`string`	`caracteristiques_communes.admin1_tr`	Name of the first-level administrative division in Turkish.
`admin2`	`string`	`villes.admin2`	Name of the second-level administrative division (county, district, etc.). Source: GeoNames admin2Codes and API Géo / INSEE (France).
`admin2_code`	`string`	`villes.admin2_code`	Composite administrative code of level 2 (GeoNames or INSEE: prefix). Source: GeoNames / INSEE.
`admin2_de`	`string`	`caracteristiques_communes.admin2_de`	Name of the second-level administrative division in German.
`admin2_en`	`string`	`caracteristiques_communes.admin2_en`	Name of the second-level administrative division in English.
`admin2_es`	`string`	`caracteristiques_communes.admin2_es`	Name of the second-level administrative division in Spanish.
`admin2_fr`	`string`	`caracteristiques_communes.admin2_fr`	Name of the second-level administrative division in French.
`admin2_geonames_id`	`string`	`caracteristiques_communes.admin2_geonames_id`	GeoNames identifier of the second-level division. Source: GeoNames admin2Codes.
`admin2_it`	`string`	`caracteristiques_communes.admin2_it`	Name of the second-level administrative division in Italian.
`admin2_nl`	`string`	`caracteristiques_communes.admin2_nl`	Name of the second-level administrative division in Dutch.
`admin2_pl`	`string`	`caracteristiques_communes.admin2_pl`	Name of the second-level administrative division in Polish.
`admin2_pt`	`string`	`caracteristiques_communes.admin2_pt`	Name of the second-level administrative division in Portuguese.
`admin2_tr`	`string`	`caracteristiques_communes.admin2_tr`	Name of the second-level administrative division in Turkish.
`administrative_role`	`string`	`caracteristiques_communes.administrative_role`	Normalized administrative role (national_capital, admin1_seat, admin2_seat…). Source: Derived from the GeoNames feature_code.
`administrative_role_de`	`string`	`caracteristiques_communes.administrative_role_de`	Label of the administrative role in German.
`administrative_role_en`	`string`	`caracteristiques_communes.administrative_role_en`	Label of the administrative role in English.
`administrative_role_es`	`string`	`caracteristiques_communes.administrative_role_es`	Label of the administrative role in Spanish.
`administrative_role_fr`	`string`	`caracteristiques_communes.administrative_role_fr`	Label of the administrative role in French.
`administrative_role_it`	`string`	`caracteristiques_communes.administrative_role_it`	Label of the administrative role in Italian.
`administrative_role_nl`	`string`	`caracteristiques_communes.administrative_role_nl`	Label of the administrative role in Dutch.
`administrative_role_pl`	`string`	`caracteristiques_communes.administrative_role_pl`	Label of the administrative role in Polish.
`administrative_role_pt`	`string`	`caracteristiques_communes.administrative_role_pt`	Label of the administrative role in Portuguese.
`administrative_role_tr`	`string`	`caracteristiques_communes.administrative_role_tr`	Label of the administrative role in Turkish.
`alternate_names`	`json`	`caracteristiques.alternate_names`	JSON list of known spelling variants, exonyms and transliterations. Source: GeoNames alternateNamesV2 and initial data.
`area_km2`	`number`	`caracteristiques.area_km2`	Area in square kilometers (km²). Source: API Géo / INSEE (hectares converted to km²) and Wikidata (P2046).
`area_scope`	`string`	`caracteristiques.area_scope`	Exact entity covered by the area (commune:<INSEE> or wikidata_entity:<QID>). Source: API Géo / Wikidata.
`area_source`	`string`	`caracteristiques.area_source`	Reference dataset that provided the area (geo-fr:<INSEE> or wd:<QID>). Source: API Géo / Wikidata.
`continent`	`string`	`caracteristiques_communes.continent; lang → caracteristiques_communes.continent_en, caracteristiques_communes.continent_de, caracteristiques_communes.continent_es, caracteristiques_communes.continent_it, caracteristiques_communes.continent_pt, caracteristiques_communes.continent_nl, caracteristiques_communes.continent_pl, caracteristiques_communes.continent_tr`	Continent name in the language requested by lang; falls back to French. Source: UN M49 / CLDR.
`continent_code`	`string`	`caracteristiques_communes.continent_code`	Two-letter continent code (EU, AS, AF, NA, SA, OC, AN). Source: GeoNames countryInfo.txt.
`coordinate_match_distance_km`	`number`	`caracteristiques.coordinate_match_distance_km`	Distance in kilometers between the source coordinate and the matched GeoNames place. Source: Geodesic calculation (Haversine formula) during matching.
`coordinate_match_status`	`string`	`caracteristiques.coordinate_match_status`	Confidence status of the coordinate-based matching (name_agrees, coordinates_unique, coordinates_guess, geonames_direct). Source: AffectionPlus geographic validation pipeline.
`coordinate_source`	`string`	`caracteristiques_communes.coordinate_source`	Source of the GPS coordinates: OpenStreetMap when longitude and latitude are provided. Source: OpenStreetMap (ODbL license).
`country`	`string`	`villes.country`	Alias of country_code: ISO 3166-1 alpha-2 code of the country or territory.
`country_code`	`string`	`villes.country`	ISO 3166-1 alpha-2 country or territory code (e.g. FR, GB, DK, GL). Source: GeoNames and verified national context.
`country_code_iso3`	`string`	`caracteristiques_communes.country_code_iso3`	Normalized three-letter ISO 3166-1 alpha-3 country code (e.g. FRA, GBR, DNK). Source: GeoNames countryInfo.txt.
`country_name`	`string`	`villes.country_name; lang → villes.country_name_en, villes.country_name_de, villes.country_name_es, villes.country_name_it, villes.country_name_pt, villes.country_name_nl, villes.country_name_pl, villes.country_name_tr`	Country name in the language requested by lang; falls back to French. Source: CLDR / Babel.
`country_tld`	`string`	`caracteristiques_communes.country_tld`	National top-level Internet domain ccTLD (e.g. .fr, .uk, .dk). Source: GeoNames countryInfo.txt.
`currency_code`	`string`	`caracteristiques_communes.currency_code`	Official ISO 4217 currency code of the country or territory (e.g. EUR, USD, GBP, DKK). Source: GeoNames countryInfo.txt.
`data_sources`	`string`	`caracteristiques.data_sources`	List separated by ';' of all sources used to build the row. Source: Per-row provenance log (osm, gn, wd, insee-2023, geo-fr, cldr, un-m49, iso-3166-2).
`demonym_de`	`string`	`caracteristiques.demonym_de`	Attested demonym in German.
`demonym_en`	`string`	`caracteristiques.demonym_en`	Attested demonym in English.
`demonym_es`	`string`	`caracteristiques.demonym_es`	Attested demonym in Spanish.
`demonym_fr`	`string`	`caracteristiques.demonym_fr`	Attested demonym in French. Source: Wikidata P1549.
`demonym_it`	`string`	`caracteristiques.demonym_it`	Attested demonym in Italian.
`demonym_nl`	`string`	`caracteristiques.demonym_nl`	Attested demonym in Dutch.
`demonym_pl`	`string`	`caracteristiques.demonym_pl`	Attested demonym in Polish.
`demonym_pt`	`string`	`caracteristiques.demonym_pt`	Attested demonym in Portuguese.
`demonym_scope`	`string`	`caracteristiques.demonym_scope`	Scope to which the demonym applies (city or commune). Source: Wikidata / INSEE.
`demonym_source`	`string`	`caracteristiques.demonym_source`	Identifier of the Wikidata entity that is the source of the demonym. Source: Wikidata.
`demonym_tr`	`string`	`caracteristiques.demonym_tr`	Attested demonym in Turkish.
`demonyms_local`	`json`	`caracteristiques.demonyms_local`	JSON object of all attested demonym forms by language code. Source: Wikidata (P1549).
`density_km2`	`number`	`caracteristiques.density_km2`	Density of inhabitants per km² (calculated only if the dated population and the area have the same scope). Source: Verified calculation from [population] / [area_km2].
`department`	`string`	`villes.admin2; lang → caracteristiques_communes.admin2_en, caracteristiques_communes.admin2_de, caracteristiques_communes.admin2_es, caracteristiques_communes.admin2_it, caracteristiques_communes.admin2_pt, caracteristiques_communes.admin2_nl, caracteristiques_communes.admin2_pl, caracteristiques_communes.admin2_tr`	Name of the second-level administrative division in the requested language; falls back to admin2.
`duplicate_name_in_country`	`boolean`	`caracteristiques_communes.duplicate_name_in_country`	Boolean (true / false) indicating whether several cities in the same country share the same ASCII name. Source: Calculation over all GeoNames identifiers of the country.
`elevation_m`	`number`	`caracteristiques.elevation_m`	Elevation in meters (only when an explicit measurement is provided). Source: GeoNames.
`english_name`	`string`	`caracteristiques.english_name`	Documented English name.
`enrichment_status`	`string`	`caracteristiques_communes.enrichment_status`	Overall matching result (name_coordinates, unmatched, coordinates_guess, geonames_direct…). Source: AffectionPlus pipeline.
`feature_code`	`string`	`caracteristiques_communes.feature_code`	GeoNames classification code of the populated place (PPLC, PPLA, PPLA2, PPL…). Source: GeoNames.
`geonames_id`	`string`	`caracteristiques.geonames_id`	Numeric GeoNames identifier of the place. Source: GeoNames.
`historical_name`	`string`	`caracteristiques.historical_name`	Original name kept in the source column "nom historique"; it does not necessarily designate a former place name.
`id`	`string`	`villes.id`	Unique and stable identifier of the city, kept as text, independent of the country.
`importance_tier`	`number`	`caracteristiques.importance_tier`	Hierarchical index from 1 to 5 (1 = national capital or ≥ 1 M inhabitants; 2 = regional capital or ≥ 200k; 3 = administrative seat or ≥ 50k; 4 = ≥ 10k; 5 = locality). Source: Algorithmic calculation from [administrative_role], [feature_code] and [population].
`languages_official`	`json`	`caracteristiques_communes.languages_official`	JSON list of applicable official language codes. Source: Wikidata (P37) and GeoNames countryInfo.txt.
`languages_scope`	`json`	`caracteristiques_communes.languages_scope`	JSON object specifying whether the languages apply to the city (city) or the country (country). Source: Language scope metadata.
`languages_source`	`json`	`caracteristiques_communes.languages_source`	JSON object indicating the source of the official and spoken languages. Source: Wikidata and GeoNames countryInfo.
`languages_spoken`	`json`	`caracteristiques_communes.languages_spoken`	JSON list of documented spoken or used languages. Source: Wikidata (P2936) and GeoNames countryInfo.txt.
`languages_year`	`string`	`caracteristiques_communes.languages_year`	Reference year of the language data when dated. Source: Wikidata.
`latitude`	`number`	`villes.latitude`	GPS latitude in decimal degrees (WGS84), maximum local precision retained. Source: OpenStreetMap (ODbL) and compatible GeoNames matching.
`local_names`	`json`	`caracteristiques.local_names`	Documented local variants, in JSON format.
`longitude`	`number`	`villes.longitude`	GPS longitude in decimal degrees (WGS84), maximum local precision retained. Source: OpenStreetMap (ODbL) and compatible GeoNames matching.
`municipality_code`	`string`	`caracteristiques.municipality_code`	Official municipality code (notably the INSEE code in France). Source: API Géo / INSEE.
`name`	`string`	`villes.name; lang → villes.name_en, villes.name_de, villes.name_es, villes.name_it, villes.name_pt, villes.name_nl, villes.name_pl, villes.name_tr`	Name in the language requested by lang; falls back to French if this variant is missing.
`name_ascii`	`string`	`caracteristiques.name_ascii`	City name in pure ASCII characters (no accents), ideal for indexing and search. Source: GeoNames asciiname.
`name_language`	`string`	`lang ← villes.name_languages (bitmask)`	Attested language of the returned name; fr when the requested variant is missing.
`name_local_phonetic`	`string`	`caracteristiques.name_local_phonetic`	Reading aid in the Latin alphabet for names in non-Latin scripts. Source: GeoNames Latin variants and English name as fallback.
`official_website`	`string`	`caracteristiques.official_website`	Official website of the municipality or locality. Source: Wikidata (P856).
`phone_prefix`	`string`	`caracteristiques_communes.phone_prefix`	International telephone dialing code of the country or territory (e.g. +33, +44, +1). Source: GeoNames countryInfo.txt.
`population`	`number`	`villes.population`	Documented number of inhabitants (empty cell if unknown, never 0 by default). Source: INSEE (2023 municipal populations), Wikidata (P1082) or GeoNames depending on [population_source].
`population_proper`	`number`	`caracteristiques.population_proper`	Strict municipal population (within the administrative limits of the commune / locality). Source: INSEE (2023 municipal populations), Wikidata (P1082 municipal) or GeoNames.
`population_scope`	`string`	`caracteristiques.population_scope`	Geographic scope of the population (commune, wikidata_entity, geonames_place). Source: Enrichment pipeline traceability.
`population_source`	`string`	`caracteristiques.population_source`	Exact source of the population figure (insee-2023, wd:<QID>, gn:<id>, original_unverified). Source: Enrichment pipeline traceability.
`population_urban`	`number`	`caracteristiques.population_urban`	Population of the agglomeration / urban area / metropolis attached to the city. Source: Wikidata (urban and metropolitan areas P1082) and GeoNames spatial agglomeration calculation (excluding PPLX sub-districts).
`population_urban_source`	`string`	`caracteristiques.population_urban_source`	Source of the urban population (wd:<QID>, gn_agglomeration or proper_only). Source: AffectionPlus urban agglomeration traceability.
`population_year`	`string`	`caracteristiques.population_year`	Reference year of the census or population estimate. Source: INSEE (2023) or Wikidata point in time qualifier (P585).
`postal_code_format`	`string`	`caracteristiques_communes.postal_code_format`	Official postal code format / mask of the country (e.g. ##### in France). Source: GeoNames countryInfo.txt.
`postcode`	`string`	`caracteristiques.postcode`	One or more postal codes separated by semicolons (;), leading zeros preserved. Source: API Géo / La Poste (France), GeoNames Postal Codes, Wikidata or initial file.
`postcode_source`	`string`	`caracteristiques.postcode_source`	Source of the postal codes (geo-fr, gn-postal, wd, original_unverified). Source: Postal traceability.
`postcode_status`	`string`	`caracteristiques.postcode_status`	Postal coverage quality (commune_codes, documented_not_exhaustive, partial_codes, original_unverified). Source: Postal coverage diagnostic.
`region`	`string`	`villes.admin1; lang → caracteristiques_communes.admin1_en, caracteristiques_communes.admin1_de, caracteristiques_communes.admin1_es, caracteristiques_communes.admin1_it, caracteristiques_communes.admin1_pt, caracteristiques_communes.admin1_nl, caracteristiques_communes.admin1_pl, caracteristiques_communes.admin1_tr`	Name of the first-level administrative division in the requested language; falls back to admin1.
`source_id`	`string`	`villes.id`	Alias of id: identifier of the same city.
`subregion_code_m49`	`string`	`caracteristiques_communes.subregion_code_m49`	Official United Nations M49 numeric code of the geographic subregion (e.g. 155, 039). Source: United Nations — M49 statistical classification.
`subregion_m49`	`string`	`caracteristiques_communes.subregion_m49; lang → caracteristiques_communes.subregion_m49_en, caracteristiques_communes.subregion_m49_de, caracteristiques_communes.subregion_m49_es, caracteristiques_communes.subregion_m49_it, caracteristiques_communes.subregion_m49_pt, caracteristiques_communes.subregion_m49_nl, caracteristiques_communes.subregion_m49_pl, caracteristiques_communes.subregion_m49_tr`	Name of the UN M49 subregion in the language requested by lang; falls back to French.
`timezone`	`string`	`caracteristiques_communes.timezone`	IANA time zone identifier (e.g. Europe/Paris, America/New_York). Source: GeoNames.
`updated_at`	`string`	`caracteristiques.updated_at`	Date of the last enrichment or verification processing of the row (YYYY-MM-DD). Source: AffectionPlus pipeline.
`wikidata_id`	`string`	`caracteristiques.wikidata_id`	Wikidata entity identifier (e.g. Q90 for Paris). Source: Wikidata (via property P1566 GeoNames ID or P374 INSEE).
`wikipedia_language`	`string`	`caracteristiques.wikipedia_language`	Language code of the Wikipedia URL provided (e.g. fr, en, de). Source: Extracted from the subdomain of [wikipedia_url].
`wikipedia_url`	`string`	`caracteristiques.wikipedia_url`	Wikipedia link kept from the selected source. The language of the link is given by wikipedia_language; this link does not change with the lang parameter.

Sources and licenses

Population figures do not all refer to the same year or geographic area. Ranking uses population, not estimated urban population.

population_urban_source = gn_agglomeration means a heuristic estimate, not an official census. population_source = original_unverified means an uncorroborated historical value. Open licenses also apply to purchased data: attribution and, for a derivative database covered by ODbL, share-alike obligations. Payment covers the service and does not remove redistribution rights granted by those licenses.

Database structure

Cities on AffectionPlus

Database structure

Sources and licenses