Parties

Parties

Local parties are often organised by parliamentary constituency. So it's useful to map upcoming local elections to parliamentary constituencies.

Notes

• This query takes a long time (hours).
• WHERE oo.divisionset_id = 25 should point at the latest parl divisionset
• WHERE ee.poll_open_date = '2022-05-05' should be the election date you're interested in.
• The area calcs will be in m^2
• WHERE overlap_area > 10000 This threshold is probably too small, but I don't know what is right, so and it doesn't make the result set too large. Figured it was easier to create filters in a spreadsheet, based on the calculated values.

WITH elections AS (
    SELECT ee.election_id, dg.geography, st_area(st_transform(dg.geography, 27700)) as electoral_division_area
    FROM elections_election ee
             JOIN organisations_divisiongeography dg
                  ON ee.division_geography_id = dg.id
    WHERE ee.poll_open_date = '2022-05-05'
      AND ee.group_type IS NULL
), elections_consitutencies AS (
    SELECT oo.official_identifier,
           oo.name,
           e.election_id,
           st_area(st_intersection(st_transform(e.geography, 27700), st_transform(odg.geography, 27700))) as overlap_area,
           e.electoral_division_area
    FROM elections e
             JOIN organisations_divisiongeography odg
                  ON e.geography && odg.geography
--                AND ST_RELATE(e.geography,odg.geography,'T********')
             JOIN organisations_organisationdivision oo on odg.division_id = oo.id
    WHERE oo.divisionset_id = 25
)
SELECT * FROM elections_consitutencies
WHERE overlap_area > 10000;