gridpin

Geocode inside SQLite and Datasette

When the addresses are already in a SQLite file, geocoding them should be a query, not an export, a script and an import.

New Geocode addresses SQL Research

What you have

The extension, built from the repository: integrations/sqlite/build.sh needs Rust and a C compiler and writes gridpin_sqlite.dylib (.so on Linux). A table and a query:

customers.sqlfile
CREATE TABLE customers (id INTEGER, address TEXT);
INSERT INTO customers VALUES
  (1, '233 S Wacker Dr, Chicago, IL 60606'),
  (2, '600 N Michigan Ave, Chicago, IL 60611'),
  (3, 'Nowhere Street 99999, Atlantis');
geocode.sqlfile
.load ./gridpin_sqlite
SELECT gridpin_open('usa.bin');
WITH g AS MATERIALIZED (SELECT id, gridpin_geocode(address) AS a FROM customers)
SELECT id, json_extract(a, '$.lat') AS lat, json_extract(a, '$.lon') AS lon,
       json_extract(a, '$.match_level') AS level, json_extract(a, '$.weak') AS weak
FROM g;

Steps

1. Create the table:

terminalCLI
$ sqlite3 customers.db < customers.sql

2. Load the extension, open the country file and geocode in SQL:

terminalCLI
$ sqlite3 -header -column customers.db < geocode.sql
gridpin_open('usa.bin')
-----------------------
index loaded: usa.bin  
id  lat         lon          level         weak
--  ----------  -----------  ------------  ----
1   41.878658   -87.6358684  PointAddress  0   
2   41.8929239  -87.6248765  PointAddress  0   
3

What you get

gridpin_lat and gridpin_lon give the point or NULL; gridpin_geocode the whole best answer as JSON ({} when there is none), to read with json_extract. In Datasette: datasette data.db --load-extension ./gridpin_sqlite, then the same functions in its SQL box (Datasette was not run for this page).

Why it works

The extension links the engine's C library into SQLite: each call is a lookup in the memory-mapped file opened by gridpin_open, once per connection.

Limits

  • The sqlite3 that comes with macOS cannot load extensions (no .load); use one from Homebrew or conda, or Python's sqlite3 with enable_load_extension(True).
  • Built from source for now: there are no prebuilt binaries in this release.
  • One country file per connection (SELECT gridpin_reset(); before opening another); a table function of several candidates is not in this version.
  • Each function call geocodes its value: the query above geocodes every row once, through a MATERIALIZED CTE; gridpin_lat(address), gridpin_lon(address) side by side geocode it twice.
  • weak = 1 marks an answer the engine itself says needs checking; keep such rows out or check them.

The function: SQLite and Datasette.

More