none
Need help getting a list of AdminDivision2 and/or adminDistrict2 lookup values for the United Kingdom RRS feed

  • Question

  • Background

    Newer versions of Microsoft Excel provide a map chart.  Under the hood, Excel calls the Bing Maps REST API to lookup the chart series values to geocode the location and display the relevant map to the user.  This Microsoft article explains how it works in more detail https://support.office.com/en-gb/article/create-a-map-chart-in-excel-f2cfed55-d622-42cd-8ec9-ec8a358b593b. 

    Under the FAQ section it discusses the concept of ‘disambiguation’ in order to help provide additional context so that the correct locations are returned.  This works well and solves problems where multiple countries might have the same county name.

    Problem

    There is a second complication that the information provided by the user in the Excel data might not match the data held in the Bing Maps databases.  This results in maps where some areas will be found and shaded according to the chart value but others that are greyed out as they do not match.  This article explains in more detail the problem http://www.patrickcarmichael.co.uk/excel-map-charts-with-uk-data-sets/.  Since this article was published the problem seems to be worse as the map boundaries are now more granular and end up having more areas that are missed. 

    Whilst investigating the issue I decided to try and manually fix some of the areas.  Looking at the following image of South East England there are two grey area between Hampshire (20) and the Isle of Wight (23).  In this case they relate to Southampton and Portsmouth.  According to Wikipedia ‘The port cities of Southampton and Portsmouth were split off as independent unitary authorities in 1997, although they are still included in Hampshire for ceremonial purposes. This is probably why they haven’t been included in Hampshire and have their own boundaries.


    Adding separate entries for Southampton and Portsmouth in the Excel chart data fixes the issue and the areas are highlighted based on the associated value.  Using Fiddler I can see that Excel is calling the Bing maps API and passing the following: dev.virtualearth.net/REST/v1/Locations?adminDistrict=Southampton%2C%20England -> etc…

    The Bing maps API returns the location data including the following attributes:  

    address.adminDistrict2 = Southampton

    entityType=AdminDivision2

    I tested this on some of the other counties that are already shaded and they work in the same way.

    Help Needed

    Based on the above if a list of the AdminDivision2 entity types for the United Kingdom could be provided we could use these county/place names to get a full map for all the boundaries.  Ideally it would be great if Excel could somehow do this automatically, but I guess that would be a significant update. 

    Two questions:

    1. Is it possible to use the Bing Maps API to return a list of adminDistrict2 values for a given country?  I looked through the online API documentation but could not see a way to make this type of request.
    2. Alternatively can a request be made to the Microsoft GeoNames team and ask them to provide the list?

    The Microsoft Excel Map chart does attribute GeoNames so I did a quick check to use their list of admin divisions found here https://www.geonames.org/GB/administrative-division-united-kingdom.html but unfortunately their list did not match up either.

    Thanks


    Wednesday, June 3, 2020 9:12 PM

All replies

  • Hi James,

    My understanding is yes, you should be able to return a list of Admin2s but it would be a massive call to test all regions and with data updates potentially weekly this list can change so no, I am not aware of a "GeoNames" team that has a canonical list.  

    If you looked through other posts here you might have seen a recent deep dive into similar issues for Italy. For that I did a web search for Italian regions then pulled that list into Excel. I then highlighted that column and used the "Geography" type to see what Bing (That is Bing all up not Bing Maps specifically) recognized from that list of names. I then dug into the ones that were not found to see if it was a history or naming issue that might be at fault.

    With that in mind, what I can take from you is a list of locations/names/entities where you see an issue and escalate those to our Data team for deeper investigation.  It would be great if you do have the details to classify them as to the issue you see such as "Shape seems wrong" or "Entitiy missing" or other data error class you may see.

    Sincerely,

    IoTGirl

    Thursday, June 4, 2020 9:00 PM
    Owner
  • Hi IoTGirl,

    Thanks for your update.  I spent some more time over the weekend experimenting with different UK locations, with mixed results.

    The frustrating thing is the way the excel map chart seems to interpret the results from Bing maps in not consistent.  For instance sometimes it will show a partial map of the UK, other times only one area will be shaded, and sometimes other external geographies are picked up.  This also happens when the location names have been converted to geographies and any ‘?’ locations have been manually associated with the correct UK geography.

    I think the ideal solution would be to base the UK boundary information on the ONS (UK Office for National Statistics) data.  The latest dataset can be found here

    https://geoportal.statistics.gov.uk/datasets/counties-and-unitary-authorities-december-2019-boundaries-uk-bfc.  The link contains a spreadsheet with the various boundary names and associated KML/shapefiles.   I think the dataset is open and free to use for commercial use but would need attribution.  If you could pass this on to the relevant data team at Bing maps possibly they could have a look and see if this is something that could be investigated.

    Many thanks

    Sunday, June 7, 2020 2:48 PM
  • Hi James,

    There was just a large re-computation of Admin regions so can you let me know if you have seen any change in this behavior?

    If you have tried a canonical list and are seeing failure, you can provide it to me here and I can review it to see if I can see the issues you are seeing.

    Sincerely,

    IoTGirl

    Thursday, June 11, 2020 1:10 AM
    Owner