1 DROP FUNCTION IF EXISTS public.import_pc_opendata(varchar);
2 CREATE OR REPLACE FUNCTION public.import_pc_opendata(in_data varchar)
10 v_main_table_created boolean;
14 v_data_root := in_data || '/';
15 v_data_main := v_data_root || 'Data/';
16 v_data_areas := v_data_root || 'Doc/';
18 RAISE NOTICE '%: Import starting with data root %', clock_timestamp(), v_data_root;
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';
24 RAISE NOTICE 'Creating table "public"."pc_opendata_types"';
25 CREATE TABLE public.pc_opendata_types
27 id bigserial NOT NULL PRIMARY KEY,
28 type character varying(3) NOT NULL UNIQUE,
29 description text NOT NULL
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';
36 RAISE NOTICE 'Creating table "public"."pc_opendata_areas"';
37 CREATE TABLE public.pc_opendata_areas
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
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;
50 RAISE NOTICE 'Creating table "public"."pc_opendata"';
51 CREATE TABLE public.pc_opendata
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)
64 v_main_table_created := true;
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';
73 -- Insert missing Country record
74 INSERT INTO public.pc_opendata_types (type, description) VALUES ('CNY', 'Country');
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';
79 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIS.csv') || ' CSV';
81 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIW.csv') || ' CSV';
83 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBO.csv') || ' CSV';
85 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBW.csv') || ' CSV';
87 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTD.csv') || ' CSV';
89 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTW.csv') || ' CSV';
91 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTA.csv') || ' CSV';
93 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTE.csv') || ' CSV';
95 v_sql := 'COPY public.pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTW.csv') || ' CSV';
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');
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';
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);
120 RAISE NOTICE '%: Completed', clock_timestamp();
125 LANGUAGE 'plpgsql' VOLATILE;
129 DROP FUNCTION IF EXISTS public.update_pc_opendata(varchar);
130 CREATE OR REPLACE FUNCTION public.update_pc_opendata(in_data varchar)
137 v_data_areas varchar;
141 v_data_root := in_data || '/';
142 v_data_main := v_data_root || 'Data/';
143 v_data_areas := v_data_root || 'Doc/';
145 RAISE NOTICE '%: Import starting with data root %', clock_timestamp(), v_data_root;
147 RAISE NOTICE 'Creating temp table "tmp_pc_opendata_types"';
148 CREATE TEMPORARY TABLE tmp_pc_opendata_types
150 id bigserial NOT NULL PRIMARY KEY,
151 type character varying(3) NOT NULL UNIQUE,
152 description text NOT NULL
155 RAISE NOTICE 'Creating temp table "tmp_pc_opendata_areas"';
156 CREATE TEMPORARY TABLE tmp_pc_opendata_areas
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
164 RAISE NOTICE 'Creating temp table "tmp_pc_opendata"';
165 CREATE TEMPORARY TABLE tmp_pc_opendata
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)
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';
184 -- Insert missing Country record
185 INSERT INTO tmp_pc_opendata_types (type, description) VALUES ('CNY', 'Country');
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';
190 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIS.csv') || ' CSV';
192 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_DIW.csv') || ' CSV';
194 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBO.csv') || ' CSV';
196 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_LBW.csv') || ' CSV';
198 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTD.csv') || ' CSV';
200 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_MTW.csv') || ' CSV';
202 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTA.csv') || ' CSV';
204 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTE.csv') || ' CSV';
206 v_sql := 'COPY tmp_pc_opendata_areas (description, code, type) FROM ' || quote_literal(v_data_areas || 'Codelist_UTW.csv') || ' CSV';
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');
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';
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;
231 INSERT INTO public.pc_opendata_types (type, description)
232 SELECT type, description FROM tmp_pc_opendata_areas;
234 INSERT INTO public.pc_opendata_areas (code, type, description)
235 SELECT code, type, description FROM tmp_pc_opendata_areas;
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;
241 RAISE NOTICE '%: Completed', clock_timestamp();
246 LANGUAGE 'plpgsql' VOLATILE;