Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
How I made a Who's On First subset database

How I made a Who's On First subset database

Using whosonfirst-data-latest.db from - technique inspired by Paul Ford:

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:

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> create table meta.whosonfirst (
   ...>   id integer primary key,
   ...>   name text,
   ...>   placetype text,
   ...>   parent_id integer,
   ...>   country text,
   ...>   bbox text,
   ...>   area_square_m float
   ...> );
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> 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.