Materialized Views

pollingstations_residential_address_view

CREATE MATERIALIZED VIEW pollingstations_residential_address_view AS
SELECT
    a.address,
	a.postcode,
	u.lad,
	u.uprn,
	u.polling_station_id,
	a.location
FROM
    addressbase_address a
    JOIN addressbase_uprntocouncil u
        ON a.uprn = u.uprn
WHERE
    u.polling_station_id != '';

CREATE UNIQUE INDEX on pollingstations_residential_address_view(uprn);

pollingstations_lines_view

CREATE MATERIALIZED VIEW pollingstations_lines_view AS
SELECT 
    ab.uprn,
    ps.internal_council_id,
    ab.address,
    ab.postcode,
    st_makeline(ps.location, ab.location) AS geom
FROM 
    addressbase_address ab
    JOIN addressbase_uprntocouncil uc
        ON ab.uprn = uc.uprn
    JOIN councils_councilgeography cg
        ON uc.lad = cg.gss
    JOIN pollingstations_pollingstation ps
        ON (ps.internal_council_id = uc.polling_station_id
        AND ps.council_id = cg.council_id);
CREATE UNIQUE INDEX on pollingstations_lines_view(uprn);

councils_geo_view

CREATE MATERIALIZED VIEW councils_geo_view AS
SELECT c.council_id, c.name, cg.gss, cg.geography AS geom
FROM councils_council c
    JOIN councils_councilgeography cg
    ON c.council_id = cg.council_id

rough_districts_missing_stations_view

DROP MATERIALIZED VIEW IF EXISTS  rough_districts_missing_stations_view;
CREATE MATERIALIZED VIEW rough_districts_missing_stations_view AS
WITH concave_hulls as (
    SELECT uprn.polling_station_id,
           uprn.lad,
        ST_ConcaveHull(
            ST_Collect(
                aa.location), 0.99) As geom
    FROM
        addressbase_uprntocouncil uprn
        JOIN addressbase_address aa on uprn.uprn = aa.uprn
    WHERE uprn.polling_station_id != ''
    GROUP BY uprn.polling_station_id, uprn.lad
)
SELECT
    ps.address,
    ps.internal_council_id,
    ps.council_id,
    ST_CollectionExtract(ch.geom,3) as geom
FROM
    concave_hulls ch
    JOIN pollingstations_pollingstation ps
    ON ch.polling_station_id = ps.internal_council_id
    JOIN councils_councilgeography cg on ps.council_id = cg.council_id
WHERE
    NOT st_contains(ch.geom, ps.location)
    AND ch.lad = cg.gss;
CREATE UNIQUE INDEX on rough_districts_missing_stations_view(council_id, internal_council_id);

rough_districts_with_stations_view

DROP MATERIALIZED VIEW IF EXISTS  rough_districts_with_stations_view;
CREATE MATERIALIZED VIEW rough_districts_with_stations_view as
WITH concave_hulls as (
    SELECT uprn.polling_station_id,
           uprn.lad,
        ST_ConcaveHull(
            ST_Collect(
                aa.location), 0.99) As geom
    FROM
        addressbase_uprntocouncil uprn
        JOIN addressbase_address aa on uprn.uprn = aa.uprn
    WHERE uprn.polling_station_id != ''
    GROUP BY uprn.polling_station_id, uprn.lad
)
SELECT
	ROW_NUMBER() OVER() as id,
    ps.address,
    ps.internal_council_id,
    ST_CollectionExtract(st_concavehull(st_collect(ST_Collect(ch.geom), ps.location), 0.99),3) as geom
FROM
    concave_hulls ch
    JOIN pollingstations_pollingstation ps
    ON ch.polling_station_id = ps.internal_council_id
    JOIN councils_councilgeography cg on ps.council_id = cg.council_id
WHERE
    ch.lad = cg.gss
group by ps.address, ps.internal_council_id, ps.location;