install-geocoding

name	install-geocoding
description	One-command installer for Snowflake-native geocoding (forward + reverse) built on the Overture Maps Marketplace share, with an optional libpostal SPCS service for international addresses. Deploys the GEOCODING.PUBLIC UDFs and stored procedures, auto-detecting whichever Overture Addresses share is installed. Idempotent and self-owning: detects-and-reuses existing objects. Use when: install geocoding, deploy geocoding, set up address geocoding, geocode addresses in Snowflake, reverse geocode lat/lon, build the geocoding service, deploy Overture geocoding. Do NOT use for: high-accuracy/routing-grade geocoding (use a partner solution) or changing the Overture data source. Triggers: install geocoding, deploy geocoding, set up geocoding, address geocoding, forward geocode, reverse geocode, geocode my addresses, Overture geocoding, libpostal, international geocoding.
metadata	{"author":"Snowflake","version":"1.0.0","category":"geospatial"}

Install Geocoding (Snowflake-native)

One command stands up forward + reverse geocoding entirely inside Snowflake, built on the Overture Maps Marketplace share — no external geocoding APIs. The installer is idempotent and self-owning: it deploys everything into GEOCODING.PUBLIC, detects whichever Overture Addresses share is present, and re-running detects-and-reuses existing objects.

Capability	Coverage	How
Forward (free-text address -> lat/lon)	US	usaddress parse + street standardization + Overture JOIN
Reverse (lat/lon -> nearest address)	worldwide	ST_DWITHIN + ST_DISTANCE nearest match
International forward (optional, --intl)	worldwide	libpostal on Snowpark Container Services

What gets created (GEOCODING.PUBLIC)

Object	Type	Signature
OVERTURE_ADDRESS	View	stable view over the resolved Overture share
PARSE_ADDRESS	Python UDF	(VARCHAR) -> OBJECT
STANDARDIZE_STREET	Python UDF	(VARCHAR) -> VARCHAR
FORWARD_GEOCODE_TABLE	SQL proc	(SOURCE_TABLE, ADDRESS_COLUMN, ID_COLUMN, OUTPUT_TABLE)
REVERSE_GEOCODE_TABLE	SQL proc	(SOURCE_TABLE, LAT_COLUMN, LON_COLUMN, ID_COLUMN, RADIUS_METERS, OUTPUT_TABLE, [COUNTRY])
IMAGE_REPOSITORY, GEOCODING_LIBPOSTAL_POOL, LIBPOSTAL_SVC, PARSE_ADDRESS_INTL, EXPAND_STREET_INTL	SPCS (only with --intl)	international parsing

Every object carries COMMENT = 'oss-geocoding' and installs under QUERY_TAG = 'oss-geocoding' so it can be discovered and cleaned up later.

Prerequisites

• Snowflake CLI (snow) with an active connection whose role can create databases from a listing (IMPORT SHARE / CREATE DATABASE), plus create schemas, functions, and procedures. Verify: snow connection list.
• The Overture Maps "Addresses" (Carto) Marketplace listing — the installer auto-acquires it (free, listing GZT0Z4CM1E9NQ) and reuses an existing copy under either OVERTURE_MAPS__ADDRESSES or DS_OVERTURE_MAPS__ADDRESSES. See references/overture-share.md.
• Only for --intl: Docker or Podman (amd64 image build) plus privileges to create an image repository, compute pool, and service. See references/libpostal-spcs.md.

External access integrations

None required. All geocoding runs on Snowflake-resident data plus the shared PyPI repository (usaddress, via snowflake.snowpark.pypi_shared_repository). The optional libpostal SPCS service bakes its ~2 GB models into the container image at build time and makes no outbound network calls, so it needs no external access integration or network rule.

Workflow

Step 1: Preflight

Goal: confirm the tools and connection. The Overture share is acquired automatically in Step 2, so no manual Marketplace step is needed up front.

1. Check the CLI and connection:

   snow --version
   snow connection list
   snow sql -c <connection> -q "SELECT CURRENT_ACCOUNT(), CURRENT_ROLE();"
   

2. (Optional) see whether an Overture Addresses share is already present (either name is fine; the installer also auto-acquires it):

   snow sql -c <connection> -q "SHOW DATABASES LIKE '%OVERTURE_MAPS__ADDRESSES%';"
   

Output: environment confirmed.

Step 2: Install (core: US forward + worldwide reverse)

Goal: acquire the Overture listing and deploy the GEOCODING.PUBLIC objects.

bash .cortex/skills/install-geocoding/scripts/install_geocoding.sh \
  --connection <connection> [--warehouse <wh>]

The orchestrator runs the SQL modules in order in a single tagged session: sql/00_setup.sql (DB/schema + auto-acquires the Overture Addresses listing GZT0Z4CM1E9NQ, then builds the OVERTURE_ADDRESS view + sanity check) -> sql/udfs/* -> sql/procedures/*. Idempotent (CREATE OR REPLACE / IF NOT EXISTS); safe to re-run.

If the listing can't be auto-acquired (e.g. not auto-fulfilled in your region), 00_setup.sql reports guidance and the sanity check fails — acquire "Overture Maps - Addresses" (Carto) from the Marketplace manually, then re-run.

Output: forward + reverse geocoding ready in GEOCODING.PUBLIC.

Step 3 (optional): International path with --intl

⚠️ MANDATORY STOPPING POINT — the --intl path builds and pushes a container image and creates an always-warm SPCS service (cost). Confirm with the user before running it.

Goal: deploy libpostal on SPCS for worldwide address parsing.

bash .cortex/skills/install-geocoding/scripts/install_geocoding.sh \
  --connection <connection> --intl

This ensures GEOCODING.PUBLIC.IMAGE_REPOSITORY, builds the libpostal image --platform linux/amd64 into the installing account's own repository, pushes it, then runs libpostal_service/deploy.sql (compute pool + service + PARSE_ADDRESS_INTL / EXPAND_STREET_INTL). The build is heavy and slow on ARM Macs. See references/libpostal-spcs.md.

Output: international parsing functions available once the service is READY.

Step 4: Verify

snow sql -c <connection> -f sql/examples/forward_geocode.sql
snow sql -c <connection> -f sql/examples/reverse_geocode.sql

Confirm the smoke tests return matches. The first PARSE_ADDRESS call is slow (~30 s) while the PyPI package installs; later calls are fast.

Configuration

Flag	Default	Purpose
--connection	(required)	Snow CLI connection name
--warehouse	session default	Warehouse to USE (MEDIUM recommended for batch jobs)
--intl	off	Also build/push libpostal image + deploy the SPCS intl service

Stopping Points

• ✋ Before Step 3 (--intl) — heavy image build + always-warm service cost.
• ✋ If the Overture listing cannot be auto-acquired in your region — acquire it manually from the Marketplace, then re-run.

Usage after install

-- Forward (US): free-text address column -> lat/lon
CALL GEOCODING.PUBLIC.FORWARD_GEOCODE_TABLE(
    'MY_DB.PUBLIC.PERMITS', 'ADDRESS_TEXT', 'PERMIT_ID',
    'MY_DB.PUBLIC.PERMITS_GEOCODED');

-- Reverse (worldwide): lat/lon -> nearest address within radius (meters)
CALL GEOCODING.PUBLIC.REVERSE_GEOCODE_TABLE(
    'MY_DB.PUBLIC.PINGS', 'LAT', 'LON', 'DEVICE_ID', 200,
    'MY_DB.PUBLIC.PINGS_ADDRESSED', 'US');   -- omit country / pass NULL for worldwide

Accuracy & limits

US forward geocoding is approximate (~500-900 m), ~98% hit rate on clean data; coverage gaps exist. For routing-grade accuracy use a partner solution (Carto / ESRI / Mapbox). Non-US forward matching via libpostal is fuzzier — see references/snowflake-sql-gotchas.md.

Troubleshooting

• 00_setup.sql sanity check returns 0 / errors: the Overture listing couldn't be auto-acquired (region not auto-fulfilled) or your role lacks CREATE DATABASE / IMPORT SHARE. Acquire the Carto "Addresses" listing manually and grant IMPORTED PRIVILEGES to your role, then re-run.
• PARSE_ADDRESS first call slow / times out: the shared PyPI usaddress package is installing; retry once it warms.
• --intl build fails on ARM Mac: ensure --platform linux/amd64; Podman may OOM — prefer Docker or raise the Podman machine memory.
• Service never READY: SELECT SYSTEM$GET_SERVICE_STATUS('GEOCODING.PUBLIC.LIBPOSTAL_SVC'); and check logs via SYSTEM$GET_SERVICE_LOGS(...).

Output

Snowflake-native forward + reverse geocoding deployed in GEOCODING.PUBLIC, tagged oss-geocoding, ready to call from any worksheet, notebook, or procedure.