Explore adding geo codes and maps

[AnnCorp]

Brock Fanning in the US has been developing some proof of concepts for adding maps - see ticket https://github.com/GSA/sdg-indicators/issues/858 and proof of concept (using D3) http://brock.tips/sdg-indicators/8-1-1/. Keen to explore how much of this work we could reuse as is and what further developments would be needed to add maps? (Assuming add some kind of 'Maps' table to the side of charts and tables?) Two suggested indicators could use for proof of concept are 3.2.2 https://sustainabledevelopment-uk.github.io/3-2-2/ and 11.6.2 https://sustainabledevelopment-uk.github.io/11-6-2/ ONS Geography have confirmed it would be good practice for us to add the 9-digit geo codes to our data files. Need to discuss requirements in more detail with Mango team re what we could do and then breakdown into smaller github tickets. Some initial suggested steps: (1) will be for us to have test data files with the 9-digit GSS codes included - may alraedy have these for 3.2.2 and 11.6.2 (2) explore what changes needed re having these codes - presume would not want codes displayed in data filtering options, though would want them in the indicator .csv files (inlcude in filtered .csv or not?) (3) explore how display maps for these 2 indicators Chris Gale in ONS Geography would be able to provide advice on best practive etc with the maps and the geoportal is a good resource re boundaries etc http://geoportal.statistics.gov.uk/.

[AnnCorp]

More comments and examples added to Brock's issue on GSA github with country specific code now separated from general mapping code.

FYI some goeg stuff in http://healthexplorer.phila.gov/planning-districts/ - the original inspiration for the US SDGs platform

Incase of any use or inspiration, here's some examples from ONS Visual website too: https://visual.ons.gov.uk/uk-trade-partners/ https://visual.ons.gov.uk/house-prices-how-much-does-one-square-metre-cost-in-your-area/ https://visual.ons.gov.uk/migration-levels-what-do-you-know-about-your-area/ https://visual.ons.gov.uk/where-are-industry-eyes-on-brexit/ https://visual.ons.gov.uk/what-impact-could-lowering-the-uk-voting-age-to-16-have-on-the-shape-of-the-electorate/ https://visual.ons.gov.uk/five-facts-about-the-uk-service-sector/

[AnnCorp]

http://geoportal.statistics.gov.uk/ This is the where to find ONS Geography info on boundaries, maps, register of geographic codes etc

[AnnCorp]

@MaxEngledewAdmin please could you attach to this issue data files for 3.2.2 and 11.6.2 with geo codes added? Would the ones Graham did still be valid or have any other parts of the data for these indicators been updated more recently. Thank you!

[D-Cobbledick]

3.2.2 CSV GeoCode.xlsx 11.6.2 CSV GeoCode.xlsx

Hi Ann,

Geocodes for indicators 3.2.2 and 11.6.2 in files attached.

For 11.6.2, I've had to make some best fit assumptions which I've highlighted. These have been taken from my current list of GeoCodes in tab 2.

Please let me know if you need anything else,

Regards,

David

[D-Cobbledick]

3.2.2 CSV GeoCode.xlsx

• Corrected - now containing 'Year' column...

[dougmet]

Hi, this is really interesting. So we can have another protected column (so far we have Year, Units, and Value) called GeoCode which is not displayed on normal plots but does form the basis for a map.

We'd need:

1. Build step to make sure they are valid codes
2. A separate "map" tab
3. Able to join geocodes to boundary files
4. Think about how all of this interacts with the filtering

One good thing is that we don't need a separate data file because our format supports extra columns like this.

[brockfanning]

@dougmet I'd been wondering about item 4 also - how to interact with filtering. It seems like the current filtering could work in a map, if only they used radio buttons instead of checkboxes. Do you think maybe they could be converted to radio buttons only while users are on the Map tab?

[dougmet]

Yes it's like filtering except that you can only choose one thing. Could work changing to radio buttons. Or perhaps you have to choose one column from the currently selected, with an extra drop-down inside the map pane. So you filter to the ones you're interested in on the chart/table page and then choose them one at a time on the map.

[shaneporter]

Taking a look at that 3.2.2 spreadsheet, I see that the geocode for England and Wales is K04000001. There are different codes for other Geography fields as expected. However, there are a number of different geocodes, K02000001 to K02000024 inclusive, for rows with no Georgraphy field. What do these represent?

Also, there is a lot of repeated data here. Could we not specify a Geography -> Geocode mapping somewhere else? An additional piece of indicator metadata would specify whether there is a map (but couldn't we just do this automatically for everything with a Geography field?

Also, are the geographical regions limited to countries (or groups of countries, such as England and Wales)? May this change?

[shaneporter]

So, from call:

• Geography is sometimes limited to country (or England and Wales that I've also seen on 3.2.2 for example), but other indicators have/will have Geographies defined by other levels, such as region of UK.

[dougmet]

Link to ultra generalised LAD boundaries http://geoportal.statistics.gov.uk/datasets/local-authority-districts-december-2016-ultra-generalised-clipped-boundaries-in-great-britain

[Mengledew]

I think this is the only published indicator with LA breakdown at the moment: Automated Geo-coding - 4.2.1.xlsx. There will definitely be more coming onto the site relatively soon.

[dougmet]

Thanks Max. The file size (13.1Mb) is a bit of a warning that we may hit the limit of serving all of the data from a single CSV file if we have too many dissaggregations with geocode. My feeling is that in the first instance we breakdown by geocode OR other fields. Down the line when data is served via a service we could have as many breakdowns as we like.

[D-Cobbledick]

Automated Geo-coding - 4.2.1.csv.zip

CSV version of 4.2.1 geo-codes (hidden formula tabs removed). This size (7Mb) should be more representative

[shaneporter]

The CSV is converted to JSON and embedded in the HTML.

This amount of data will cripple the load time, and unless it's loaded on demand, isn't practical at all.

[dougmet]

Could we not just have one level of dissaggregation at this level of detail? Essentially Year, GeoCode. Then we can show just one map. Otherwise we're going to need to find a whole new way to serve the data.

[dougmet]

Well this is a bit embarrassing. I did some work to strip off the geocodes so that it only reports the headline. Turns out the file size is about the same as it is in the live site. So we're already at 7Mb (ish)!

Here's an ever-so-slightly smaller csv with the value column at the end.

small-4-2-1.zip

[shaneporter]

Thanks. I removed the NA text, and I was good to go.

My thinking for now is that I will detect the presence of a GeoCode column, and dynamically create the 'Map' tab.

At present, the API endpoint is fixed, but this can be changed quite easily when the time comes.

[dougmet]

Ah, yes that's a bad default. need to remember to set na="". Agree with detecting GeoCode column. Also agree at fixing the end point. Only other option is to use the metadata.

[AnnCorp]

@shaneporter @dougmet from discussions within SDG team and with ONS Geography suggest the following:

• keep a consistent UK map outline (not GB) for all the maps, as target coverage is UK
• for bits that are out of scope, make it transparent rather than white, so that there is a clear distinction between where areas are out of scope and where there is missing data
• use white for missing data but reduce the starkness of the white (240,240,240 rather than 255,255,255)
• where there is data it should always be a colour, not white so scale would start with palest green Once these bits are done and the scale labels are on etc then will send to Geography for them to review. If this is on the wrong ticket, please paste across to the individual tickets.Cheers!

[shaneporter]

Thanks for the feedback @AnnCorp. I will get onto the tweaks asap.

[shaneporter]

I think those first two points are covered in #2590.

[AnnCorp]

Looks great - happy to close this ticket. Would raise any future enhancements as separate tickets.