Project

General

Profile

1 11494 psarando
/*
2
  SQL statements to generate tables mapping geonames features at each
3
  relevant administative level (country, state/province, county/parish)
4
  to GADM2 features.
5
6
  Todo:
7
  * Be tolerant to presence/absence of administrative qualifiers in
8
    names? (Already doing this mildly for County/Co/Co.)
9
    - Municipality
10
    - Barrio
11
    - District
12
13
  * Get rid of rudundancy
14
  * Try matching against GADM2 varname_* columns?
15
16
  Jim Regetz
17
  NCEAS
18
  Created Nov 2012
19
*/
20
21
----------------------
22
-- Level 2 (County) --
23
----------------------
24
BEGIN;
25
26
TRUNCATE gadm_county_lookup;
27
28
-- map gadm2 level2 to geonames ADM2
29
INSERT INTO gadm_county_lookup (countyid)
30
  SELECT DISTINCT geonameid
31
    FROM geonames
32
    WHERE featurecode='ADM2';
33
-- INSERT 0 32374
34
-- Time: 13177.539 ms
35
36
-- try matching against geonames (names and alternatenames)
37
UPDATE gadm_county_lookup gcl
38
  SET name_0 = gadm.name_0,
39
      name_1 = gadm.name_1,
40
      name_2 = gadm.name_2
41
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm,
42
       geonames g,
43
       hierarchy h,
44
       gadm_stateprovince_lookup gsl
45
  WHERE (lower(gadm.name_2)=lower(g.name)
46
         OR lower(gadm.name_2) =
47
            ANY (string_to_array(lower(g.alternatenames), ',')))
48
    AND gcl.countyid = g.geonameid
49
    AND gcl.countyid = h.childid
50
    AND h.parentid = gsl.stateprovinceid
51
    AND gsl.name_0 = gadm.name_0
52
    AND gsl.name_1 = gadm.name_1
53
    AND g.featurecode='ADM2';
54
-- UPDATE 12352
55
-- Time: 27390.357 ms
56
57
-- try matching against alternatenames table
58
UPDATE gadm_county_lookup gcl
59
  SET name_0 = gadm.name_0,
60
      name_1 = gadm.name_1,
61
      name_2 = gadm.name_2
62
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm,
63
       alternatenames a,
64
       hierarchy h,
65
       geonames g,
66
       gadm_stateprovince_lookup gsl
67
  WHERE lower(gadm.name_2)=lower(a.alternatename)
68
    AND gcl.countyid = a.geonameid
69
    AND a.geonameid = g.geonameid
70
    AND gcl.countyid = h.childid
71
    AND h.parentid = gsl.stateprovinceid
72
    AND gsl.name_0 = gadm.name_0
73
    AND gsl.name_1 = gadm.name_1
74
    AND g.featurecode='ADM2'
75
    AND gcl.name_2 IS NULL;
76
-- UPDATE 0
77
-- Time: 6340.441 ms
78
79
-- map geonames '/Foo/ County' to gadm2 '/Foo/'
80
-- todo: other mappings like this???
81
UPDATE gadm_county_lookup gcl
82
  SET name_0 = gadm.name_0,
83
      name_1 = gadm.name_1,
84
      name_2 = gadm.name_2
85
  FROM (SELECT DISTINCT name_0, name_1, name_2 FROM gadm2) gadm,
86
       geonames g,
87
       hierarchy h,
88
       gadm_stateprovince_lookup gsl
89
  WHERE lower(gadm.name_2||' County')=lower(g.name)
90
    AND gcl.countyid = g.geonameid
91
    AND gcl.countyid = h.childid
92
    AND h.parentid = gsl.stateprovinceid
93
    AND gsl.name_0 = gadm.name_0
94
    AND gsl.name_1 = gadm.name_1
95
    AND g.featurecode='ADM2'
96
    AND gcl.name_2 IS NULL;
97
-- UPDATE 3000
98
-- Time: 22248.393 ms
99
100
COMMIT;