SQLite concordances table other_id column should be string (not int)

[nvkelso]

As these values often contain string values, and even FIPS which look like ints are actually strings where the leading 0 is significant. These are correctly string in the source, it's in the process of converting to SQLite that data loss happens.

For example: 02013 for Aleutians East with WOF ID = 102080625:

• https://spelunker.whosonfirst.org/id/102080625/

/cc @missinglink

[thisisaaronland]

This is a problem in the WOF code to generate SQLite tables:

https://github.com/whosonfirst/go-whosonfirst-sqlite-features/blob/main/tables/concordances.go

Can you file an issue there please?

[missinglink]

nice catch, we should change the column type.

weird thing is I tried to reproduce the bug from the hosted downloads and wasn't able to!? 🤷

sqlite3 whosonfirst-data-admin-us-latest.db 'SELECT body FROM geojson WHERE id = 102080625' \
    | jq '.properties."wof:concordances"'
{
  "fips:code": "02013",    <---- note: type:string and leading zero
  "gp:id": 12587554,
  "hasc:id": "US.AK.AE",
  "wd:id": "Q504371"
}

aria2c https://data.geocode.earth/wof/dist/sqlite/whosonfirst-data-admin-us-latest.db.bz2

lbunzip2 whosonfirst-data-admin-us-latest.db.bz2

sha256sum whosonfirst-data-admin-us-latest.db
c9b154263bfe2125131cdf49e31a90919836f6548949ac653e5dfd04a064a234  whosonfirst-data-admin-us-latest.db

@nvkelso where did you source the SQLite database?

[missinglink]

agh actually, scratch that, I wasn't looking in the concordances table 🙄

sqlite3 whosonfirst-data-admin-us-latest.db 'SELECT * FROM concordances WHERE id = 102080625'
102080625|2013|fips:code|1566609656
102080625|12587554|gp:id|1566609656
102080625|US.AK.AE|hasc:id|1566609656
102080625|Q504371|wd:id|1566609656