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;