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