Skip to main content

DuckDB Quickstart

Get started with A5 in DuckDB by installing the extension and running a simple example.

Installation​

Install and load the A5 extension with this SQL:

INSTALL a5 from community;
LOAD a5;

-- Optional: Install spatial extension for geometry operations
INSTALL spatial;
LOAD spatial;

Example: Translate Lat/Lon to A5 Cell and back​

-- Get the A5 Cell for London
SELECT a5_lonlat_to_cell(-0.1276, 51.50735, 10) as cell;
┌─────────────────────┐
│        cell         │
│       uint64        │
├─────────────────────┤
│ 7176244663412588544 │
└─────────────────────┘

-- Get the center of the A5 cell previously returned
-- since cells cover a greater area, the returned lon/lat
-- will be different.
SELECT a5_cell_to_lonlat(7176244663412588544) AS lonlat;
┌───────────────────────────────────────────┐
│                  lonlat                   │
│                 double[2]                 │
├───────────────────────────────────────────┤
│ [-0.15971839880376137, 51.511842921513406]│
└───────────────────────────────────────────┘

Example: Generate GeoJSON for Cell​

To generate a GeoJSON polygon for the A5 cell above use this SQL along with DuckDB's spatial extension:

SELECT
    ST_AsGeoJSON(
        ST_MakePolygon(
            ST_MakeLine(
                list_transform(
                    a5_cell_to_boundary(7176244663412588544),
                    x -> ST_Point(x[1], x[2])
                )
            )
        )
    ) as g

This produces:

{
  "type":"Polygon",
  "coordinates":[
    [
      [-0.19250141916919006, 51.51946462334752],
      [-0.20078615946991363, 51.47558191837641],
      [-0.13985206278343298, 51.48472345047163],
      [-0.11104984728311251, 51.52438151810816],
      [-0.15436577597120050, 51.55503847320052],
      [-0.19250141916919006, 51.51946462334752]
    ]
  ]
}

Visualizing that A5 cell shows:

Example: Generate A5 Cells​

Here's a complete example that generates the A5 cells at a specified resolution.


SELECT unnest(a5_cell_to_children(7176244663412588544, 13)) AS cell_id;
┌─────────────────────┐
│       cell_id       │
│       uint64        │
├─────────────────────┤
│ 7176244122246709248 │
│ 7176244139426578432 │
│ 7176244156606447616 │
│ 7176244173786316800 │
│ 7176244190966185984 │
│ 7176244208146055168 │
│ ...                 │
├─────────────────────┤
│ 64 rows             │
└─────────────────────┘

Example: Compare Cell Areas​

This example shows how to obtain the cell area, cross-checking against the value from ST_Area_Spheroid().

Note that all cells at the same resolution have the exact same area. In general a5_cell_area() should always be used rather than manually computing the area

-- Compare the exact area (from a5_cell_area) with the estimated area
-- calculated from the cell boundary using ST_Area_Spheroid
WITH cells AS (
  SELECT
    unnest(generate_series(0, 10))::INTEGER AS resolution
),
areas AS (
  SELECT
    resolution,
    a5_lonlat_to_cell(-0.1276, 51.50735, resolution) AS cell,
    a5_cell_area(resolution) AS exact_area, -- Area constant within resolution level
    ST_Area_Spheroid(
      ST_MakePolygon(
        ST_MakeLine(
          list_transform(
            a5_cell_to_boundary(a5_lonlat_to_cell(-0.1276, 51.50735, resolution)),
            x-> ST_Point(x[2], x[1]) -- Swap to [lat, lon] for ST_Area_Spheroid
          )
        )
      )
    ) as estimated_area -- Quantized boundary will yield only estimate of area
  FROM cells
)
SELECT
  resolution,
  cell,
  exact_area,
  estimated_area,
  printf('%.4g%%', 100 * (estimated_area - exact_area) / exact_area) as area_error
FROM areas;
┌────────────┬─────────────────────┬────────────────────┬────────────────────┬─────────────┐
│ resolution │        cell         │     exact_area     │   estimated_area   │ area_error  │
│   int32    │       uint64        │       double       │       double       │   varchar   │
├────────────┼─────────────────────┼────────────────────┼────────────────────┼─────────────┤
│          0 │ 1297036692682702848 │  42505468731619.93 │  42505469418157.65 │ 1.615e-06%  │
│          1 │ 6989586621679009792 │  8501093746323.985 │  8501093267808.199 │ -5.629e-06% │
│          2 │ 7169730606773829632 │ 2125273436580.9963 │  2125259982148.427 │ -0.0006331% │
│          3 │ 7178737806028570624 │  531318359145.2491 │ 531312733699.80804 │ -0.001059%  │
│          4 │ 7176486006214885376 │ 132829589786.31229 │ 132841421743.74396 │ 0.008908%   │
│          5 │ 7183804355609362432 │ 33207397446.578068 │ 33204356296.555916 │ -0.009158%  │
│          6 │ 7184226568074428416 │  8301849361.644517 │  8300133697.437225 │ -0.02067%   │
│          7 │ 7176239715610263552 │ 2075462340.4111292 │ 2075718617.1494904 │ 0.01235%    │
│          8 │ 7176248511703285760 │  518865585.1027823 │ 518895962.34988785 │ 0.005855%   │
│          9 │ 7176246312680030208 │ 129716396.27569558 │ 129712619.13629723 │ -0.002912%  │
│         10 │ 7176244663412588544 │ 32429099.068923894 │  32428626.05546379 │ -0.001459%  │
├────────────┴─────────────────────┴────────────────────┴────────────────────┴─────────────┤
│ 11 rows                                                                        5 columns │
└──────────────────────────────────────────────────────────────────────────────────────────┘

Next Steps​