Materialized Views

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,
    ch.geom
FROM
    concave_hulls ch
    JOIN pollingstations_pollingstation ps
    ON ch.polling_station_id = ps.internal_council_id
    JOIN councils_council cc on ps.council_id = cc.council_id
WHERE
    NOT st_contains(ch.geom, ps.location)
    AND ch.lad = ANY(cc.identifiers);

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 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