Polling place ingest improvements

[keithamoss]

• [ ] Federal elections take a long time and it's all in one transaction - this would block folks from submitting stalls
• [ ] Optimise the slower "search by location" queries
  • [ ] e.g. We could add "State" as a qualifier to detect_facility_type/chance_of_sausage to cut the time a bit.
• [ ] Optimise migrate_noms
• [ ] The "find by distance" approach is flawed (as demonstrated by the query below that returns both a public school and a nearby community centre. Consider a more robust approach.
• [ ] Add an append mode for adding semi-official collections of booths? (e.g. Overseas/Interstate)
• [ ] Add a way to manually add a single booth?
• [ ] Optimise the handful of long-running steps in the process
• [ ] Geocoding enhancements: https://github.com/keithamoss/demsausage/issues/64
• [ ] Geocoding validation of polling place locations
• [ ] Check that polling places are within the borders of the postcode / state they claim to be in
• [ ] Add a rough difference report between the data the existed and the data that loaded (e.g. Based on presence/absence of name + premises + state). Just something rough for us to gauge what's changed.
• [ ] More control over what we do when we merge extras. e.g. Summing CCD doesn't make sense!

SELECT "app_pollingplaces"."id", "app_pollingplaces"."old_id", "app_pollingplaces"."election_id", "app_pollingplaces"."noms_id", "app_pollingplaces"."geom"::bytea, "app_pollingplaces"."name", "app_pollingplaces"."facility_type_id", "app_pollingplaces"."premises", "app_pollingplaces"."address", "app_pollingplaces"."divisions", "app_pollingplaces"."state", "app_pollingplaces"."wheelchair_access", "app_pollingplaces"."entrance_desc", "app_pollingplaces"."opening_hours", "app_pollingplaces"."booth_info", "app_pollingplaces"."status", "app_pollingplaces"."chance_of_sausage", "app_pollingplaces"."extras", "app_pollingplaces"."ec_id", ST_Distance("app_pollingplaces"."geom", ST_GeogFromWKB('\x0101000020e610000062105839b4e46240c286a757caba40c0'::bytea)) AS "distance" FROM "demsausage"."app_pollingplaces" WHERE ("app_pollingplaces"."status" = 'Active' AND NOT ("app_pollingplaces"."election_id" = 27) AND ST_Distance("app_pollingplaces"."geom", ST_GeogFromWKB('\x0101000020e610000062105839b4e46240c286a757caba40c0'::bytea)) <= 200.0) ORDER BY "app_pollingplaces"."election_id" ASC;

Resources

Django

https://docs.djangoproject.com/en/2.2/topics/db/transactions/ https://docs.djangoproject.com/en/2.2/ref/databases/ (Performance and optimisation)

PostGIS

https://gis.stackexchange.com/questions/14232/using-a-geodjango-pointfield-with-geography-true-my-distance-calculations-are-w https://web.archive.org/web/20180204152904/http://workshops.boundlessgeo.com/postgis-intro/geography.html

AB#41

[keithamoss]

Closing and merging into https://github.com/keithamoss/demsausage/issues/1339