BEGIN;

--

-- insert additional custom name-scrub mappings

--

-- add some custom stateprovince alternate names that we need

INSERT INTO alt_stateprovince

    (country, stateprovince, alternatename)

    VALUES

    ('Canada', 'British Columbia', 'B.C.')

  , ('Canada', 'Newfoundland and Labrador', 'Newfoundland & Labrador')

  , ('Canada', 'Newfoundland and Labrador', 'New Foundland And Labrador')

  , ('Canada', 'Newfoundland and Labrador', 'Labrador & Newfoundland')

  , ('Canada', 'Northwest Territories', 'North West Territories')

  , ('Canada', 'Northwest Territories', 'Northwest Territorie')

  , ('Canada', 'Northwest Territories', 'NWT')

  , ('Canada', 'Québec', 'Quebéc')

  , ('Canada', 'Québec', 'Pq')

  , ('Canada', 'Québec', 'PQ')

  , ('United States', 'Hawaii', 'Hawai`i')

  , ('United States', 'North Carolina', 'N. Carolina')

  , ('United States', 'North Dakota', 'N. Dakota')

  , ('United States', 'South Dakota', 'S. Dakota')

  , ('Kenya', 'Coast Province', 'Coast')

  , ('Mexico', 'Estado de Baja California', 'Baja California Norte')

  , ('Mexico', 'Estado de Baja California', 'Baja California (Norte)')

  , ('Mexico', 'Estado de Baja California', 'Baja California, Norte de')

  , ('Mexico', 'Estado de Baja California', 'Baja California (state)')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja California, Sur de')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja California Sur (state)')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja Califronia')

  , ('Mexico', 'Estado de Baja California Sur', 'Baja Cal. Sur')

  , ('Ecuador', 'Provincia de Sucumbíos', 'Sucumbíos')

;

-- additions for mapping to gadm2 level 1 units

--  - Los Lagos -> Los Rios is imperfect but needed for gadm

--  - Orellana -> Napo is imperfect (wrong??) but needed for gadm

--  - Mapping both Concelho de Macau and Concelho das Ilhas to Ilhas

--  - Nassa seems to be a gadm misspelling? (Niassa)

-- not addressed:

--  - Bahamas: unmapped gadm level1 entities may be obsolete?

--  - French Guiana has arrondissements as adm0 in gadm, low level?

--  - French Polynesia has no adm1 in gadm

--  - Madagascar: Sava is (was?) inside gadm's Antsiranana province

--  - Philippines: GN has 17 regions at level1, gadm has 82 provinces

--  - Sri Lanka: GN ADM2 matches gadm level1; bien data side with GN

--  - Venezuela: Dependencias Federales don't seem to be in GADM

--  - Vietnam: GN ADM1 matches gadm level2; bien data side with GN?

INSERT INTO alt_stateprovince

    (country, stateprovince, alternatename)

    VALUES

    ('Cambodia', 'Khétt Siĕm Réab', 'Siemréab')

  , ('Chile', 'Región del Biobío', 'Bío-Bío')

  , ('Chile', 'Región del Libertador General Bernardo O’Higgins', 'Libertador General Bernardo O''Higgins')

  , ('Chile', 'Región de Los Ríos', 'Los Lagos')

  , ('Chile', 'Providencia y Santa Catalina, Departamento de Archipiélago de San Andrés', 'San Andrés y Providencia')

  , ('Dominican Republic', 'Provincia de Baoruco', 'Bahoruco')

  , ('Ecuador', 'Provincia de Morona Santiago', 'Morona Santiago')

  , ('Ecuador', 'Provincia de Napo', 'Orellana')

  , ('Ecuador', 'Provincia de Zamora Chinchipe', 'Zamora Chinchipe')

  , ('Gabon', 'Province du Woleu-Ntem', 'Wouleu-Ntem')

  , ('Ghana', 'Brong-Ahafo Region', 'Brong Ahafo')

  , ('Guatemala', 'Departamento de Quetzaltenango', 'Quezaltenango')

  , ('Haiti', 'Département de l''Artibonite', 'L''Artibonite')

  , ('Liberia', 'Grand Bassa County', 'GrandBassa')

  , ('Liberia', 'Grand Gedeh County', 'GrandGedeh')

  , ('Macao', 'Concelho de Macau', 'Ilhas')

  , ('Mozambique', 'Niassa Province', 'Nassa')

  , ('Norway', 'Østfold fylke', 'Ãstfold')

  , ('Peru', 'Región de Huánuco', 'Huánuco')

  , ('Puerto Rico', 'Guánica Municipio', 'Guánica')

  , ('Puerto Rico', 'Loíza Municipio', 'Loíza')

  , ('Puerto Rico', 'Manatí Municipio', 'Manatí')

  , ('Puerto Rico', 'Peñuelas Municipio', 'Peñuelas')

  , ('Puerto Rico', 'Río Grande Municipio', 'Río Grande')

  , ('Puerto Rico', 'San Germán Municipio', 'San Germán')

  , ('Puerto Rico', 'San Sebastián Municipio', 'San Sebastián')

  , ('Russia', 'Karachayevo-Cherkesskaya Respublika', 'Karachay-Cherkess')

  , ('Syria', 'Muḩāfaz̧at al Lādhiqīyah', 'Lattakia')

  , ('Togo', 'Région Centrale', 'Centre')

;

COMMIT;