-- The capital layer does not contain the country names as structured in the world country layer. 
-- In order to make subsequent joins, we need a unique field in both tables.
-- Via an intersect, we stick the country names to the capital layer
ALTER TABLE capital_cities ADD COLUMN country_name VARCHAR(50);
UPDATE capital_cities cc SET country_name = wc.name 
FROM world_countries wc
WHERE ST_INTERSECTS(cc.geom, wc.geom);

-- Procedures to detect which capital cities were not joined
SELECT city_name FROM capital_cities WHERE country_name IS NULL;

-- DETECT POSSIBLE CAPITAL CITIES NOT IN THEIR COUNTRIES (IDENTIFIED MONACO AS BEING INSIDE FRANCE)
-- SEARCH FOR DUPLICATES
SELECT country_name FROM capital_cities GROUP BY country_name HAVING count(*) > 1; 
-- RESULT
--     country_name
-- ---------------------
--  FRANCE
--  IRAN
--  ANTIGUA & BARBUDA
--  BENIN
--  American Samoa (US)
-- (5 rows)

-- Add manually the right country names
UPDATE capital_cities SET country_name = 'MONACO' where country_name='FRANCE' AND city_name='Monaco';
UPDATE capital_cities SET country_name = 'KUWAIT' where country_name='IRAN' AND city_name='Kuwait';
UPDATE capital_cities SET country_name = 'Montserrat (UK)' where country_name='ANTIGUA & BARBUDA' AND city_name='Plymouth';
UPDATE capital_cities SET country_name = 'TOGO' where country_name='BENIN' AND city_name='Lome';
UPDATE capital_cities SET country_name = 'SAMOA' where country_name='American Samoa (US)' AND city_name='Apia';
