The city of Milton Keynes has been crashing late in the analysis and, worse still, creating a record in the database that crashes any AnalysisJob query that touches it.
It turns out the main source of the problem is that there's no population. The population file used by the failing jobs, https://pfb-public-documents.s3.amazonaws.com/population/tabblock2010_99_pophu.zip, seems to be missing a lot of population values. Loading it QGIS and looking at the data table, I can see it has 181,408 records, but when I filter for "POP10 = NULL" it shows there are 115,096 records that match. Some blocks without population might make sense, but almost 2/3rds doesn't. I didn't manage to get a sense of where the population is and where it's missing, and whether it's all-or-nothing across big areas or if they're interspersed, but when I click around the area of Milton Keynes I didn't find any non-null blocks.
So what's happening is that the population is getting imported, and having the geometries is enough to do all the stress and connectivity calculations, but then at the end when it tries to export the scores they're all zero, including total population. Zeroes in the other scores are allowed/expected, but for population, it turns out it will insert into the database OK, but then any query that tries to load the scores will fail a the database level, because it's looking for an integer value from the population key within the JSONB field, and getting an empty string instead.
Steps I think we should take to address this:
Do a validation query after we've loaded the census blocks and trimmed them to the buffered boundary. If the total population of the remaining census blocks is zero, we should raise an error immediately. This is fairly early in the analysis, and will both make it easier to see what went wrong and avoid wasting time running an analysis that's doomed to fail.
In load_overall_scores.py, don't let a non-integer population value get through. Right now there's an if ValueError: pass, which I think is there because some entries in the metrics table it's working with aren't numbers and that's fine (though it might be worth double-checking that). That shouldn't apply to the population total. If it's not an integer, the script should raise an exception.
It would be nice to also prevent a bad record that gets into the database from crashing the API when any query tries to interact with it, but I think it would be tricky to identify exactly those cases and do something useful--i.e. don't crash but also don't just swallow potentially serious errors silently. So I think it makes sense to focus on preventing bad records from getting into the database in the first place.
The city of Milton Keynes has been crashing late in the analysis and, worse still, creating a record in the database that crashes any AnalysisJob query that touches it.
It turns out the main source of the problem is that there's no population. The population file used by the failing jobs, https://pfb-public-documents.s3.amazonaws.com/population/tabblock2010_99_pophu.zip, seems to be missing a lot of population values. Loading it QGIS and looking at the data table, I can see it has 181,408 records, but when I filter for "POP10 = NULL" it shows there are 115,096 records that match. Some blocks without population might make sense, but almost 2/3rds doesn't. I didn't manage to get a sense of where the population is and where it's missing, and whether it's all-or-nothing across big areas or if they're interspersed, but when I click around the area of Milton Keynes I didn't find any non-null blocks.
So what's happening is that the population is getting imported, and having the geometries is enough to do all the stress and connectivity calculations, but then at the end when it tries to export the scores they're all zero, including total population. Zeroes in the other scores are allowed/expected, but for population, it turns out it will insert into the database OK, but then any query that tries to load the scores will fail a the database level, because it's looking for an integer value from the population key within the JSONB field, and getting an empty string instead.
Steps I think we should take to address this:
load_overall_scores.py, don't let a non-integer population value get through. Right now there's anif ValueError: pass, which I think is there because some entries in the metrics table it's working with aren't numbers and that's fine (though it might be worth double-checking that). That shouldn't apply to the population total. If it's not an integer, the script should raise an exception.It would be nice to also prevent a bad record that gets into the database from crashing the API when any query tries to interact with it, but I think it would be tricky to identify exactly those cases and do something useful--i.e. don't crash but also don't just swallow potentially serious errors silently. So I think it makes sense to focus on preventing bad records from getting into the database in the first place.