GeoGenius

A versioned catalog of named geographic areas for Elixir and PostgreSQL/PostGIS.

GeoGenius owns the geographic reference domain: collections of named areas (states, counties, ZIP codes, school districts, or any other hierarchy you define), versioned releases of that data, atomic publication and rollback, and the read-side primitives — spatial containment, proximity, code lookup, name search, and hierarchy traversal — for resolving one against the catalog. It also ingests that data: a reviewed manifest, an artifact cache, format providers, and an explicitly retryable import pipeline that ends in a verification gate. Your application owns when an import runs, any projection it builds on top of the catalog, and presentation.

GeoGenius is not a geocoder. It resolves points, polygons, codes, and names against areas it already knows about; it does not parse or normalize free-form street addresses.

What It Does

• Catalog identity: collections, authorities, ranked area types, and areas addressed by a stable area_key, with names, external codes, retirement, and successor tracking.
• Versioned releases: sources, source releases, checksummed artifacts, and a verification gate a release must pass before it can publish.
• Optional geometry: an area joins a release through a release_area membership row; attaching a boundary is optional, and a collection can require it with requires_geometry.
• Relations derived from geometry (rebuild_relations) or asserted from source data (put_relation), traversed identically either way.
• Atomic publication and rollback through a single pointer swap, with a durable, sequenced publication event log.
• Spatial and non-spatial resolution: containment, overlap-with-coverage, proximity, code lookup, ranked name search, hierarchy traversal, and a resolve cascade over one input.
• A durable import run state machine with heartbeat leases, explicit failed-attempt retry, and one executor per attempt.
• Ingestion: manifests describing a release, an artifact cache with checksum verification, GeoJSON/CSV/shapefile/SimpleMaps providers, pluggable cache, downloader, command, notifier, and runner adapters, and mix tasks for importing, publishing, and rolling back.

Why GeoGenius?

• Prefix-installed, host-owned schema — every object installs into whatever PostgreSQL schema the host names; the package's own SQL never hard-codes a schema, so one database can host more than one GeoGenius-backed catalog.
• Geometry is optional, not assumed — a reference hierarchy with no polygons at all (postal codes imported with no boundary data, for instance) is a first-class citizen, not a degenerate case. Boundaries attach when a source provides them.
• Publish is one row, not a rebuild — readers flip on one publication row under a transaction advisory lock. complete_import finishes a verified candidate without that flip. publish_import completes the publishing attempt, drops the lease, re-verifies, and swaps the pointer in one transaction. Readers never see a half-published state.
• No invented notification protocol — publication events land in a durable, sequenced table instead of a library-chosen pg_notify channel; see Reacting to publication for why, and the host-owned trigger recipe if a live signal is wanted.
• Postgres is the source of truth — the schema installs through EctoEvolver raw SQL migrations, wrapped in a host-owned, version-pinned Ecto migration the host commits and runs itself.

Architecture

GeoGenius owns exactly one PostgreSQL schema, at whatever prefix the host selects, through EctoEvolver raw SQL migrations. Every table, view, function, and type reference in the shipped SQL is a $SCHEMA$ placeholder substituted with the configured prefix at migration time — the package never hard-codes a schema name, and the same SQL installs identically at any prefix.

Current tables:

• collection, authority, area_type, area, area_name, area_code
• source, source_release, artifact, release, release_source
• publication, publication_event
• boundary, boundary_part, relation, release_area (partitioned by release)
• import_run, import_run_lease, import_run_artifact

Current views: release_areas, release_area_codes, release_area_names, release_relations, published_areas, published_area_codes, published_area_names, published_area_relations, published_boundaries, import_run_status, release_artifacts, run_artifacts.

Every catalog operation is a SQL function or view, and the Elixir API is a layer over them rather than a second implementation. GeoGenius exposes the read functions (areas_for_point/3 and its siblings), the ingestion API (import/1, status/2, await/3, publish/2, rollback/2, published_release/2), and GeoGenius.verify/2 for a startup prerequisite check; GeoGenius.Query wraps the views for hosts composing their own Ecto queries; the mix tasks drive the same calls from a deploy. A host that prefers raw SQL can still call every function and view below through its own Repo.

Prerequisites

• Elixir 1.17+
• PostgreSQL with the postgis and pg_trgm extensions, and an Ecto Repo (Ecto SQL + Postgrex >= 0.20.0 and < 0.23.0)
• GDAL's ogr2ogr binary when using the shapefile provider; GeoJSON and CSV ingestion do not require it

Installation

Add geo_genius to your dependencies in mix.exs:

def deps do
  [
    {:geo_genius, "~> 0.2.0"}
  ]
end

Then fetch dependencies:

mix deps.get

Setup

Configure the host repo and PostgreSQL prefix:

# config/config.exs
config :my_app, ecto_repos: [MyApp.Repo]
config :geo_genius, repo: MyApp.Repo, prefix: "geo_genius"

:prefix belongs in config/config.exs and must never move to config/runtime.exs: GeoGenius.Query reads it at compile time, and Elixir refuses to boot when a runtime value differs from what that read recorded, including when the runtime value is the identical default string. :repo may live wherever the host prefers. See guides/installation.md.

PostGIS and pg_trgm must already exist in the target database before migrating — install them yourself, as a privileged role, or generate the setup migration with --with-extensions to have it issue the CREATE EXTENSION statements for you (only works if the role running the migration has that privilege):

mix geo_genius.setup --repo MyApp.Repo --prefix geo_genius
mix ecto.migrate
mix geo_genius.check_schema --repo MyApp.Repo --prefix geo_genius

geo_genius.setup delegates to the host's own ecto.gen.migration. It generates a host-owned Ecto migration wrapper pinned to the installed schema version and never runs a migration itself. Commit the wrapper so a later package upgrade cannot silently change an already-deployed migration. Later upgrades use mix geo_genius.gen.migration --from N --to N+1. See guides/installation.md for prefixes, the pinned wrapper, SQL-first rendering, the pre-production return-and-reinstall policy, the destructive down migration, and uninstalling. GeoGenius never installs or changes a host schema automatically.

Startup verification. Place {GeoGenius.Preflight, repo: MyApp.Repo, prefix: "geo_genius"} in the supervision tree right after the Repo to fail host startup, with a clear message, when the required extensions, schema version, or content-addressed schema contract do not match — rather than deferring that failure to the first query that touches GeoGenius. An older pre-production install must be returned and reinstalled from the current package; see guides/installation.md.

Usage

Every operation is a SQL function or view. GeoGenius calls them for a host that wants typed results, GeoGenius.Query returns composable Ecto queries over the views, and a host that prefers raw SQL calls the same functions through its own Repo.

Importing a collection

Write a manifest naming the collection, the release, the provider that parses it, and every file it is built from, at <manifest_dir>/<collection>/<release>.json:

{
  "collection": "us_counties",
  "collection_name": "US Counties",
  "release": "2026",
  "provider": "geojson",
  "requires_geometry": true,
  "authorities": [{ "key": "census", "name": "US Census Bureau" }],
  "area_types": [{ "key": "county", "rank": 200 }],
  "sources": [
    {
      "source_key": "census:counties",
      "provider": "geojson",
      "license": "public-domain",
      "release_key": "2026",
      "artifacts": [
        {
          "logical_name": "counties.geojson",
          "url": "https://example.test/counties.geojson",
          "operator_supplied": false,
          "format": "geojson",
          "required": true,
          "sha256": "0000000000000000000000000000000000000000000000000000000000000000",
          "bytes": 4096
        }
      ]
    }
  ],
  "options": { "code_property": "GEOID", "name_property": "NAME", "area_type": "county" }
}

Then import it, watch it, and publish it:

mix geo_genius.import --collection us_counties --release 2026
mix geo_genius.status --collection us_counties
mix geo_genius.publish --collection us_counties --release 2026

An import produces a verified candidate; publishing is a separate act, so nothing hosts read changes until you make that call. See guides/ingestion.md for the manifest in full, the provider contract, the adapters, and the runner backends.

Drive an import through SQL

prepare_import registers the exact manifest and claims one attempt. Every ingestion mutation then takes that run_id, not the release id; the run fence prevents a failed or superseded attempt from changing the candidate. The high-level GeoGenius.import/1 pipeline performs this sequence for you. A direct loader uses the same lifecycle:

manifest_map = GeoGenius.Manifest.to_map(manifest)

{:ok, %{rows: [["enqueue", "registered", release_id, run_id, 1]]}} =
  MyApp.Repo.query(
    "SELECT * FROM geo_genius.prepare_import($1, $2)",
    [manifest_map, %{owner: "catalog-loader", runner_backend: "direct"}]
  )

executor_id = Ecto.UUID.generate()

{:ok, %{rows: [["claimed"]]}} =
  MyApp.Repo.query(
    "SELECT geo_genius.claim_import_execution($1, $2)",
    [run_id, executor_id]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'downloading', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, %{rows: [[artifact_id]]}} =
  MyApp.Repo.query(
    "SELECT artifact_id FROM geo_genius.run_artifacts WHERE run_id = $1",
    [run_id]
  )

[source] = manifest.sources
[artifact] = source.artifacts

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.record_artifact_observation($1, $2, $3, $4, $5)",
    [run_id, executor_id, artifact_id, artifact.sha256, artifact.bytes]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'validating', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'staging', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'normalizing', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.upsert_area_many($1, $2, $3, $4, $5)",
    [run_id, executor_id, ["census"], ["county"], ["48201"]]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.put_area_in_release($1, $2, $3, NULL, $4)",
    [run_id, executor_id, "census:county:48201", %{}]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.put_area_code($1, $2, $3, $4, $5)",
    [run_id, executor_id, "census:county:48201", "fips", "48201"]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'relating', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'indexing', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'verifying', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, %{rows: [[verification]]}} =
  MyApp.Repo.query("SELECT geo_genius.verify_import($1, $2)", [run_id, executor_id])
# %{"ok" => true, "failures" => [], "area_count" => 1, "boundary_count" => 0}

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.advance_import($1, $2, 'publishing', '{}'::jsonb)",
    [run_id, executor_id]
  )

{:ok, _} = MyApp.Repo.query("SELECT geo_genius.publish_import($1, $2)", [run_id, executor_id])

Only the executor whose claim returned claimed runs the attempt. Another delivery receives occupied and must not take over. An independently supervised guardian records failure when that executor process dies while the Repo remains reachable. Whole-node loss, or shutdown after the Repo has already stopped, cannot be recorded by a local guardian; a stale heartbeat is then diagnostic. Fail the abandoned attempt with its latched executor_id from import_run_status, then call retry_failed to create a new run with its own immutable manifest, artifact observations, and failure evidence. A new executor cannot fail a live lease, and import/1 will not replace it. publish_import removes the lease, marks the run completed, verifies the release, and swaps publication in one transaction. Calling GeoGenius.import/1 again after a durable failure returns %GeoGenius.CandidateError{reason: :failed} for an identical manifest (or :manifest_changed for a different one); ordinary import never creates the replacement attempt.

publish_release(release_id) remains an operator API for a release with no active lease and no incomplete latest import. It is not the ingestion completion path. A published release is immutable; changing what readers see means importing a new release key and publishing that candidate.

Attaching a real centroid or boundary binds a Geo struct directly as the query parameter, not WKT/EWKT text: GeoGenius requires the host Repo to be configured with a Postgrex types module that registers Geo.PostGIS.Extension before it will even boot (GeoGenius.Preflight fails startup, with a remedy, otherwise), and that same codec covers every Repo.query/2 call, not only reads. See guides/reading.md for the Repo configuration and guides/installation.md for where it sits alongside the extension requirements.

{:ok, _} =
  MyApp.Repo.query(
    "SELECT geo_genius.put_area_in_release($1, $2, $3, $4, $5)",
    [
      run_id,
      executor_id,
      "census:county:48201",
      %Geo.Point{coordinates: {-97.75, 30.5}, srid: 4326},
      %{}
    ]
  )

Read through resolution

Once published, hosts read through the published_* views (they resolve through the publication pointer, so no view takes a release argument) or through the resolution functions:

{:ok, %{rows: rows}} =
  MyApp.Repo.query(
    "SELECT area_key, match_method FROM geo_genius.resolve($1)",
    [%{code_type: "fips", code_value: "48201"}]
  )
# [["census:county:48201", "code"]]

resolve/6 tries containment, code, name, and proximity strategies in order against one JSON input and returns the first strategy's matches; see guides/sql_api.md for what each input key selects.

Reading the catalog

GeoGenius's Elixir read layer wraps the SQL reads above in typed structs, so a host resolves an area without hand-building SQL or mapping Postgrex.Result rows itself:

GeoGenius.areas_for_point(-97.75, 30.5)
# [%GeoGenius.AreaMatch{area_key: "census:county:48201", match_method: "containment", ...}]

GeoGenius.areas_by_code("fips", "48201")
# [%GeoGenius.AreaMatch{area_key: "census:county:48201", match_method: "code", ...}]

Both calls resolve :repo and :prefix from config :geo_genius, repo: ..., prefix: ... unless a call overrides them. See guides/reading.md for every read, the shared option keys, what %GeoGenius.AreaMatch{} carries, the plural reads that resolve a whole list in one call, and GeoGenius.Query for composing a catalog read with a host's own tables in one round trip.

Loading data has an Elixir API of its own: GeoGenius.import/1 runs a manifest through the ingestion pipeline, and GeoGenius.publish/2 and GeoGenius.rollback/2 move the publication pointer. See guides/ingestion.md. Calling the SQL write functions below directly remains supported for a host loading data some other way.

Public API

Every operation is a SQL function or view called through the host's own Repo. A representative set, grouped by what they do — the complete reference, every function and its full parameter list, lives in guides/sql_api.md:

-- Resolution
SELECT * FROM geo_genius.areas_for_point(lon, lat);
SELECT * FROM geo_genius.areas_by_code(code_type, code_value);
SELECT * FROM geo_genius.search_areas(query);
SELECT * FROM geo_genius.resolve(input);

-- Traversal
SELECT * FROM geo_genius.children_of(parent_area_key);
SELECT * FROM geo_genius.ancestors_of(child_area_key);

-- Set-keyed reads: one call per list, not one call per element
SELECT * FROM geo_genius.children_of_many(parent_area_keys);
SELECT * FROM geo_genius.areas_by_code_many(code_type, code_values);

-- Write
SELECT geo_genius.upsert_collection(key, name, description, requires_geometry);
SELECT geo_genius.upsert_area(collection_key, authority_key, area_type_key, code);
SELECT geo_genius.put_area_in_release(run_id, executor_id, area_key, centroid, data);
SELECT geo_genius.put_boundary(run_id, executor_id, area_key, source_release_id, geom);

-- Set writes: one call per batch, not one call per row. Each scalar write above
-- is its plural form called with one-element arrays.
SELECT geo_genius.upsert_area_many(run_id, executor_id, authority_keys, area_type_keys, codes);
SELECT geo_genius.put_area_in_release_many(run_id, executor_id, area_keys, centroids, data);
SELECT geo_genius.put_boundaries(
  run_id, executor_id, area_keys, source_release_ids, geometries, display_tiers, source_properties
);

-- Lifecycle
SELECT geo_genius.verify_release(release_id);
SELECT geo_genius.publish_release(release_id);
SELECT geo_genius.rollback_publication(collection_key);

-- Import
SELECT * FROM geo_genius.prepare_import(manifest, claim);
SELECT * FROM geo_genius.retry_failed(failed_run_id, manifest, claim);
SELECT geo_genius.claim_import_execution(run_id, executor_id);
SELECT geo_genius.advance_import(run_id, executor_id, next_status, metrics_patch);
SELECT geo_genius.verify_import(run_id, executor_id);
SELECT geo_genius.complete_import(run_id, executor_id, metrics_patch);
SELECT geo_genius.publish_import(run_id, executor_id);

Reads that need no resolution logic join the read views instead. They come in pairs: a release_* base carrying every release, and a published_* view that is the base joined to the publication pointer, so a pointer swap changes what all of them show at once:

GeoGenius.Published is the Elixir surface over those views: read-only Ecto schemas carrying every column, plus composable query functions (areas/1, children_of/2, ancestors_of/2, areas_by_code/3, codes/1, names/1, relations/1) that return an Ecto.Query a host joins and aggregates against its own tables. Use it for joins, aggregates, set-keyed reads, and anything keyed on release_id; use GeoGenius and GeoGenius.Query for spatial resolution, name search, and multi-level traversal. Its :release_id option reads a release whether or not its collection publishes it, by swapping every source in the query onto the release_* base views.

See guides/installation.md for what NULL measurement columns on a relation row mean, why max_depth costs what it costs on a densely mutually-related graph, why published_areas does not hide retired areas, and how to react to a publication without polling.

Mix Tasks

Guides

• guides/installation.md — prefixes, the pinned wrapper, upgrades, the destructive down migration, uninstalling, retired areas, measured versus asserted relations, traversal depth on dense graphs, and reacting to publication
• guides/sql_api.md — every SQL function, grouped and with its full parameter list
• guides/reading.md - the Elixir read layer: every read, shared options, what %AreaMatch{} carries, and GeoGenius.Query
• guides/ingestion.md — the manifest, providers, adapters, runners, the import phases, publication, and the operational mix tasks
• guides/projections.md — keeping a source's own columns in a host-owned table keyed (release_id, area_key), and resolving the artifacts to fill it from
• CHANGELOG — releases
• docs/design/geo-genius-design.md — the design spec: what the catalog stores, why a release is immutable once published, and what the library deliberately leaves to its host

Testing

mix test
mix check   # format check, compile --warnings-as-errors, credo --strict, doctor, test

Integration tests run real install and uninstall cycles against Postgres, at the default prefix, at a custom one, and at a prefix that requires quoting. They are excluded from the default run:

mix test --include integration

The migration wrapper the Mix tasks generate is covered by the default run, which needs no database.

The SQL layer itself is covered by a pgTAP suite, run against the same database:

./test/pgtap/run.sh

Quality gates used by this repo:

mix quality   # compile --warnings-as-errors, deps.unlock --unused, format check, sobelow, ex_dna, doctor, credo --strict

License

MIT