Create seperate tables for CodePoint with Polygons

CREATE SEQUENCE codepointpolygons.codepointpolygons_discard_seq START WITH 6000000;
COMMIT;
 
CREATE TABLE codepointpolygons.discards_polys
(
  gid bigint,
  postcode character varying(8),
  upp character varying(20),
  pc_area text,
  geom geometry(MultiPolygon,27700),
  discard boolean,
  vstreet boolean,
  vstreet_and_std boolean
)
WITH (
  OIDS=FALSE
);
COMMIT;
 
INSERT INTO codepointpolygons.discards_polys SELECT 
  NEXTVAL('codepointpolygons.codepointpolygons_discard_seq') AS gid, 
  postcode,
  cast(NULL AS CHARACTER varying(20)) AS upp, 
  SUBSTRING(postcode FROM '^[A-Z][A-Z]?') AS pc_area, 
  cast(NULL AS geometry) AS geom, 
  TRUE AS DISCARD, 
  FALSE AS vstreet,
  FALSE AS vstreet_and_std
FROM codepointpolygons.discards;
COMMIT;
 
 
 
 
SET enable_seqscan = FALSE; 
COMMIT;
-- (otherwise pg sometimes fails to use the index)
 
CREATE SEQUENCE codepointpolygons.codepointpolygons_vstreet_seq START WITH 7000000;
COMMIT;
 
CREATE TABLE codepointpolygons.verticalstreets_polys
(
  gid bigint,
  postcode text,
  upp character varying(20),
  pc_area text,
  geom geometry(MultiPolygon,27700),
  discard boolean,
  vstreet boolean,
  vstreet_and_std boolean
)
WITH (
  OIDS=FALSE
);
COMMIT;
 
INSERT INTO codepointpolygons.verticalstreets_polys 
SELECT 
    NEXTVAL('codepointpolygons.codepointpolygons_vstreet_seq') AS gid, 
    MAX(v.postcode) AS postcode, 
    cast(NULL AS varchar(20)) AS upp, 
    MAX(pc_area) AS pc_area, 
    st_multi(st_union(geom)) AS geom, 
    FALSE AS DISCARD,
    TRUE AS vstreet,
    FALSE AS vstreet_and_std
  FROM codepointpolygons.verticalstreets v 
  LEFT JOIN codepointpolygons.codepointpolygons p 
  ON v.vstreet = p.postcode
  GROUP BY v.postcode;
COMMIT;
 
 
 
 
 
 
 
 
 
CREATE TABLE codepointpolygons.verticalstreets_polys AS (
  SELECT 
    NEXTVAL('codepointpolygons.codepointpolygons_vstreet_seq') AS gid, 
    MAX(v.postcode) AS postcode, 
    cast(NULL AS varchar(20)) AS upp, 
    MAX(pc_area) AS pc_area, 
    st_union(geom) AS geom, 
    FALSE AS DISCARD,
    TRUE AS vstreet,
    FALSE AS vstreet_and_std
  FROM codepointpolygons.verticalstreets v 
  LEFT JOIN codepointpolygons.codepointpolygons p 
  ON v.vstreet = p.postcode
  GROUP BY v.postcode
);
COMMIT;
 
 
CREATE SEQUENCE codepointpolygons.verticalstreets_and_std_seq START WITH 8000000;
COMMIT;
 
CREATE TABLE codepointpolygons.verticalstreets_and_std_polys AS (
SELECT 
  NEXTVAL('codepointpolygons.verticalstreets_and_std_seq') AS gid, 
  p.postcode AS postcode,
  cast(NULL AS varchar(20)) AS upp, 
  p.pc_area AS pc_area,
  st_multi(st_union(p.geom, v.geom)) AS geom, 
  FALSE AS DISCARD, 
  FALSE AS vstreet,
  TRUE AS vstreet_and_std
FROM codepointpolygons.codepointpolygons p
INNER JOIN codepointpolygons.verticalstreets_polys v
ON p.postcode = v.postcode
);
COMMIT;
 
CREATE SEQUENCE codepointpolygons.verticalstreets_and_std_seq START WITH 8000000;
COMMIT;
 
CREATE TABLE codepointpolygons.verticalstreets_and_std_polys
(
  gid bigint,
  postcode character varying(8),
  upp character varying(20),
  pc_area character varying(2),
  geom geometry(MultiPolygon,27700),
  discard boolean,
  vstreet boolean,
  vstreet_and_std boolean
)
WITH (
  OIDS=FALSE
);
COMMIT;
 
INSERT INTO codepointpolygons.verticalstreets_and_std_polys
SELECT 
  NEXTVAL('codepointpolygons.verticalstreets_and_std_seq') AS gid, 
  p.postcode AS postcode,
  cast(NULL AS varchar(20)) AS upp, 
  p.pc_area AS pc_area,
  st_multi(st_union(p.geom, v.geom)) AS geom, 
  FALSE AS DISCARD, 
  FALSE AS vstreet,
  TRUE AS vstreet_and_std
FROM codepointpolygons.codepointpolygons p
INNER JOIN codepointpolygons.verticalstreets_polys v
ON p.postcode = v.postcode
;
COMMIT;
 
 
 
 
 
 
ALTER TABLE codepointpolygons.codepointpolygons ADD COLUMN discard BOOLEAN;
COMMIT;
ALTER TABLE codepointpolygons.codepointpolygons ADD COLUMN vstreet BOOLEAN;
COMMIT;
ALTER TABLE codepointpolygons.codepointpolygons ADD COLUMN vstreet_and_std BOOLEAN;
COMMIT;
 
ALTER TABLE codepointpolygons.codepointpolygons ADD COLUMN style NUMERIC;
COMMIT;
 
 
CREATE INDEX codepointpolygons_discard_idx ON codepointpolygons.codepointpolygons(discard);
COMMIT;
CREATE INDEX codepointpolygons_vstreet_idx ON codepointpolygons.codepointpolygons(vstreet);
COMMIT;
CREATE INDEX codepointpolygons_vstreet_std_idx ON codepointpolygons.codepointpolygons(vstreet_and_std);
COMMIT;
 
 
UPDATE codepointpolygons.codepointpolygons SET style = '1' WHERE discard IS NULL AND vstreet IS NULL AND vstreet_and_std IS NULL;
COMMIT;
UPDATE codepointpolygons.codepointpolygons SET style = '2' WHERE discard = 'TRUE' AND vstreet = 'FALSE' AND vstreet_and_std = 'FALSE';
COMMIT;
UPDATE codepointpolygons.codepointpolygons SET style = '3' WHERE discard = 'FALSE' AND vstreet = 'TRUE' AND vstreet_and_std = 'FALSE';
COMMIT;
UPDATE codepointpolygons.codepointpolygons SET style = '4' WHERE discard = 'FALSE' AND vstreet = 'FALSE' AND vstreet_and_std = 'TRUE'; 
COMMIT;       
 
CREATE INDEX codepointpolygons_style_idx ON codepointpolygons.codepointpolygons(style);
COMMIT;
 
 
 
 
 
DELETE FROM  codepointpolygons.codepointpolygons  WHERE postcode IN (SELECT postcode FROM  codepointpolygons.verticalstreets_and_std_polys);
COMMIT;
-- the above could take around 25 mins
DELETE FROM  codepointpolygons.verticalstreets_polys WHERE postcode IN (SELECT postcode FROM  codepointpolygons.verticalstreets_and_std_polys);
COMMIT;
 
 
INSERT INTO  codepointpolygons.codepointpolygons  SELECT * FROM  codepointpolygons.discards_polys;
COMMIT;
INSERT INTO  codepointpolygons.codepointpolygons  SELECT * FROM  codepointpolygons.verticalstreets_polys;
COMMIT;
INSERT INTO  codepointpolygons.codepointpolygons  SELECT * FROM  codepointpolygons.verticalstreets_and_std_polys;
COMMIT;