Table structure and fields details for the worldCities database

worldCities tables chart

The different codes and values included in the fields marked with * are described in the definitions table.

Main worldCities database

features_details table

Field nameField descriptionData type
feature_idFeature identifier
A number which uniquely identifies the feature.
integer (positive)
feature_typeGeneral description of the feature
Indicate the main type of feature (country, administrative division, city, etc...)
text (max. 6 characters)
feature_descriptionDetailed description of the feature
More detailed description than feature_type
text (max. 64 characters)

features_hierarchy table

Field nameField descriptionData type
feature_idFeature identifier
A number which uniquely identifies the feature.
integer (positive)
parent_idFeature identifier (feature_id) of the parent feature
Used in the classic parent-child relationship
integer (positive)
lftLeft value
Used for the Preorder Tree Traversal
integer (positive)
rgtRight value
Used for the Preorder Tree Traversal
integer (positive)

Related article: How to work with pre-ordered tree traversal hierarchical data

features_names table

Field nameField descriptionData type
feature_idFeature identifier
A number which uniquely identifies the feature.
integer (positive)
name_typeName type
Identifies if the name is in the local language, romanized version, translation, etc...
text (max. 3 characters)
iso_639_3ISO 639-3 code
A 3-character code uniquely identifying a language.
text (3 characters)
charactersWriting system
Indicates which alphabet is used for the feature name.
text (max. 24 characters)
shortnameShort form of the name
A specific part of the name that could substitute for the full name.
text (max. 128 characters)
shortname_ndShort name without diacritics and special characters
Diacritics are symbols (accents,...) situated on top or below letters.
text (max. 128 characters)
fullnameFull name, including diacritics and special characters
Diacritics are symbols (accents,...) situated on top or below letters.
text (max. 200 characters)
fullname_ndFull name, without diacritics and special characters
Diacritics are symbols (accents,...) situated on top or below letters.
text (max. 200 characters)

definitions table

This table provides descriptions of the different codes and values included in the following fields:

  • features_details table: field_type, field_description
  • features_names and language_pack (extension) tables: name_type
Field nameField descriptionData type
field_nameName of the field/columntext (max. 32 characters)
field_valueValue of the field/columntext (max. 32 characters)
iso_639_3ISO 639-3 code
A 3-character code uniquely identifying a language.
text (max. 5 characters)
value_shortdescShort description of the valuetext (max. 64 characters)
value_longdescMore detailed description of the valuetext (max. 65535 characters)

worldCities Extension – GPS Coordinates

features_coordinates table

Field nameField descriptionData type
feature_idFeature identifier
A number which uniquely identifies the feature.
integer (positive)
latitudeLatitude
Latitude in decimal degrees (WGS84); no sign (+) = North, negative sign (-) = South
decimal number
longitudeLongitude
Longitude in decimal degrees (WGS84); no sign (+) = East, negative sign (-) = West
decimal number

Related article: How to convert Decimal Degrees to Degree-Minute-Second (DMS) in MySQL


worldCities Extension – Language Pack

language_pack table

This table has the same structure as the names table in the main worldCities database.

Field nameField descriptionData type
feature_idFeature identifier
A number which uniquely identifies the feature.
integer (positive)
name_typeName type
Identifies if the name is in the local language, romanized version, translation, etc...
text (max. 3 characters)
iso_639_3ISO 639-3 code
A 3-character code uniquely identifying a language.
text (3 characters)
charactersWriting system
Indicates which alphabet is used for the feature name.
text (max. 24 characters)
shortnameShort form of the name
A specific part of the name that could substitute for the full name.
text (max. 128 characters)
shortname_ndShort name without diacritics and special characters
Diacritics are symbols (accents,...) situated on top or below letters.
text (max. 128 characters)
fullnameFull name, including diacritics and special characters
Diacritics are symbols (accents,...) situated on top or below letters.
text (max. 200 characters)
fullname_ndFull name, without diacritics and special characters
Diacritics are symbols (accents,...) situated on top or below letters.
text (max. 200 characters)

worldCities Extension – Standard Codes

features_codes table

Field nameField descriptionData type
feature_idFeature identifier
A number which uniquely identifies the feature.
integer (positive)
fips_alphaFIPS alpha code
A 2- or 3-character code identifying a geopolitical entity (country, state, region, etc...), according to the FIPS standard.
text (max. 3 characters)
fips_numericFIPS numeric code
A 2- or 3-digit code identifying a geopolitical entity (country, state, region, etc...), according to the FIPS standard.
text (max. 3 characters)
iso_alpha1ISO 3166 code, 1 letter
A 1-letter code identifying a geopolitical entity (country, state, region, etc...).
text (1 character)
iso_alpha2ISO 3166 code, 2 letters
A 2-letter code identifying a geopolitical entity (country, state, region, etc...).
text (2 characters)
iso_alpha3ISO 3166 code, 3 letters
A 3-letter code identifying a geopolitical entity (country, state, region, etc...)
text (3 characters)
iso_numericISO 3166 code, 3 digits
A 3-digit code identifying a geopolitical entity (state, region, etc...).
text (max. 3 characters)

worldCities Extension – Importance

importance table

Field nameField descriptionData type
feature_idFeature identifier
A number which uniquely identifies the feature.
integer (positive)
importanceRelative importance of the feature, on a 1-5 scaleinteger (positive)