-
Notifications
You must be signed in to change notification settings - Fork 0
/
os_postgresql_import.plpgsql
246 lines (212 loc) · 11.7 KB
/
os_postgresql_import.plpgsql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
DROP FUNCTION IF EXISTS public.import_pc_opendata(varchar);
CREATE OR REPLACE FUNCTION public.import_pc_opendata(in_data varchar)
RETURNS boolean AS
$BODY$
DECLARE
v_data_root varchar;
v_data_main varchar;
v_data_areas varchar;
v_main_table_created boolean;
v_sql text;
BEGIN
v_data_root := in_data || '/';
v_data_main := v_data_root || 'Data/';
v_data_areas := v_data_root || 'Doc/';
RAISE NOTICE '%: Import starting with data root %', clock_timestamp(), v_data_root;
-- Create our tables if required
IF EXISTS (SELECT 1 FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename = 'pc_opendata_types') THEN
RAISE NOTICE 'Table "public"."pc_opendata_types" already exists';
ELSE
RAISE NOTICE 'Creating table "public"."pc_opendata_types"';
CREATE TABLE public.pc_opendata_types
(
id bigserial NOT NULL PRIMARY KEY,
type character varying(3) NOT NULL UNIQUE,
description text NOT NULL
);
END IF;
IF EXISTS (SELECT 1 FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename = 'pc_opendata_areas') THEN
RAISE NOTICE 'Table "public"."pc_opendata_areas" already exists';
ELSE
RAISE NOTICE 'Creating table "public"."pc_opendata_areas"';
CREATE TABLE public.pc_opendata_areas
(
id bigserial NOT NULL PRIMARY KEY,
code character varying(9) NOT NULL UNIQUE,
type character varying(3) NOT NULL REFERENCES public.pc_opendata_types (type),
description text NOT NULL
);
END IF;
IF EXISTS (SELECT 1 FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename = 'pc_opendata') THEN
RAISE NOTICE 'Table "public"."pc_opendata" already exists';
v_main_table_created := false;
ELSE
RAISE NOTICE 'Creating table "public"."pc_opendata"';
CREATE TABLE public.pc_opendata
(
id bigserial NOT NULL PRIMARY KEY,
postcode character varying(7) NOT NULL,
easting integer NOT NULL,
northing integer NOT NULL,
latitude double precision NOT NULL,
longitude double precision NOT NULL,
country_code character varying(9) REFERENCES public.pc_opendata_areas (code),
admin_county_code character varying(9) REFERENCES public.pc_opendata_areas (code),
admin_district_code character varying(9) REFERENCES public.pc_opendata_areas (code),
admin_ward_code character varying(9) REFERENCES public.pc_opendata_areas (code)
);
v_main_table_created := true;
END IF;
-- Import data
RAISE NOTICE '% : Importing "public"."pc_opendata_types"', clock_timestamp();
v_sql := 'COPY public.pc_opendata_types (type, description) FROM ' || quote_literal(v_data_areas || 'Codelist_AREA_CODES.csv') || ' CSV';
EXECUTE v_sql;
-- Insert missing Country record
INSERT INTO public.pc_opendata_types (type, description) VALUES ('CNY', 'Country');
RAISE NOTICE '% : Importing "public"."pc_opendata_areas"', clock_timestamp();
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_CTY.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIS.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIW.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBO.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBW.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTD.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTW.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTA.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTE.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTW.csv') || ' CSV';
EXECUTE v_sql;
-- Insert missing Counties/Countries and missing Scilly Isles
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('W92000004', 'CNY', 'Wales');
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('E92000001', 'CNY', 'England');
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('S92000003', 'CNY', 'Scotland');
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('E05008322', 'DIW', 'Bryher Ward');
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('E05008323', 'DIW', 'St. Agnes Ward');
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('E05008324', 'DIW', E'St. Martin\'s Ward');
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('E05008325', 'DIW', E'St. Mary\'s Ward');
INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('E05008326', 'DIW', 'Tresco Ward');
-- Finally our postcode data
RAISE NOTICE '% : Importing "public"."pc_opendata"', clock_timestamp();
v_sql := 'COPY public.pc_opendata (postcode,easting,northing,latitude,longitude,country_code,admin_county_code,admin_district_code,admin_ward_code) FROM '
|| quote_literal(v_data_main || 'all_areas_20140324.csv') || ' CSV HEADER';
EXECUTE v_sql;
if (v_main_table_created) THEN
CREATE UNIQUE INDEX pc_opendata_postcode ON public.pc_opendata USING btree (postcode);
CREATE INDEX pc_opendata_latitude ON public.pc_opendata USING btree (latitude);
CREATE INDEX pc_opendata_longitude ON public.pc_opendata USING btree (longitude);
END IF;
RAISE NOTICE '%: Completed', clock_timestamp();
RETURN true;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
DROP FUNCTION IF EXISTS public.update_pc_opendata(varchar);
CREATE OR REPLACE FUNCTION public.update_pc_opendata(in_data varchar)
RETURNS boolean AS
$BODY$
DECLARE
v_data_root varchar;
v_data_main varchar;
v_data_areas varchar;
v_sql text;
BEGIN
v_data_root := in_data || '/';
v_data_main := v_data_root || 'Data/';
v_data_areas := v_data_root || 'Doc/';
RAISE NOTICE '%: Import starting with data root %', clock_timestamp(), v_data_root;
RAISE NOTICE 'Creating temp table "tmp_pc_opendata_types"';
CREATE TEMPORARY TABLE tmp_pc_opendata_types
(
id bigserial NOT NULL PRIMARY KEY,
type character varying(3) NOT NULL UNIQUE,
description text NOT NULL
);
RAISE NOTICE 'Creating temp table "tmp_pc_opendata_areas"';
CREATE TEMPORARY TABLE tmp_pc_opendata_areas
(
id bigserial NOT NULL PRIMARY KEY,
code character varying(9) NOT NULL UNIQUE,
type character varying(3) NOT NULL REFERENCES tmp_pc_opendata_types (type),
description text NOT NULL
);
RAISE NOTICE 'Creating temp table "tmp_pc_opendata"';
CREATE TEMPORARY TABLE tmp_pc_opendata
(
id bigserial NOT NULL PRIMARY KEY,
postcode character varying(7) NOT NULL,
easting integer NOT NULL,
northing integer NOT NULL,
latitude double precision NOT NULL,
longitude double precision NOT NULL,
country_code character varying(9) REFERENCES tmp_pc_opendata_areas (code),
admin_county_code character varying(9) REFERENCES tmp_pc_opendata_areas (code),
admin_district_code character varying(9) REFERENCES tmp_pc_opendata_areas (code),
admin_ward_code character varying(9) REFERENCES tmp_pc_opendata_areas (code)
);
-- Import data
RAISE NOTICE '% : Importing "tmp_pc_opendata_types"', clock_timestamp();
v_sql := 'COPY tmp_pc_opendata_types (type, description) FROM ' || quote_literal(v_data_areas || 'Codelist_AREA_CODES.csv') || ' CSV';
EXECUTE v_sql;
-- Insert missing Country record
INSERT INTO tmp_pc_opendata_types (type, description) VALUES ('CNY', 'Country');
RAISE NOTICE '% : Importing "tmp_pc_opendata_areas"', clock_timestamp();
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_CTY.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIS.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIW.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBO.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBW.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTD.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTW.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTA.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTE.csv') || ' CSV';
EXECUTE v_sql;
v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTW.csv') || ' CSV';
EXECUTE v_sql;
-- Insert missing Counties/Countries and missing Scilly Isles
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('W92000004', 'CNY', 'Wales');
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('E92000001', 'CNY', 'England');
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('S92000003', 'CNY', 'Scotland');
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('E05008322', 'DIW', 'Bryher Ward');
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('E05008323', 'DIW', 'St. Agnes Ward');
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('E05008324', 'DIW', E'St. Martin\'s Ward');
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('E05008325', 'DIW', E'St. Mary\'s Ward');
INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('E05008326', 'DIW', 'Tresco Ward');
-- Finally our postcode data
RAISE NOTICE '% : Importing "tmp_pc_opendata"', clock_timestamp();
v_sql := 'COPY tmp_pc_opendata (postcode,easting,northing,latitude,longitude,country_code,admin_county_code,admin_district_code,admin_ward_code) FROM '
|| quote_literal(v_data_main || 'all_areas_20140324.csv') || ' CSV HEADER';
EXECUTE v_sql;
-- Now do the upgrade with a truncate (if the locking is an issue then it's better to diff and update/delete/insert)
RAISE NOTICE '% : Switching the data via truncate & reload', clock_timestamp();
TRUNCATE TABLE public.pc_opendata RESTART IDENTITY;
TRUNCATE TABLE public.pc_opendata_areas RESTART IDENTITY CASCADE;
TRUNCATE TABLE public.pc_opendata_types RESTART IDENTITY CASCADE;
INSERT INTO public.pc_opendata_types (type, description)
SELECT type, description FROM tmp_pc_opendata_areas;
INSERT INTO public.pc_opendata_areas (code, type, description)
SELECT code, type, description FROM tmp_pc_opendata_areas;
INSERT INTO public.pc_opendata (postcode,easting,northing,latitude,longitude,country_code,admin_county_code,admin_district_code,admin_ward_code)
SELECT postcode,easting,northing,latitude,longitude,country_code,admin_county_code,admin_district_code,admin_ward_code
FROM tmp_pc_opendata;
RAISE NOTICE '%: Completed', clock_timestamp();
RETURN true;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;