Project

General

Profile

1 11495 psarando
BEGIN;
2
3
SELECT setval('geonames_geonameid_seq', (SELECT max(geonameid) FROM geonames));
4
CREATE INDEX geonames_countrycode_idx ON geonames (countrycode);
5
CREATE INDEX geonames_featurecode_idx ON geonames (featurecode);
6
CREATE INDEX geonames_name_idx ON geonames (name);
7
8
-- augment geonames with some additional places we need, and update
9
-- hierarchy table accordingly
10
WITH newnames AS (
11
  INSERT INTO geonames
12
    (name, alternatenames, featurecode, countrycode )
13
    VALUES
14
    ('Antananarivo', NULL, 'ADM1', 'MG'),
15
    ('Antsiranana', NULL, 'ADM1', 'MG'),
16
    ('Fianarantsoa', NULL, 'ADM1', 'MG'),
17
    ('Mahajanga', NULL, 'ADM1', 'MG'),
18
    ('Toamasina', NULL, 'ADM1', 'MG'),
19
    ('Toliara', 'Toliary', 'ADM1', 'MG')
20
  RETURNING geonameid, countrycode
21
)
22
INSERT INTO hierarchy (parentid, childid)
23
  SELECT c.geonameid AS parentid,
24
         n.geonameid AS childid
25
    FROM newnames n,
26
         countries c
27
    WHERE n.countrycode = c.iso;
28
29
COMMIT;