Skip to content
johnzastrowPublic

About

A PostgreSQL 18 / PostGIS geocoder with a Pelias-compatible API. Builds any US state, or several at once, from OpenAddresses, OpenStreetMap, Who's on First, GNIS, Overture and Census data.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Repository files navigation

pgeo

pgeo

PGEO is a feature-rich geocoder that can live entirely inside PostgreSQL. Haven't you always wanted a personal geocoding service? PGEO is a personal, petite PostgreSQL Geocoder (hence the short name PGEO) for when you need just enough geocoding and a simple deployment. It is fast, resource-efficient, easy to deploy, and feature-rich. With 1 CPU and 1 GB of RAM it served 32 concurrent users with a median answer in 40 ms and most autocomplete keystrokes under 35 ms, using rich data for the whole State of Maine. It does not scale horizontally the way Pelias does. We measured it against Pelias, Photon and Nominatim for accuracy, speed and scalability: docs/REPORT.pdf.

This project is unrelated to pgeocode, a Python postal-code library.

Getting started goes from nothing to a geocoder answering on a public URL, for any US state. The workstation half is one command:

scripts/build_region.sh --build ny     # or me,nh,vt, or any state code

Why pgeo

  • It is more accurate on the same data. On 1,560 ground-truth cases for Maine, pgeo answered 95.8% correctly against Pelias's 75.5%, with the identical inputs. Photon managed 73.7% and Nominatim 61.7%, both on OpenStreetMap alone.
  • It fits on a small server. One core, one gigabyte, one Postgres. No Elasticsearch, no JVM, no six-container stack to keep alive.
  • It is Pelias-compatible. The same /v1/search, /v1/autocomplete, /v1/reverse and /v1/place with the same query parameters and the same GeoJSON, so existing Pelias clients work unchanged.
  • The data is richer than OSM alone. OpenAddresses, OpenStreetMap, Who's on First, USGS GNIS, Census ZCTAs and Overture Places, conflated into one table.
  • It can be pure SQL. Serve it with PostgREST and there is no application code in the request path at all - or use the FastAPI front end if you want libpostal parsing.
  • You can host it yourself, entirely. Every source is downloaded straight from its publisher; nothing phones home, and no query leaves your network.

When to use which

Use Why
pgeo One state or a handful; modest hardware; you already run PostgreSQL; you need address accuracy and autocomplete more than global coverage
Pelias Many countries or the planet; you want to scale out across machines; you have the operational appetite for Elasticsearch
Photon OSM is enough; you want good typo tolerance and a simple single service; venues and addresses are not the priority
Nominatim OSM is enough; you need detailed reverse geocoding and administrative polygons; you do not need autocomplete

pgeo's limits are real and measured: it is a single database, so capacity grows by making the machine bigger rather than by adding machines, and the report's section 4 says where it stops.

What is in the box

  • A PostgreSQL 18 / PostGIS geocoder with a Pelias-compatible HTTP API, served by PostgREST (no application code) or FastAPI
  • A build pipeline that turns six open datasets into one table, for any US state or combination of states
  • A demo web page - map, autocomplete, structured search, reverse geocoding, a batch CSV tool, and four tabs that show off confidence, boundary search, nearby search and form filling
  • Ansible roles that take a bare Debian host to a hardened, rate-limited, API-key-protected service
  • A test suite that measures accuracy against independent ground truth, not against itself
  • A pre-processor image (ghcr.io/johnzastrow/pgeo-build) and a three-container serving bundle of stock images (DOCKER-DEPLOY.md, deploy/README.md, docs/DOCKER_IMAGES.md)

Requirements and technology stack

pgeo is built on one workstation and served from another machine, and the two have very different needs. For how this compares with Pelias, Photon and Nominatim, see docs/STACK_COMPARISON.md.

Serving (the query host)

Component Version Notes
PostgreSQL 18 The geocoder itself. Parsing, ranking and JSON formatting run in PL/pgSQL
PostGIS 3.6 Spatial indexes, point-in-polygon, distance ranking, reverse geocoding
pg_trgm, fuzzystrmatch, unaccent ship with PostgreSQL Fuzzy and type-ahead matching, typo rescue, diacritic folding
PostgREST v16.3 Pure-SQL HTTP front end. No application code
or FastAPI on Python 3.12+ Optional alternative front end (FastAPI, Uvicorn, asyncpg, Pydantic), and the route to libpostal parsing
nginx 1.29 Maps Pelias paths to SQL functions; API keys, rate limits, security headers, demo page
Docker Engine + Compose current Packaging. Three stock images, pinned by digest: postgis/postgis, postgrest/postgrest, nginx

It needs no search engine, no JVM, no Node.js and no separate parser or point-in-polygon service.

The stack can be smaller still. Every endpoint is a SQL function, so nginx and PostgREST only carry requests to the database and protect it:

Shape What runs What you give up
Full edge (default) PostgreSQL + PostgREST + nginx Nothing. Pelias paths, API keys, rate limits, demo page
FastAPI direct PostgreSQL + FastAPI API keys, rate limits, security headers, demo page. Pelias paths stay
PostgREST direct PostgreSQL + PostgREST The above, plus Pelias paths: call /rpc/v1_search?text=... instead
SQL only PostgreSQL alone, or a database you already run All HTTP. Your app runs SELECT geocode_api.v1_search(text => '...')

For a greenfield app that does not need Pelias compatibility, the last two shapes keep every geocoding function: search, structured search, autocomplete, reverse, place, /v1/address, filters and confidence. Only the internet-facing protections go, so run those shapes on a private network or behind a gateway you already have. docs/STACK_COMPARISON.md section 9 gives the details, parameter names and trade-offs, and section 9.4 shows how to keep full Pelias compatibility with Caddy in place of nginx.

Hardware, measured:

Region vCPU RAM Disk
Maine-sized state (0.9 M features) 1 1 GB 10 GB
New York-sized state (7.2 M features) 2 4 GB 20 GB

The reference host is Debian 13, and the Ansible roles in infra/ansible/ target it.

Building (the workstation)

Tool Purpose
Docker Engine + Compose Runs the build database
Python 3.12+ with uv Loader (pgeo-load), tuning (pgeo-tune), data prep (pelias-prep)
DuckDB 1.5.5 Reads Overture Parquet and the CSV sources (installed by uv)
GDAL (ogr2ogr) Loads the OSM extract and the Census shapefiles
git, curl, jq, unzip, bzip2 Fetching and scripting
pmtiles (optional) Cuts the demo page's basemap to the region

Budget 20 GB of disk for a small state and 40 GB for a large one, plus a one-off 5.2 GB for Who's on First that every build shares. Maine builds in about 5 minutes and New York in about 44. See GETTING_STARTED.md for install commands and docs/SIZING.md for the measurements.

The demo page

Served same-origin under a strict CSP: no CDN, no remote script, a self-hosted MapLibre and a PMTiles basemap cut to the region. Every capture below is the Maine build answering for itself.

Search tab: autocomplete results for a venue, with the map following Confidence tab: every candidate with its score, and a plain-words reading of it
Search. Type-ahead over addresses, venues, streets and places, biased to the map centre and filterable by layer and source. Confidence. Every candidate with its score, and a sentence saying what the score means. A weak match says so instead of pretending.
Area tab: a rectangle drawn on the map and the results found inside it Nearby tab: the address at a clicked point and everything around it by distance
Area. Drag a rectangle or click a circle, then search inside it. Here "mosc" in the box is Moscow, Maine and two things named after it, out of a much longer statewide list. Nearby. Click the map: the address at that point, then everything around it by distance, grouped by kind.
Form filler: a contact form completed from the chosen address Batch tab: a CSV of addresses geocoded, with a match and confidence per row
Form filler. The map follows the best candidate as you type; Confirm fills a contact form from /v1/address, formatted to USPS Publication 28. Batch. Paste or upload a CSV - forward or reverse - and get a match, a confidence and a note per row, throttled client-side.

Also there and not pictured: Structured search by field, Reverse geocoding with a layer and radius filter, and Compare, which runs the same query against two engines side by side where both are configured. Which geocoder the page talks to is a one-line file - see docs/DEMO_ENGINES.md. Full set in docs/screenshots/.

Documentation

Start here

Document What it covers
GETTING_STARTED.md Zero to a hosted geocoder, mostly commands
docs/REPORT.md / REPORT.pdf The study: four engines, accuracy, capacity, cost
docs/OPTIMIZATION.md / .pdf What changed after it: any US state, a 3.7x faster build, and the defects a second region found
docs/SIZING.md What a deployment costs, measured for Maine and New York
docs/STACK_COMPARISON.md Requirements and technology stacks of pgeo, Pelias, Photon and Nominatim compared
DOCKER-DEPLOY.md The container route, with the published pgeo-build image
TODO.md What is not done, and what was decided about it

Running it

Document What it covers
docs/DATA_PIPELINE.md Every source, where it comes from, how it is converted
docs/REBUILD.md Rebuild everything from scratch, and what each step proves
docs/DEPLOY_PGEO.md The query host in detail
docs/CONFIGURATION.md Every limit and knob a deployment can change: request limits, statement timeout, rate limits, pools
docs/SECURITY_DEPLOYMENT.md The hardening the Ansible roles apply
docs/API_KEYS.md Issuing, using and revoking keys at the edge
docs/PGEO_TUNING.md / TUNING_REPORT.md The tuning profiles and how they were measured

Using the API

Document What it covers
docs/PELIAS_COMPATIBILITY.md What matches Pelias, and where it deliberately does not
docs/ADDRESS_API.md /v1/address, the structured extension
docs/HTTP_OPTIONS.md PostgREST or FastAPI, and why you might pick each

How it works, and how it was checked

Document What it covers
docs/PGEO_DESIGN.md The schema, the query pipeline, the ranking
docs/PERFORMANCE_OPTIMIZATION.md The 0.10.0 optimisation work, including what did not help
docs/TESTING_GUIDE.md What the tests do, in plain language
docs/ACCURACY_RESULTS.md / ENGINE_COMPARISON.md The measurements
docs/PROJECT_LOG.md Goals, questions, decisions, findings, in order

Layout

Path Purpose
pgeo/ The geocoder: SQL, loader, FastAPI app, tuning profiles
regions/ regions.json: the per-state facts every build step reads
prep/ Python (uv) package pelias-prep: GNIS, Census ZCTA, Overture Places -> CSV
scripts/ Fetch data, build, snapshot, issue API keys, serve the page locally
web/ Demo page (MapLibre + self-hosted Protomaps basemap) and a reusable search element
infra/ansible/ Host config for any Debian host: hardening, Docker, query stack, nginx edge
infra/proxmox/, infra/wharf/ VM creation, and a reference TLS front end
tests/ Accuracy, load, compatibility, security and browser tests
projects/pelias_maine/ The Pelias stack pgeo was measured against
report/, slides/, docs/ The study, the deck, and the documentation above
data/ Downloads and build artefacts (gitignored, except the measurements)

Licence and data

The code is Apache-2.0. The data it loads is not: each source carries its own licence and a deployment that redistributes data or derived tiles must comply with them - see NOTICE and docs/DATA_PIPELINE.md section 13. A running service reports the same list at /v1/attribution.

About

A PostgreSQL 18 / PostGIS geocoder with a Pelias-compatible API. Builds any US state, or several at once, from OpenAddresses, OpenStreetMap, Who's on First, GNIS, Overture and Census data.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Used by

Contributors

Languages