BEGIN;

SELECT setval('geonames_geonameid_seq', (SELECT max(geonameid) FROM geonames));
CREATE INDEX geonames_countrycode_idx ON geonames (countrycode);
CREATE INDEX geonames_featurecode_idx ON geonames (featurecode);
CREATE INDEX geonames_name_idx ON geonames (name);

-- augment geonames with some additional places we need, and update
-- hierarchy table accordingly
WITH newnames AS (
  INSERT INTO geonames
    (name, alternatenames, featurecode, countrycode )
    VALUES
    ('Antananarivo', NULL, 'ADM1', 'MG'),
    ('Antsiranana', NULL, 'ADM1', 'MG'),
    ('Fianarantsoa', NULL, 'ADM1', 'MG'),
    ('Mahajanga', NULL, 'ADM1', 'MG'),
    ('Toamasina', NULL, 'ADM1', 'MG'),
    ('Toliara', 'Toliary', 'ADM1', 'MG')
  RETURNING geonameid, countrycode
)
INSERT INTO hierarchy (parentid, childid)
  SELECT c.geonameid AS parentid,
         n.geonameid AS childid
    FROM newnames n,
         countries c
    WHERE n.countrycode = c.iso;

COMMIT;