Canonical key to identify locations

jzohrab commented 4 years ago

Original issue https://github.com/covidatlas/coronadatascraper/issues/1051, transferred here on Wednesday Jun 10, 2020 at 23:47 GMT

I'm looking at locations.json and am trying to figure out what to use as a primary key to canonically identify locations in my consumer library.

It seems like the name field serves as a primary key in practice, but I'm worried it might change because its's a human-readable, localized name.

I was hoping I could use featureId as a key – it's particularly useful because I can easily obtain names in many language for each location. But 355 out of 4414 locations don't have a featureId:

Locations with missing featureId

I noticed that there is a bunch of logic in src/events/processor/find-features/index.js to generate featureId fields, so I'm assuming this could possibly be fixed to cover 100% of locations.

Finally, I tried using [countryId, stateId, countyId] triplets, but they don't uniquely identify locations either:

```js > require('./locations.json').length 4414 > new Set(require('./locations.json').map((l) => `${l.countryId},${l.stateId},${l.countyId}`)).size 4372 ```

Any thoughts?

jzohrab commented 4 years ago

(Transferred comment)

Hi @joliss - hm, the triplets should actually be correct, b/c together they should identify the location. Moreover, in Li, we're still using those triplets, so they'll likely be the best ID. Can you let me know what triplets have dups, and perhaps give me some more detail about them?

jzohrab commented 4 years ago

(Transferred comment)

Sure, here's the duplicates:

triplets.js

```js let locations = require('./locations.json') function getKey({ countryId, stateId, countyId }) { return `${countryId || ''},${stateId || ''},${countyId || ''}` } function summarizeLocation(loc) { return `[${loc.level}] ${loc.name}` } let locationsByTriplet = new Map for (let location of locations) { let key = getKey(location) if (!locationsByTriplet.has(key)) { locationsByTriplet.set(key, []) } locationsByTriplet.get(key).push(location) } let duplicateTriplets = {} for (let [key, locs] of locationsByTriplet) { if (locs.length > 1) { duplicateTriplets[key] = locs.map(summarizeLocation) } } console.log(JSON.stringify(duplicateTriplets, null, 2)) ```

Output:

{
  "iso1:US,iso2:US-AK,fips:02020": [
    "[county] Anchorage Municipality, Alaska, United States",
    "[county] Anchorage Economic Region, AK, USA"
  ],
  "iso1:US,iso2:US-AK,fips:02170": [
    "[county] Matanuska-Susitna Borough, Alaska, United States",
    "[county] Mat-Su Economic Region, AK, USA"
  ],
  "iso1:US,iso2:US-MI,fips:26163": [
    "[county] Wayne County, Michigan, United States",
    "[city] Detroit City, Wayne County, Michigan, United States"
  ],
  "iso1:US,iso2:US-RI,fips:44001": [
    "[county] Bristol County, Rhode Island, United States",
    "[city] Barrington, Bristol County, Rhode Island, United States",
    "[city] Bristol, Bristol County, Rhode Island, United States",
    "[city] Warren, Bristol County, Rhode Island, United States"
  ],
  "iso1:US,iso2:US-RI,fips:44003": [
    "[county] Kent County, Rhode Island, United States",
    "[city] Coventry, Kent County, Rhode Island, United States",
    "[city] East Greenwich, Kent County, Rhode Island, United States",
    "[city] Warwick, Kent County, Rhode Island, United States",
    "[city] West Greenwich, Kent County, Rhode Island, United States",
    "[city] West Warwick, Kent County, Rhode Island, United States"
  ],
  "iso1:US,iso2:US-RI,fips:44005": [
    "[county] Newport County, Rhode Island, United States",
    "[city] Jamestown, Newport County, Rhode Island, United States",
    "[city] Little Compton, Newport County, Rhode Island, United States",
    "[city] Middletown, Newport County, Rhode Island, United States",
    "[city] Newport, Newport County, Rhode Island, United States",
    "[city] Portsmouth, Newport County, Rhode Island, United States",
    "[city] Tiverton, Newport County, Rhode Island, United States"
  ],
  "iso1:US,iso2:US-RI,fips:44007": [
    "[county] Providence County, Rhode Island, United States",
    "[city] Burrillville, Providence County, Rhode Island, United States",
    "[city] Central Falls, Providence County, Rhode Island, United States",
    "[city] Cranston, Providence County, Rhode Island, United States",
    "[city] Cumberland, Providence County, Rhode Island, United States",
    "[city] East Providence, Providence County, Rhode Island, United States",
    "[city] Foster, Providence County, Rhode Island, United States",
    "[city] Glocester, Providence County, Rhode Island, United States",
    "[city] Johnston, Providence County, Rhode Island, United States",
    "[city] Lincoln, Providence County, Rhode Island, United States",
    "[city] North Providence, Providence County, Rhode Island, United States",
    "[city] North Smithfield, Providence County, Rhode Island, United States",
    "[city] Pawtucket, Providence County, Rhode Island, United States",
    "[city] Providence, Providence County, Rhode Island, United States",
    "[city] Scituate, Providence County, Rhode Island, United States",
    "[city] Smithfield, Providence County, Rhode Island, United States",
    "[city] Woonsocket, Providence County, Rhode Island, United States"
  ],
  "iso1:US,iso2:US-RI,fips:44009": [
    "[county] Washington County, Rhode Island, United States",
    "[city] Charlestown, Washington County, Rhode Island, United States",
    "[city] Exeter, Washington County, Rhode Island, United States",
    "[city] Hopkinton, Washington County, Rhode Island, United States",
    "[city] Narragansett, Washington County, Rhode Island, United States",
    "[city] New Shoreham, Washington County, Rhode Island, United States",
    "[city] North Kingstown, Washington County, Rhode Island, United States",
    "[city] Richmond, Washington County, Rhode Island, United States",
    "[city] South Kingstown, Washington County, Rhode Island, United States",
    "[city] Westerly, Washington County, Rhode Island, United States"
  ]
}

So maybe we need to add some kind of cityId. For example, Detroit City has a city field, but no cityId to key on:

    {
      "state": "Michigan",
      "country": "United States",
      "sources": [
        {
          "name": "Michigan Department of Health & Human Services"
        }
      ],
      "url": "https://www.michigan.gov/coronavirus/0,9753,7-406-98163-520743--,00.html",
      "aggregate": "county",
      "city": "Detroit City",
      "county": "Wayne County",
      "rating": 0.5098039215686274,
      "countryId": "iso1:US",
      "stateId": "iso2:US-MI",
      "countyId": "fips:26163",
      "name": "Detroit City, Wayne County, Michigan, United States",
      "level": "city"
    }

jzohrab commented 4 years ago

(Transferred comment)

Thanks very much, this is great.

If I recall correctly, the Alaska ones are tricky ... they changed things around with their reporting, using some strange region codes.

For the others which have city, do you know if the city values roll up to the state values, or are contained in them? If so, perhaps it would be best to ignore level: city data points. We don't have many of those, and don't have good geo data for them, iirc.

jzohrab commented 4 years ago

(Transferred comment)

For US counties, there should be exactly one county per fips code, it should identify uniquely. Here is the US Census reference document for fips codes <> names: https://www2.census.gov/programs-surveys/popest/geographies/2018/all-geocodes-v2018.xlsx

For cities, I believe they need to be 4-tuples, including city name or city slug of some sort.

So for example "North Kingstown, Washington County, Rhode Island, United States" would be iso1:US,iso2:US-RI,fips:44009,north_kingstown, but that is just my idea. They need 4-tuples for sure.

jzohrab commented 4 years ago

(Transferred comment)

I just looked at the Excel table you linked; it seems that the unique FIPS county code is the 2nd + 3rd column combined, correct?

I was hoping there might be some way to get unique numeric city identifiers, perhaps from the "Consolidtated City Code (FIPS)"[sic] or "Place Code (FIPS)" columns, but at the very least they'd need to be used in conjunction with other columns to be unique. For example here are all lines containing "03220":

Summary Level,State Code (FIPS),County Code (FIPS),County Subdivision Code (FIPS),Place Code (FIPS),Consolidtated City Code (FIPS),Area Name (including legal/statistical area description)
162,01,000,00000,03220,00000,Autaugaville town
162,02,000,00000,03220,00000,Anderson city
061,17,097,03220,00000,00000,Avon township
061,26,157,03220,00000,00000,Arbela township
061,33,001,03220,00000,00000,Barnstead town
061,38,053,03220,00000,00000,Arnegard city
162,38,000,00000,03220,00000,Arnegard city
061,46,085,03220,00000,00000,Bailey township

Or maybe you've looked into this already and it's just impossible?

jzohrab commented 4 years ago

(Transferred comment)

No, I haven't looked into cities and I don't know anything about them. The 5 digit fips code is a standard, look at Wikipedia or anywhere else, they are used in many places. Even the NYT and JHU datasources use the 5 digit one. For cities, I have no idea but Wikipedia lists something for cities.

jzohrab commented 4 years ago

(Transferred comment)

Thanks @hyperknot for jumping in.

@joliss - unless you specifically need city-level data, see if you can roll them up, or use only the state-level data. We don't have reliable/useful city-level data, and most scrapers/sources don't report on that. Can you give that a shot and LMK how it works out?

jzohrab commented 4 years ago

Dup of #479.

covidatlas / li

Canonical key to identify locations #467