git.8kb.co.uk Git - postgresql/geographic_data/blob - 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', E'St. Martin\'s Ward');
 105         INSERT INTO public.pc_opendata_areas (code, type, description) VALUES ('E05008325', 'DIW', E'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', E'St. Martin\'s Ward');
 216         INSERT INTO tmp_pc_opendata_areas (code, type, description) VALUES ('E05008325', 'DIW', E'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;