Offline geocoding from a single country file — forward and reverse, no server, no network
Installing and Loading
INSTALL gridpin_ext FROM community;
LOAD gridpin_ext;
Example
-- Country data files are free downloads: https://gridpin.dev
-- (France, Italy, the Netherlands, Serbia, Germany, Spain and Brazil today; one file per country.)
LOAD gridpin_ext;
-- Point the extension at a downloaded sheet. One sheet is loaded at a time.
SELECT gridpin_load('./france.bin');
-- Forward geocoding: an address string in, a JSON object out.
SELECT gridpin_geocode('10 rue de rivoli paris');
-- Reverse geocoding: coordinates in, the nearest address out.
SELECT gridpin_reverse(48.8686, 2.3305);
-- It is an ordinary scalar function, so a whole table geocodes in one query.
SELECT id,
json_extract(gridpin_geocode(address), '$.lat') AS lat,
json_extract(gridpin_geocode(address), '$.lon') AS lon
FROM customers;
About gridpin_ext
GridPin resolves addresses to coordinates and back entirely offline: one memory-mapped file per country, millisecond lookups on a single core, and typo tolerance built into the index. There is no server to run, no database to import, no API key and no per-request fee — the addresses you geocode never leave the machine.
Functions
| Function | Purpose |
|---|---|
gridpin_load(path) |
memory-map a country sheet; the last path wins |
gridpin_geocode(address) |
address string → JSON object with lat, lon, street, city |
gridpin_reverse(lat, lon) |
coordinates → JSON object for the nearest address |
gridpin_load_poi(path) |
optional points-of-interest layer, consulted after addresses |
gridpin_reset() |
drop the loaded sheet so another country can be loaded |
Every function carries a description and parameter names, so SQL assistants and
AI agents can discover them through duckdb_functions().
A query issued before gridpin_load is a clear error rather than a crash, and an
address with no match returns {} — an empty result is a documented contract, not a
guess.
Data files
Country sheets are published as free downloads at gridpin.dev and in the releases of the main repository. Each sheet carries its own source licence (France comes from the national BAN registry under Licence Ouverte; Italy, the Netherlands, Serbia, Germany, Spain and Brazil are built from Overture, with OpenAddresses, CartoCiudad and IBGE CNEFE sources where the country publishes them). Germany covers 15 of 16 Länder (Bavaria is not covered). The engine itself is Apache-2.0 and lives at gridpin/gridpin; this repository holds the extension crate only.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| gridpin_geocode | scalar | Return the best address or POI match as JSON, or an empty JSON object when nothing matches. | NULL | [SELECT gridpin_geocode('boulevard des moulins 27 monaco')] |
| gridpin_load | scalar | Load an address index for this database, requiring an explicit reset before changing its path. | NULL | [SELECT gridpin_load('mc.bin')] |
| gridpin_load_poi | scalar | Load an optional POI index that must match the address index country. | NULL | [SELECT gridpin_load_poi('mc_poi.bin')] |
| gridpin_reset | scalar | Unload the address and POI indexes in a standalone statement before switching sheets. | NULL | [SELECT gridpin_reset()] |
| gridpin_reverse | scalar | Return the nearest indexed house as JSON for finite coordinates, or an empty JSON object when none is nearby. | NULL | [SELECT gridpin_reverse(43.7391, 7.4266)] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
This extension does not add any settings.