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