Some geographies are wrong

[symroe]

I've not tested this any more than the following:

We have local.exeter.heavitree.2018-05-03 as an election, and that election object has a geography associated (pk 13477 in our main EE database).

That geography is wrong:

election = Election.objects.get(election_id="local.exeter.heavitree.2018-05-03")
election.geography.geography.centroid.coords

Returns (-7.55726027231739, 49.76725898689887)

This results in a false negative in this URL:

https://elections.democracyclub.org.uk/api/elections/?postcode=EX1%202QT&current=true

[chris48s]

Oh good totally glad that's happened then.

There's quite a long rabbit hole to disappear down here, but here's 2 SQL statements that help to navigate this process:

SELECT * FROM organisations_divisiongeography WHERE NOT ST_ISVALID(geography);

SELECT *
FROM
    organisations_divisiongeography dg1 JOIN
    organisations_organisationdivision od ON od.id=dg1.division_id JOIN
    organisations_divisiongeography dg2 ON dg2.organisation_id=od.organisation_id
WHERE
    NOT ST_CONTAINS(dg2.geography, dg1.geography) AND
    NOT ST_OVERLAPS(dg2.geography, dg1.geography);

First one should be self-explanatory. Second one identifies valid geographies where the divisions don't sit inside the organisation boundaries they are supposed to be a child of. Looking at this, there are several classes of problem we need to solve there and there might be some false positives but there are definitely multiple problems that need to be solved there.. :(

silver linings:

1. Very few of the issues seem to be as a result of the recent updates. We should be able to sort out most of this using published sources.
2. That second SQL statement makes it look worse than it is ~600 of them can be fixed with one very obvious fix :)

fun times ahead

[chris48s]

I'm going to close this off and declare victory. Future work can be covered under #193 and #197.

For the benefit of FutureChris (that poor guy!), here's what I happened:

1. To fix geographies imported with the wrong SRID, I ran:

UPDATE organisations_divisiongeography
SET geography=ST_SetSRID(ST_Transform(geography, 27700), 4326)
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((-7.5573756131898335 49.76733007787106,-7.557160701192515 49.76733007787106,-7.557160701192515 49.76723003049607,-7.5573756131898335 49.76723003049607,-7.5573756131898335 49.76733007787106))', 4326),
    geography
);

(basically, find polygons contained by tiny box in the sea and switch the SRID to re-project them to the right place)

2. I just deleted the geography for UK parl. We'll move sorting that out to #197

3. To fix the invalid geographies, I ran:

UPDATE organisations_divisiongeography
SET geography=ST_MakeValid(geography)
WHERE NOT ST_IsValid(geography);

Hopefully that's the end of those problems. If it becomes necessary to know, the divisions affected by that are:

• BIR:sutton-reddicap
• BIR:sutton-walmley-and-minworth
• HRT:potters-bar-west-and-shenley
• unit_id:7366
• gss:E05010034
• unit_id:7413
• gss:E05010039
• HAO:misterton
• ELN:tranent-wallyford-and-macmerry

and the orgs:

• sp (Scottish Parliament)
• NIR-Y (Castlereagh)

The errors that it spat out were:

NOTICE:  Ring Self-intersection at or near point -1.7742294746239811 52.564615210399317
NOTICE:  Ring Self-intersection at or near point -1.8086258604459258 52.553143894598584
NOTICE:  Ring Self-intersection at or near point -0.30004405702017511 51.696519539377555
NOTICE:  Self-intersection at or near point -0.72936128740714734 52.318880768448693
NOTICE:  Self-intersection at or near point -0.72936128740714734 52.318880768448693
NOTICE:  Self-intersection at or near point -0.72936128740714734 52.318880768448693
NOTICE:  Self-intersection at or near point -0.72936128740714734 52.318880768448693
NOTICE:  Nested shells at or near point -2.871500286069796 59.269755354569163
NOTICE:  Ring Self-intersection at or near point -1.2360147726782689 52.435749436277121
NOTICE:  Ring Self-intersection at or near point -2.8698817699287797 55.961448809082718
NOTICE:  Self-intersection at or near point -5.863545050270023 54.581055517114173

If it all went horribly wrong, the backup taken just before I did all that is 2018-03-01-11-backup.dump