How I made a Who's On First subset database

whosonfirst_sqlite_subset.md

How I made a Who's On First subset database

Using whosonfirst-data-latest.db from https://dist.whosonfirst.org/sqlite/ - technique inspired by Paul Ford: https://twitter.com/ftrain/status/957833410017361921

If you fire up the sqlite3 CLI against an existing database you can and then mount a brand new database using attach database - then you can create tables in that new database and populate them using a select statement, extracting data from JSON columns using the json_extract() function.

Having populated the new table, I also create a full-text index against the name column.

Here's an example query against the resulting database, using a wildcard to implement prefix autocomplete: https://whosonfirst-metadata-only.now.sh/whosonfirst-metadata-only-c11ebe3/whosonfirst?_search=san+fra%2A

whosonfirst $ sqlite3 whosonfirst-data-latest.db 
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> attach database 'whosonfirst-metadata-only.db' as meta;
sqlite> 
sqlite> create table meta.whosonfirst (
   ...>   id integer primary key,
   ...>   name text,
   ...>   placetype text,
   ...>   parent_id integer,
   ...>   country text,
   ...>   bbox text,
   ...>   area_square_m float
   ...> );
sqlite> 
sqlite> replace into meta.whosonfirst select 
   ...>   id,
   ...>   json_extract(body, '$.properties."wof:name"') as name,
   ...>   json_extract(body, '$.properties."wof:placetype"') as placetype,
   ...>   json_extract(body, '$.properties."wof:parent_id"') as parent_id,
   ...>   json_extract(body, '$.properties."wof:country"') as country,
   ...>   json_extract(body, '$.bbox') as bbox,
   ...>   json_extract(body, '$.properties."geom:area_square_m"') as area_square_m
   ...>   from geojson;
sqlite> 
sqlite> CREATE VIRTUAL TABLE meta.whosonfirst_fts USING FTS4 ("name", content="whosonfirst");
sqlite> INSERT INTO meta.whosonfirst_fts (rowid, name) select rowid, name from meta.whosonfirst;