CREATE TABLE world_tree (
  id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
  name VARCHAR(128),
  type ENUM('planet', 'continent', 'region', 'country', 'district', 'city') NOT NULL,
  parent_id INT UNSIGNED,
  INDEX (parent_id),
  INDEX (type),
  INDEX (name)
) engine=innodb;
# insert the base-node
BEGIN;
INSERT INTO world_tree 
  VALUES ( 1, 'Earth', 'planet', NULL );
# feed the continents
INSERT INTO world_tree 
  SELECT DISTINCT NULL, continent, 'continent',
         (SELECT id 
            FROM world_tree
           WHERE name = 'Earth') 
    FROM Country; 
# regions
INSERT INTO world_tree 
  SELECT DISTINCT NULL, c.region,  'region',
         (SELECT id 
            FROM world_tree AS t
           WHERE t.name = c.continent AND t.type = 'continent' ) as parent_id
    FROM Country AS c; 
# countries
INSERT INTO world_tree 
  SELECT NULL, c.name, 'country',
         (SELECT id 
            FROM world_tree AS t
           WHERE t.name = c.region AND t.type = 'region') as parent_id
    FROM Country AS c; 
# fetch districts 
INSERT INTO world_tree 
  SELECT DISTINCT NULL, c.district, 'district',
         (SELECT id 
            FROM world_tree AS t
              JOIN Country AS co
                ON co.name = t.name
           WHERE co.code = c.countryCode AND t.type = 'country') as parent_id
    FROM City AS c; 
# cities
INSERT INTO world_tree 
  SELECT NULL, c.name, 'city',
         (SELECT t.id 
            FROM world_tree AS t 
              JOIN world_tree AS t2 
                ON t2.id = t.parent_id 
              JOIN Country as co 
                ON t2.name = co.name
           WHERE t.name = c.district 
             AND t.type = 'district' 
             AND co.code = c.countrycode ) as parent_id
    FROM City AS c; 

COMMIT;
