Recall Petitions

[GeoWill]

There have now been two of these so just going to put the notes about how the CSV that the Stitcher class in AggregatorAPI consumes is made.

The minimum requirement is to have all the addresses for every postcode that appears within the constituency having a recall petition. This is necessary so that:

• we know when to show an addresspicker if a postcode is split between polling districts, constituencies, and council areas.
• we can tell users to contact their council if we have no data for a postcode, but we know that those addresses should be eligible to vote in the recall petition.

The two recall petitions that we've covered (Rutherglen and Hamilton West, and Wellingborough) both were for constituencies which were contained by a council area. If there's a recall petition for a constituency which covers more than one council area, the query below will need amending.

The process I've followed has been to:

• Import the council export data locally as if it were for a 'normal' election.
• Run a query [1] to create a materialized view with a row per address for every address within every postcode that appears in the council area
• Do a spatial join in QGIS between the addresses materialized view and the westminster divisions from EE and attach the following attributes: organisationdivision__official_identifier, organisationdivision__name. (you'll need a materialized view of divisions/division geographies in EE to do this).

It would be possible to also filter the output based on postcodes that appear within the necessary constituency (nb simple spatial select isn't good enough - you need the addresses with the relevant postcodes that aren't in the constituency so you know the postcode is split). But haven't bothered as we can just check the division name in the aggregator api code.

[1] The SQL to create the materialized view:

DROP MATERIALIZED VIEW IF EXISTS wellingborough_addresses;

CREATE MATERIALIZED VIEW wellingborough_addresses AS
WITH nnt_postcodes AS (
--     Creates a list of all postcodes inside NNT.
    SELECT DISTINCT aa.postcode
    FROM addressbase_address aa JOIN addressbase_uprntocouncil au on aa.uprn = au.uprn
    JOIN councils_councilgeography cg on au.lad = cg.gss
    WHERE cg.council_id = 'NNT'
) SELECT
    aa.uprn,
    aa.address,
    aa.postcode,
    upper(replace(aa.postcode,' ','')) as postcode_ns,
    substring(aa.postcode,0,position(' ' IN aa.postcode)) as outcode,
    substring(aa.postcode,0,position(' ' IN aa.postcode)+2) as sector,
    cg.council_id,
    ps.internal_council_id as station_council_id, ps.address as station_address, ps.postcode as station_postcode,
    aa.location as geometry
FROM
    addressbase_address aa JOIN addressbase_uprntocouncil au ON aa.uprn = au.uprn
     JOIN councils_councilgeography cg ON au.lad = cg.gss
     LEFT JOIN pollingstations_pollingstation ps ON ps.internal_council_id = au.polling_station_id AND ps.council_id = cg.council_id
WHERE
--     Filter on postcodes which appear within NNT
    aa.postcode IN (SELECT postcode FROM nnt_postcodes);

CREATE UNIQUE INDEX on rutherglen_addresses(uprn);