Project

General

Profile

1 11443 psarando
BEGIN;
2
3
-- define custom function to pass characters back through latin-1
4
-- encoding to revert errors; in principle this could introduce new
5
-- errors, but it seems to do more good than harm
6
CREATE OR REPLACE FUNCTION u2L2u(text) RETURNS text AS $$
7
DECLARE
8
    string text;
9
BEGIN
10
  string :=
11
    convert_from(convert($1::bytea, 'UTF8', 'LATIN1'), 'UTF8');
12
  RETURN string;
13
EXCEPTION
14
  WHEN data_exception THEN
15
    RETURN $1;
16
END;
17
$$ LANGUAGE plpgsql;
18
19
CREATE TABLE geonames (
20
    geonameid serial primary key,
21
    name text,
22
    asciiname text,
23
    alternatenames text,
24
    latitude numeric,
25
    longitude numeric,
26
    featureclass char(1),
27
    featurecode text,
28
    countrycode char(2),
29
    cc2 char(60),
30
    admin1code text,
31
    admin2code text,
32
    admin3code text,
33
    admin4code text,
34
    population bigint,
35
    elevation int,
36
    dem int,
37
    timezone text,
38
    modification date
39
);
40
41
--
42
-- load geonames.org alternate names
43
--
44
45
CREATE TABLE alternateNames (
46
    alternateNameId serial primary key,
47
    geonameid int references geonames (geonameid),
48
    isolanguage varchar(7),
49
    alternateName varchar(200),
50
    isPreferredName char(1),
51
    isShortName char(1),
52
    isColloquial char(1),
53
    isHistoric char(1)
54
);
55
56
--
57
-- load geonames.org country info
58
--
59
60
CREATE TABLE countries (
61
    iso char(2),
62
    iso3 char(3),
63
    isonumeric text,
64
    fips text,
65
    country text UNIQUE,
66
    capital text,
67
    area text,
68
    population text,
69
    continent text,
70
    tld text,
71
    currencyCode text,
72
    currencyName text,
73
    phone text,
74
    postalCodeFormat text,
75
    postalCodeRegex text,
76
    languages text,
77
    geonameid int,
78
    neighbours text,
79
    equivalentFipsCode text
80
);
81
82
--
83
-- load geonames.org hierarchy info
84
--
85
86
CREATE TABLE hierarchy (
87
    parentId int,-- references geonames (geonameid),
88
    childId int,-- references geonames (geonameid),
89
    type text
90
);
91
92
--
93
-- tables for additional custom name-scrub mappings
94
--
95
96
-- table for custom country alternate names that we need
97
CREATE TABLE alt_country (
98
    country text references countries (country),
99
    alternatename text
100
);
101
102
-- table for custom stateprovince alternate names that we need
103
CREATE TABLE alt_stateprovince (
104
    country text references countries (country),
105
    stateprovince text,
106
    alternatename text
107
);
108
109
COMMIT;