Voters to districts
The following example queries should help you match voters to the districts that they are in. The first query covers matching a single voter with the districts they have. The second query matches a random ten voters to their districts.
In these queries, we have narrowed to most commonplace offices. If you remove that, you'll get more unique offices as well as statewide, citywide, and countywide offices. We opted for a long view over a wide view since many jurisdictions have overlapping local districts, such as at-large districts and "super districts" that overlap ordinary districts.
A voter w/districts
WITH
voter AS (
SELECT
id_vb AS targetsmart_voterbase_id,
ST_GEOGPOINT(longitude, latitude) AS voter_coordinates
FROM
`hubble_voter.deck_person`
WHERE
id_vb = "GA-000004954055"),
districts AS (
SELECT
geo_id AS ballotready_geo_id,
position_name,
normalized_position_name,
district_name,
parent_jurisdiction,
geometry AS district_shape
FROM
`hubble_geography_plus.source_district_ballotready`
WHERE
normalized_position_name IN ("City Legislature",
"County Legislature//Executive Board",
"Local School Board",
"State Representative",
"State Senator",
"U.S. Representative"))
SELECT
*
FROM
voter,
districts
WHERE
ST_INTERSECTS(voter_coordinates, district_shape)
ORDER BY
position_name,
district_name
random 10 w/districts
WITH
voter AS (
SELECT
id_vb AS targetsmart_voterbase_id,
ST_GEOGPOINT(longitude, latitude) AS voter_coordinates
FROM
`hubble_voter.deck_person` TABLESAMPLE SYSTEM (1 PERCENT)
LIMIT
10),
districts AS (
SELECT
geo_id AS ballotready_geo_id,
position_name,
normalized_position_name,
district_name,
parent_jurisdiction,
geometry AS district_shape
FROM
`hubble_geography_plus.source_district_ballotready`
WHERE
normalized_position_name IN ("City Legislature",
"County Legislature//Executive Board",
"Local School Board",
"State Representative",
"State Senator",
"U.S. Representative"))
SELECT
*
FROM
voter,
districts
WHERE
ST_INTERSECTS(voter_coordinates, district_shape)
ORDER BY
targetsmart_voterbase_id,
position_name,
district_name
Last modified 2mo ago