A5 Geospatial Extension for DuckDB

June 24, 2026 · View on GitHub

A high-performance DuckDB extension that provides functions for the A5 global geospatial index - a millimeter-accurate, equal-area indexing system for geospatial data.

✨ What is A5?

A5 is an innovative geospatial index that partitions the world into pentagonal cells based on a geodesic grid. Key features include:

  • 🌍 Global Coverage: Seamless indexing from global to millimeter scales
  • 📐 Equal Area: All cells at the same resolution level have identical area (OGC compliant)
  • 🔍 31 Resolution Levels: From world-spanning cells to sub-30mm² precision
  • ⚡ Fast Spatial Operations: Optimized for aggregation, filtering, and spatial joins

🎯 Use Cases

Spatial Data Aggregation

Group point data spatially to understand distributions:

-- Analyze restaurant density by A5 cells
SELECT a5_lonlat_to_cell(longitude, latitude, 15) as cell_id, COUNT(*) as restaurant_count
FROM restaurants
GROUP BY cell_id
ORDER BY restaurant_count DESC;

🚀 Quick Start

Installation

The a5 extension is available as a DuckDB Community Extension:

INSTALL a5 FROM community;
LOAD a5;

DuckDB version compatibility: Development tracks DuckDB releases on parallel branches — v1.5 (the default branch) targets DuckDB v1.5 (v1.5-variegata), while main targets the previous stable line. The Community Extensions registry builds and serves the binary matching your installed DuckDB version, so the INSTALL command above resolves to the right build automatically.

Basic Usage

-- Get the A5 cell for a specific location (longitude, latitude, resolution)
SELECT a5_lonlat_to_cell(-74.0060, 40.7128, 15) as nyc_cell;  -- Times Square
┌─────────────────────┐
│      nyc_cell       │
│       uint64        │
├─────────────────────┤
│ 2742821848331845632 │
└─────────────────────┘

-- Find the area of that cell in square meters
SELECT a5_cell_area(15) as cell_area_m2;
┌───────────────────┐
│   cell_area_m2    │
│      double       │
├───────────────────┤
│ 31669.04205949599 │
└───────────────────┘

-- Get the center coordinates of a cell
SELECT a5_cell_to_lonlat(a5_lonlat_to_cell(-74.0060, 40.7128, 15)) as center_coords;
┌────────────────────────────────────────┐
│             center_coords              │
│               double[2]                │
├────────────────────────────────────────┤
│ [-74.00764805615836, 40.7128022513843] │
└────────────────────────────────────────┘

-- Find parent cell at lower resolution
SELECT a5_cell_to_parent(a5_lonlat_to_cell(-74.0060, 40.7128, 15), 10) as parent_cell;
┌─────────────────────┐
│     parent_cell     │
│       uint64        │
├─────────────────────┤
│ 2742821365684895744 │
└─────────────────────┘

-- Get all children cells at higher resolution
SELECT a5_cell_to_children(a5_lonlat_to_cell(-74.0060, 40.7128, 10), 11) as child_cells;
┌──────────────────────────────────────────────────────────────────────────────────────┐
│                                     child_cells                                      │
│                                       uint64[]                                       │
├──────────────────────────────────────────────────────────────────────────────────────┤
│ [2742820953368035328, 2742821228245942272, 2742821503123849216, 2742821778001756160] │
└──────────────────────────────────────────────────────────────────────────────────────┘

Code Example: Generate GeoJSON for Cell

The simplest way to get a GeoJSON polygon for an A5 cell is a5_cell_to_geometry, which returns a DuckDB GEOMETRY directly (combine with the spatial extension's ST_AsGeoJSON):

SELECT ST_AsGeoJSON(a5_cell_to_geometry(a5_lonlat_to_cell(-3.7037, 40.41677, 10))) as g;

Equivalently, without a5_cell_to_geometry, you can build the polygon from the raw boundary points:

SELECT
    ST_AsGeoJSON(
        ST_MakePolygon(
            ST_MakeLine(
                list_transform(
                    a5_cell_to_boundary(
                        a5_lonlat_to_cell(-3.7037, 40.41677, 10)
                    ),
                    x -> ST_Point(x[1], x[2])
                )
            )
        )
    ) as g

This produces:

{
    "type":"Polygon",
    "coordinates":[
        [
            [-3.63932161106527,40.445029005677384],
            [-3.697330052435973,40.44427170464866],
            [-3.745928891833728,40.424159040292594],
            [-3.707910290384234,40.394201800420205],
            [-3.6544386596323193,40.4080830654645],
            [-3.63932161106527,40.445029005677384]
        ]
    ]
}

Visualizing that A5 cell shows:

{
    "type":"Polygon",
    "coordinates":[
        [
            [-3.63932161106527,40.445029005677384],
            [-3.697330052435973,40.44427170464866],
            [-3.745928891833728,40.424159040292594],
            [-3.707910290384234,40.394201800420205],
            [-3.6544386596323193,40.4080830654645],
            [-3.63932161106527,40.445029005677384]
        ]
    ]
}

📚 API Reference

Function Index

FunctionReturnsDescription
a5_lonlat_to_cell(lon, lat, res)UBIGINTCell containing a coordinate
a5_cell_to_lonlat(cell)DOUBLE[2]Cell center [lon, lat]
a5_cell_to_boundary(cell [, closed, segments])DOUBLE[2][]Boundary vertices
a5_cell_area(res)DOUBLECell area (m²) at a resolution
a5_get_resolution(cell)INTEGERResolution of a cell
a5_get_num_cells(res)UBIGINTTotal cells at a resolution
a5_get_num_children(parent_res, child_res)UBIGINTChildren count between resolutions
a5_cell_to_parent(cell, res)UBIGINTParent at a coarser resolution
a5_cell_to_children(cell [, res])UBIGINT[]Children at a finer resolution
a5_get_res0_cells()UBIGINT[]The 12 base (resolution-0) cells
a5_world_cell()UBIGINTThe root world cell (0)
a5_is_valid_cell(cell)BOOLEANWhether a value is a valid cell ID
a5_compact(cells)UBIGINT[]Merge sibling cells into parents
a5_uncompact(cells, res)UBIGINT[]Expand to a uniform resolution
a5_grid_disk(cell, k)UBIGINT[]Cells within k edge-steps
a5_grid_disk_vertex(cell, k)UBIGINT[]Cells within k vertex-steps
a5_spherical_cap(cell, radius)UBIGINT[]Cells within a radius (meters)
a5_geometry_to_cells(geom, res)UBIGINT[]Cells covering any GEOMETRY
a5_cell_to_geometry(cell [, segments])GEOMETRYCell as a POLYGON
a5_cell_to_point(cell)GEOMETRYCell center as a POINT
a5_hex_to_u64(hex)UBIGINTParse a hex cell ID
a5_u64_to_hex(cell)VARCHARCell ID as a hex string

Core Functions

a5_lonlat_to_cell(longitude, latitude, resolution) -> UBIGINT

Returns the A5 cell ID for given coordinates and resolution level.

Parameters:

  • longitude (DOUBLE): Longitude in decimal degrees (-180 to 180)
  • latitude (DOUBLE): Latitude in decimal degrees (-90 to 90)
  • resolution (INTEGER): Resolution level (0-30, where 0 is coarsest)

Example:

SELECT a5_lonlat_to_cell(-0.1278, 51.5074, 12) as london_cell;
┌─────────────────────┐
│     london_cell     │
│       uint64        │
├─────────────────────┤
│ 7161033366718906368 │
└─────────────────────┘

a5_cell_area(resolution) -> DOUBLE

Returns the area of an A5 cell in the specified resolution in square meters.

Example:

SELECT a5_cell_area(5) as area_m2;
┌────────────────────┐
│      area_m2       │
│       double       │
├────────────────────┤
│ 33207397446.578068 │
└────────────────────┘

a5_get_resolution(cell_id) -> INTEGER

Returns the resolution level of an A5 cell.

Example:

SELECT a5_get_resolution(207618739568) as resolution;
┌────────────┐
│ resolution │
│   int32    │
├────────────┤
│     30     │
└────────────┘

Spatial Relationships

a5_cell_to_parent(cell_id, target_resolution) -> UBIGINT

Returns the parent cell at a coarser resolution level.

Example:

SELECT a5_cell_to_parent(207618739568, 10) as parent_cell;
┌──────────────────────┐
│     parent_cell      │
│        uint64        │
├──────────────────────┤
│ 11529215595824283648 │
└──────────────────────┘

a5_cell_to_children(cell_id, target_resolution) -> UBIGINT[]

Returns all children cells at a finer resolution level.

Example:

SELECT unnest(a5_cell_to_children(a5_lonlat_to_cell(2.2945, 48.8584, 10), 11)) as child_cells;
┌─────────────────────┐
│     child_cells     │
│       uint64        │
├─────────────────────┤
│ 7188344376559403008 │
│ 7188344651437309952 │
│ 7188344926315216896 │
│ 7188345201193123840 │
└─────────────────────┘

Geometric Properties

a5_cell_to_lonlat(cell_id) -> DOUBLE[2]

Returns the center coordinates [longitude, latitude] of a cell.

Example:

SELECT a5_cell_to_lonlat(207618739568) as center;
┌──────────────────────────────────────────┐
│                  center                  │
│                double[2]                 │
├──────────────────────────────────────────┤
│ [122.99924776996579, -52.73900617231107] │
└──────────────────────────────────────────┘

a5_cell_to_boundary(cell_id, [closed_ring, [segments]]) -> DOUBLE[2][]

Returns the boundary vertices of a cell as an array of [longitude, latitude] pairs.

Parameters:

  • cell_id (UBIGINT): The A5 cell
  • closed_ring (BOOLEAN): Whether to close the ring by repeating the first point at the end. Defaults to true.
  • segments (INTEGER): Number of segments to use for each edge. If this argument is not supplied or a value is supplied that is <= 0, a resolution-appropriate value will be used.

Examples:

SELECT unnest(a5_cell_to_boundary(207618739568)) as boundary_points;
┌──────────────────────────────────────────┐
│             boundary_points              │
│                double[2]                 │
├──────────────────────────────────────────┤
│ [122.9992478383981, -52.739006156957515] │
│ [122.9992477686406, -52.73900615209739]  │
│ [122.99924770219661, -52.73900616591276] │
│ [122.9992477354179, -52.73900619711099]  │
│ [122.99924780517563, -52.73900618947682] │
│ [122.9992478383981, -52.739006156957515] │
└──────────────────────────────────────────┘

SELECT unnest(a5_cell_to_boundary(207618739568, false, 5)) as boundary_points;
┌───────────────────────────────────────────┐
│              boundary_points              │
│                 double[2]                 │
├───────────────────────────────────────────┤
│ [122.99924782444657, -52.73900615598544]  │
│ [122.99924781049504, -52.73900615501345]  │
│ [122.99924779654361, -52.73900615404143]  │
│ [122.99924778259208, -52.73900615306941]  │
│ [122.9992477686406, -52.73900615209739]   │
│ [122.9992477553518, -52.739006154860455]  │
│ [122.99924774206306, -52.73900615762355]  │
│ [122.99924772877421, -52.73900616038661]  │
│ [122.99924771548547, -52.739006163149696] │
│ [122.99924770219661, -52.73900616591276]  │
│ [122.99924770884093, -52.73900617215241]  │
│ [122.99924771548513, -52.73900617839204]  │
│ [122.99924772212938, -52.7390061846317]   │
│ [122.99924772877364, -52.73900619087136]  │
│ [122.9992477354179, -52.73900619711099]   │
│ [122.99924774936949, -52.739006195584174] │
│ [122.99924776332102, -52.73900619405734]  │
│ [122.99924777727256, -52.739006192530496] │
│ [122.9992477912241, -52.73900619100366]   │
│ [122.99924780517563, -52.73900618947682]  │
│ [122.99924781182006, -52.739006182972965] │
│ [122.9992478184646, -52.7390061764691]    │
│ [122.99924782510902, -52.739006169965215] │
│ [122.99924783175356, -52.739006163461376] │
│ [122.9992478383981, -52.739006156957515]  │
├───────────────────────────────────────────┤
│                  25 rows                  │
└───────────────────────────────────────────┘

Region Functions

a5_geometry_to_cells(geom, resolution) -> UBIGINT[]

Indexes any vector geometry into the set of A5 cells covering it. It builds directly on DuckDB's built-in GEOMETRY type — no spatial extension is required (though it composes with it):

  • Points map to their containing cell.
  • Lines are traced, in order.
  • Polygons are filled by center containment (a cell is included iff its center lies inside), with interior rings (holes) excluded.
  • MULTIPOINT / MULTILINESTRING / MULTIPOLYGON / GEOMETRYCOLLECTION inputs are unioned.

This is the single entry point for indexing geometries. (To go the other way — cell → geometry — see a5_cell_to_geometry.)

Parameters:

  • geom (GEOMETRY): Any geometry
  • resolution (INTEGER): Resolution level (0-30)

Note: Polygon coverings are returned compacted — cells may be at coarser resolutions where a parent is fully contained. Use a5_uncompact to expand to a uniform resolution. Also note that polygon fill is center-based, so features smaller than a cell (no cell center inside) yield no cells.

Examples:

-- Index a point, a line, and a polygon
SELECT a5_geometry_to_cells('POINT(-74.0 40.7)'::GEOMETRY, 8) as cell;
SELECT a5_geometry_to_cells('LINESTRING(-74.0 40.7, -73.9 40.8)'::GEOMETRY, 8) as line_cells;
SELECT a5_geometry_to_cells('POLYGON((-74.02 40.70, -73.95 40.70, -73.95 40.78, -74.02 40.78, -74.02 40.70))'::GEOMETRY, 10) as poly_cells;

-- Multi-geometries are unioned
SELECT a5_geometry_to_cells('MULTIPOINT((-74.0 40.7), (-73.9 40.8))'::GEOMETRY, 8) as cells;

-- Expand a polygon covering to a uniform resolution
SELECT a5_uncompact(a5_geometry_to_cells(g, 10), 10) FROM (SELECT 'POLYGON((-74.02 40.70, -73.95 40.70, -73.95 40.78, -74.02 40.78, -74.02 40.70))'::GEOMETRY AS g);

Polygon holes: Interior rings are excluded from the covering — the a5 crate removes them directly. At a uniform resolution this simply drops the cells the hole contains:

SELECT
    len(a5_uncompact(a5_geometry_to_cells('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'::GEOMETRY, 6), 6)) AS solid_cells,
    len(a5_uncompact(a5_geometry_to_cells('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (3 3, 7 3, 7 7, 3 7, 3 3))'::GEOMETRY, 6), 6)) AS with_hole_cells;
┌─────────────┬─────────────────┐
│ solid_cells │ with_hole_cells │
│    int64    │      int64      │
├─────────────┼─────────────────┤
│         150 │             126 │
└─────────────┴─────────────────┘

GEOMETRY Output

These functions turn A5 cells back into DuckDB GEOMETRY values, so cells become first-class spatial objects you can join, measure, and export (e.g. ST_AsGeoJSON, ST_Area, ST_Intersects).

a5_cell_to_geometry(cell_id [, segments]) -> GEOMETRY

Returns the cell pentagon as a POLYGON geometry. The optional segments argument interpolates each edge into that many segments (densification); omit it for the raw 5-vertex pentagon. The world cell (a5_world_cell()) returns POLYGON EMPTY.

Example:

SELECT ST_AsText(a5_cell_to_geometry(a5_lonlat_to_cell(-74.0060, 40.7128, 10))) as wkt;
-- POLYGON ((-73.9735... 40.6859..., ...))

This makes the GeoJSON example above a one-liner — a5_cell_to_geometry(cell) replaces the manual ST_MakePolygon(ST_MakeLine(list_transform(a5_cell_to_boundary(cell), ...))) construction.

a5_cell_to_point(cell_id) -> GEOMETRY

Returns the cell center as a POINT geometry.

Example:

SELECT ST_AsText(a5_cell_to_point(a5_lonlat_to_cell(-74.0060, 40.7128, 10))) as wkt;
-- POINT (-73.9790... 40.7074...)

Traversal Functions

a5_grid_disk(cell_id, k) -> UBIGINT[]

Returns all A5 cells within k edge-steps of the given cell (edge adjacency).

Parameters:

  • cell_id (UBIGINT): The center A5 cell
  • k (INTEGER): Number of edge-steps (must be >= 0)

Example:

SELECT a5_grid_disk(a5_lonlat_to_cell(-74.0060, 40.7128, 15), 1) as neighbors;

a5_grid_disk_vertex(cell_id, k) -> UBIGINT[]

Returns all A5 cells within k vertex-steps of the given cell (vertex adjacency). This returns more cells than a5_grid_disk at the same k because vertex adjacency includes cells that share only a vertex.

Parameters:

  • cell_id (UBIGINT): The center A5 cell
  • k (INTEGER): Number of vertex-steps (must be >= 0)

Example:

SELECT a5_grid_disk_vertex(a5_lonlat_to_cell(-74.0060, 40.7128, 15), 1) as neighbors;

a5_spherical_cap(cell_id, radius) -> UBIGINT[]

Returns all A5 cells within the specified radius (in meters) of the given cell.

Parameters:

  • cell_id (UBIGINT): The center A5 cell
  • radius (DOUBLE): Radius in meters

Example:

SELECT a5_spherical_cap(a5_lonlat_to_cell(-74.0060, 40.7128, 15), 5000.0) as nearby_cells;

Utility Functions

a5_get_num_cells(resolution) -> UBIGINT

Returns the total number of A5 cells at a given resolution level.

Example:

SELECT a5_get_num_cells(15) as total_cells;
┌─────────────────┐
│   total_cells   │
│     uint64      │
├─────────────────┤
│   16106127360   │
└─────────────────┘

a5_get_res0_cells() -> UBIGINT[]

Returns all 12 base cells at resolution level 0.

Example:

SELECT unnest(a5_get_res0_cells()) as base_cells;
┌─────────────────────┐
│     base_cells      │
│       uint64        │
├─────────────────────┤
│  144115188075855872 │
│  432345564227567616 │
│  720575940379279360 │
│ 1008806316530991104 │
│ 1297036692682702848 │
│ 1585267068834414592 │
│ 1873497444986126336 │
│ 2161727821137838080 │
│ 2449958197289549824 │
│ 2738188573441261568 │
│ 3026418949592973312 │
│ 3314649325744685056 │
├─────────────────────┤
│       12 rows       │
└─────────────────────┘

a5_world_cell() -> UBIGINT

Returns the A5 world cell, the root cell that covers the entire globe and is the ancestor of all resolution-0 cells. Its resolution is reported as -1 since it sits above resolution 0.

Example:

SELECT a5_world_cell() as world_cell;
┌────────────┐
│ world_cell │
│   uint64   │
├────────────┤
│     0      │
└────────────┘

a5_is_valid_cell(cell_id) -> BOOLEAN

Returns true if the value is a valid A5 cell — i.e. a canonically-encoded cell ID. Useful for guarding against malformed identifiers (e.g. values from another indexing system, or a BIGINT column that lost its encoding). The world cell (0) is considered valid.

The check decodes the ID and verifies it re-encodes to the same value, so it rejects out-of-range origins and non-canonical bit patterns. Note that A5's encoding is dense: most in-range 64-bit values decode to a genuine cell, so this is a structural well-formedness check, not a guarantee that a given number was intentionally produced.

Example:

SELECT a5_is_valid_cell(a5_lonlat_to_cell(-74.0060, 40.7128, 15)) as ok,
       a5_is_valid_cell((63::UBIGINT << 58)) as bad_origin;
┌─────────┬────────────┐
│   ok    │ bad_origin │
│ boolean │  boolean   │
├─────────┼────────────┤
│ true    │ false      │
└─────────┴────────────┘

a5_hex_to_u64(hex) -> UBIGINT

Converts an A5 hex string representation to a UBIGINT cell ID.

Example:

SELECT a5_hex_to_u64('1600000000000000') as cell_id;
┌─────────────────────┐
│       cell_id       │
│       uint64        │
├─────────────────────┤
│ 1585267068834414592 │
└─────────────────────┘

a5_u64_to_hex(cell_id) -> VARCHAR

Converts a UBIGINT A5 cell ID to its hex string representation.

Example:

SELECT a5_u64_to_hex(1585267068834414592::ubigint) as hex_id;
┌──────────────────┐
│      hex_id      │
│     varchar      │
├──────────────────┤
│ 1600000000000000 │
└──────────────────┘

a5_get_num_children(parent_resolution, child_resolution) -> UBIGINT

Returns the number of child cells at child_resolution that fit within a single cell at parent_resolution.

Example:

SELECT a5_get_num_children(0, 1) as num_children;
┌──────────────┐
│ num_children │
│    uint64    │
├──────────────┤
│            5 │
└──────────────┘

a5_compact(cell_ids) -> UBIGINT[]

Compacts a set of A5 cells by replacing complete groups of sibling cells with their parent cells.

Example:

SELECT a5_compact([324259173170675712, 396316767208603648, 468374361246531584, 540431955284459520]::ubigint[]) as result;
┌──────────────────────┐
│        result        │
│       uint64[]       │
├──────────────────────┤
│ [360287970189639680] │
└──────────────────────┘

a5_uncompact(cell_ids, target_resolution) -> UBIGINT[]

Expands a set of A5 cells to a target resolution by generating all descendant cells.

Example:

SELECT a5_uncompact([324259173170675712, 396316767208603648, 468374361246531584, 540431955284459520]::ubigint[], 2) as result;
┌──────────────────────────────────────────────────────────────────────────────────┐
│                                      result                                      │
│                                     uint64[]                                     │
├──────────────────────────────────────────────────────────────────────────────────┤
│ [324259173170675712, 396316767208603648, 468374361246531584, 540431955284459520] │
└──────────────────────────────────────────────────────────────────────────────────┘

🎯 Resolution Guide

ResolutionCell Area (approx)Use Case
0-542M km² - 33k km²Continental/Country analysis
6-108k km² - 130 km²Regional/State analysis
11-1532 km² - 32 hectaresCity/District analysis
16-208 hectares - 124 m²Neighborhood/Building analysis
21-2531 m² - 0.5 m²Room/Vehicle analysis
26-308 cm² - 0.03 mm²Precision measurements

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Credits