alter table data_preparation.ne_10m_admin_0_countries
    add column geom54009 public.geometry(MULTIPOLYGON, 54009)
    add column region_name varchar
    add column region_id int
;

update data_preparation.ne_10m_admin_0_countries
  set geom54009 = public.ST_Transform(geom, 54009);

update data_preparation.ne_10m_admin_0_countries
    set region_name = 'Americas', region_id = 1
where iso_a3 in (
    'CHL', 'BOL', 'PER', 'GUY', 'CRI', 'NIC', 'BRA', 'URY',
    'HTI', 'DOM', 'SLV', 'GTM', 'HND', 'ECU', 'MEX', 'BLZ',
    'PAN', 'VEN', 'TTO', 'ATG', 'JAM', 'DMA'
    );

update data_preparation.ne_10m_admin_0_countries
    set region_name = 'Asia and Pacific', region_id = 2
where iso_a3 in (
    'IDN', 'MYS', 'IND', 'PAK', 'BTN', 'UZB', 'VNM', 'KHM',
    'LAO', 'MMR', 'BGD', 'AFG', 'NPL', 'PNG', 'YEM', 'FJI',
    'PHL', 'LKA', 'KIR', 'TON', 'SLB', 'VUT', 'TLS', 'MUS',
    'FSM', 'BRN'
    );

update data_preparation.ne_10m_admin_0_countries
    set region_name = 'North and West Africa', region_id = 3
where iso_a3 in (
    'MAR', 'SLE', 'GIN', 'LBR', 'CAF', 'DJI', 'CIV', 'MLI',
    'SEN', 'NGA', 'BEN', 'TCD', 'DZA', 'CMR', 'NER', 'BFA',
    'TGO', 'GHA', 'GNB', 'MRT', 'GNQ', 'GMB', 'STP', 'CPV'
    );

update data_preparation.ne_10m_admin_0_countries
    set region_name = 'South and East Africa', region_id = 4
where iso_a3 in (
    'ETH', 'SSD', 'SOM', 'KEN', 'MWI', 'TZA', 'COD', 'NAM',
    'ZMB', 'SDN', 'AGO', 'ZWE', 'MOZ', 'SWZ', 'BDI', 'RWA',
    'UGA', 'LSO', 'EGY', 'MDG', 'COM', 'COG'
    );

update data_preparation.ne_10m_admin_0_countries
    set region_name = 'Other', region_id = 0
where region_id is null
