Changes on GeoDataSource World Cities Database May 2019 update

GeoDataSource.com is pleased to announce the release of updates for the GeoDataSource World Cities Database. There are some changes for both the Free Edition and the Commercial Edition starting from May 2019 update. In this article, we will discuss about the changes in both Free Edition and Commercial Edition World Cities Database.

World Cities Database Free Edition Changes

A new column named cc_iso, ISO 3166 Primary Country Code has been added to the World Cities Database Free Edition. It is a two alphabetic character ISO 3166 Primary Country Code uniquely identifying a geopolitical entity (countries, dependencies, and areas of special sovereignty).

By adding the new column, the existing world_cities_free table can no longer be used. Please follow the steps below to create the world_cities_free table with the new table structure.

  1. Drop the current world_cities_free table by running the SQL below
    DROP TABLE `world_cities_free`;
  2. Create the new world_cities_free table by running the SQL below
    CREATE TABLE `world_cities_free`(
    	`cc_fips` VARCHAR(2),
    	`cc_iso` VARCHAR(2),
    	`full_name_nd` VARCHAR(200),
    	INDEX `idx_cc_iso` (`cc_iso`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  3. Load the data into the new world_cities_free table by running the SQL below
    LOAD DATA LOCAL
    INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
    INTO TABLE `world_cities_free`
    FIELDS TERMINATED BY '\t'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES;
  4. Done.

World Cities Database Commercial Edition Changes

World Cities Database Commercial Edition consists of Basic Edition, Premium Edition, Gold Edition, Platinum Edition and Titanium Edition. Some column names have been changed in the World Cities Database Commercial Edition for May 2019 update. The affected columns are the region_code and the sub_region_code fields. The field names have been amended to continent_id and sub_continent_id respectively. The continent_id is an id that determines the character mapping used in the continent name. It contains,

  • 1 = Africa
  • 2 = Americas
  • 3 = Antarctica
  • 4 = Asia
  • 5 = Europe
  • 6 = Oceania

The sub_continent_id is a sub-division of the continent id. It is used to represent the sub-continent name. For example,

  • 1A = Central Africa
  • 1B = Eastern Africa
  • 1C = Indian Ocean
  • 1D = Northern Africa
  • 1E = Southern Africa
  • 1F = Western Africa
  • 2A = Central America
  • 2B = North America
  • 2C = South America
  • 2D = West Indies
  • 3A = Antarctica
  • 3B = Atlantic Ocean
  • 4A = Central Asia
  • 4B = East Asia
  • 4C = Northern Asia
  • 4D = South Asia
  • 4E = South East Asia
  • 4F = South West Asia
  • 5A = Central Europe
  • 5B = Eastern Europe
  • 5C = Northern Europe
  • 5D = South East Europe
  • 5E = South West Europe
  • 5F = Southern Europe
  • 5G = Western Europe
  • 6A = North Pacific Ocean
  • 6B = Pacific
  • 6C = South Pacific Ocean

With the changes in the column names, the current database table can still be used as normal. However, the table columns should be modified to follow the latest coding standard. The table can be changed by running the following SQL commands.

ALTER TABLE `world_cities_titanium` CHANGE `region_code` `continent_id` INT;
ALTER TABLE `world_cities_titanium` CHANGE `sub_region_code` `sub_continent_id` VARCHAR(2);

 

Was this article helpful?

Related Articles