duckdb-geosupport

Try it in Google Colab

Open the step-by-step Colab notebook ↗ to download and load the prebuilt extension, install 26C, geocode a table, review Geosupport return codes and messages, and export CSV/Parquet results with provenance.

Choose a Python 3 CPU runtime and run the cells in order. Initial setup takes several minutes and several GiB of disk, including the approximately 195 MiB download and 1.9 GiB installed 26C bundle. The notebook downloads a checksum-verified release binary and installs runtime libraries only if missing; no compiler, Make, Git, Docker, or GitHub token is needed. Optional cells let you upload your own CSV and compare quarters.

Download your results before the temporary Colab runtime ends. Download the notebook · View on GitHub

1. Download and set up

Linux x86-64: download the extension

Download the v0.2.0 Linux x86-64 archive and compare its SHA-256 with the release checksums. Extract the archive to get geosupport.duckdb_extension. A GitHub account is not required.

Use DuckDB 1.4.4 for Linux x86-64. Unzip it and open the DuckDB interface from your terminal:

./duckdb -unsigned

In DuckDB, load the downloaded extension using its absolute path:

LOAD '/absolute/path/to/geosupport.duckdb_extension';

This binary was built on Ubuntu 22.04 and tested on Ubuntu 22.04 and 24.04 with DuckDB 1.4.4. It requires libcurl, OpenSSL 3, and the C++ runtime (libcurl.so.4, libcrypto.so.3, and libstdc++.so.6). Compilers and development headers are not needed. See the release notes and build provenance.

Mac with Apple Silicon: run the published container

Start Docker Desktop, then run the published 26c image to open an interactive DuckDB session:

docker run --rm -it --platform linux/amd64 \
  ghcr.io/sptkl/duckdb-geosupport:26c

Docker downloads the image on first use. It includes DuckDB, the loaded extension, and the installed 26C engine and data, with 26C selected as the default. Continue directly to the query below.

2. Run a query

If you downloaded the Linux extension, first run this setup in DuckDB. The install downloads approximately 195 MiB and uses 1.9 GiB on disk. Skip this setup when using the 26c container.

CALL gs_install('26c');

SET VARIABLE geosupport_release =
  (SELECT bundle_id FROM gs_load('26c'));

Run this query in either DuckDB session:

CREATE TEMP TABLE result AS
SELECT gs_address('125', 'Worth St', 'Mn') AS geo;

SELECT geo.bbl, geo.bin, geo.latitude, geo.longitude,
       geo.grc, geo.grc2
FROM result;

Sample output, captured from the 26C engine:

To select a release for an individual lookup, pass release := '26c'. Installed engine data is stored separately from your DuckDB database; the Linux download setup uses ~/.cache/duckdb-geosupport by default.

3. Examples

Geocode a table

CREATE TEMP TABLE addresses AS
SELECT * FROM (VALUES
  (1, '125',    'Worth St',          'Mn'),
  (2, '350',    '5 Ave',             'Mn'),
  (3, '30-30',  'Thomson Ave',       'QN'),
  (4, '999999', 'NOT A REAL STREET', 'Mn')
) AS t(id, house_number, street_name, borough);

CREATE TEMP TABLE geocoded AS
SELECT id, gs_address(house_number, street_name, borough,
                      release := '26c') AS geo
FROM addresses;

SELECT id, geo.bbl, geo.bin, geo.latitude, geo.longitude,
       geo.grc, geo.grc2
FROM geocoded ORDER BY id;

Keep house numbers and identifiers as strings. Rejected addresses remain rows with return codes and messages. Inspect both grc and grc2 for an address lookup.

SELECT id, geo.grc, geo.grc2, geo.message, geo.message2
FROM geocoded
WHERE geo.grc IS DISTINCT FROM '00'
   OR geo.grc2 IS DISTINCT FROM '00';

This filter flags anything other than two exact 00 codes for review; your acceptance policy may allow other codes.

Look up a tax lot or building

SELECT (gs_bbl('1001680032', release := '26c')).bbl AS bbl;
-- 1001680032

SELECT (gs_bin('1001831', release := '26c', mode := 'tpad')).bbl AS bbl;
-- 1001680032

Call a Geosupport function directly

SELECT gs_call('1E', to_json({
  'house_number': '125',
  'street_name': 'Worth St',
  'borough_code': 'Mn'
}), release := '26c', mode := 'extended') AS geo;

Every result includes raw, the full parsed output as JSON text. Use geo.raw::JSON to inspect function-specific fields.

Use another quarter

CALL gs_install('26b');
SELECT (gs_address('125', 'Worth St', 'Mn', release := '26b')).bbl AS bbl;
-- 1001680032

Save release, engine_version, and bundle_id with your results. An explicit release overrides the connection variable. Prepared statements capture that variable when prepared.

Examples use real captured engine output. Download the full sample results · Capture provenance

4. Function reference

Lookups

Accepted modes: regular, extended, long, tpad, long+tpad, extended+tpad. Output depends on the function and mode. gs_address does not implement a separate 1A/1E fallback.

Inputs must fit their fixed field widths and contain ASCII without NUL. Generic field values may be strings, integers, booleans, or NULL. Individual NULL fields become blanks. A NULL generic function, entire input value, or mode returns NULL when a release is selected.

Returned fields

Unavailable result fields are NULL. Not every function returns coordinates or both return codes.

Release management

These functions return release, bundle_id, engine_version, platform, and details (JSON text). Geocoding never installs missing data automatically.

5. Geosupport errors and return messages

Geosupport returns rejected addresses as result rows. Inspect grc and message, plus grc2 and message2 for address lookups. These fields preserve the NYC engine's return codes and messages.

Example: an unrecognized street

This query submits an address with an unrecognized street name and captures the returned error fields:

WITH rejected AS (
  SELECT gs_address('999999', 'NOT A REAL STREET', 'Mn',
                    release := '26c') AS geo
)
SELECT geo.grc, geo.message, geo.grc2, geo.message2,
       geo.bbl, geo.bin, geo.latitude, geo.longitude
FROM rejected;

Captured 26C result, displayed by field:

Here, both return codes are EE, and both messages report the unrecognized street and suggest a possible name. The BBL, BIN, and coordinates are NULL. The query completes and returns this row, so you can retain it for review alongside successful lookups.

For address results, check both return codes. The table example above filters rows where either code differs from 00 for review. Message text and suggestions depend on the input and quarterly release; keep the returned codes and messages with your results.