Project

General

Profile

1 11443 psarando
BEGIN;
2
3
-----------------------
4
-- Level 0 (Country) --
5
-----------------------
6
7
-- maps gadm2 level0 to geonames countries
8
CREATE TABLE gadm_country_lookup (
9
        countryid integer primary key references geonames (geonameid),
10
        name_0 text
11
);
12
13
------------------------------
14
-- Level 1 (State/Province) --
15
------------------------------
16
17
-- maps gadm2 level1 to geonames ADM1
18
CREATE TABLE gadm_stateprovince_lookup (
19
        stateprovinceid integer primary key references geonames (geonameid),
20
        name_0 text,
21
        name_1 text
22
);
23
24
/*
25
-- another way to create gadm_stateprovince_lookup, though this won't
26
-- pick up multiple matches from geonames (good or bad??)
27
CREATE TABLE gadm_stateprovince_lookup AS
28
SELECT DISTINCT name_0, name_1
29
  FROM gadm2;
30
ALTER TABLE gadm_stateprovince_lookup ADD COLUMN stateprovinceid integer;
31
UPDATE gadm_stateprovince_lookup gs
32
  SET stateprovinceid = g.geonameid
33
  FROM gadm_country_lookup gc,
34
       alternatenames a,
35
       geonames g,
36
       countries c
37
  WHERE gs.name_0=gc.name_0
38
    AND gs.name_1=a.alternatename
39
    AND a.geonameid=g.geonameid
40
    AND g.countrycode=c.iso
41
    AND gc.countryid=c.geonameid
42
    AND g.featurecode='ADM1';
43
-- UPDATE 2137
44
-- Time: 600.345 ms
45
*/
46
47
----------------------
48
-- Level 2 (County) --
49
----------------------
50
51
-- maps gadm2 level2 to geonames ADM2
52
CREATE TABLE gadm_county_lookup (
53
        countyid integer primary key references geonames (geonameid),
54
        name_0 text,
55
        name_1 text,
56
        name_2 text
57
);
58
59
COMMIT;