Spatial Pagination & Cursor Strategies

← Back to Core Geospatial API Architecture

Geospatial APIs routinely serve millions of features across dynamic bounding boxes, proximity searches, and spatial joins. Traditional OFFSET/LIMIT pagination collapses under these workloads due to index fragmentation, inconsistent row ordering, and expensive sequential scans. This guide details a production-ready implementation using FastAPI and PostGIS, focusing on cursor generation, spatial index alignment, and stateless API design that scales to tens of millions of rows without per-page latency growth.

# Decision Matrix: Offset vs Keyset vs Spatial Cursor

Before choosing a pagination strategy, map your access pattern against these three approaches:

The spatial cursor combines a floating-point spatial metric (distance or coordinate) with a stable unique key. The composite pair is the only reliable way to guarantee deterministic page boundaries when ST_Distance returns identical values for multiple features.

# Architecture: How Spatial Cursor Traversal Works

The diagram below shows the full request lifecycle. A client carries an opaque cursor token between pages; the server decodes it to a (distance, id) boundary, issues a keyset WHERE clause, and emits a new cursor from the last row of each response.

# Prerequisites & Environment

Before implementing spatial cursors, confirm these baseline requirements:

• FastAPI 0.100+ with uvicorn and Pydantic v2 models
• SQLAlchemy 2.0+ using asyncpg or psycopg async drivers
• PostGIS 3.2+ with GiST indexing enabled on geometry columns
• Python 3.10+ for union-type annotations (str | None)
• orjson or msgspec for high-throughput GeoJSON serialization

Your spatial table must carry a properly tuned GiST index on the geometry column. Verify index health with pg_stat_user_indexes and ensure work_mem is sized for spatial sort operations (128 MB or more on large datasets). For geometry column design and SRID conventions, follow the Spatial Resource Modeling Patterns baseline.

# Why Offset/Limit Fails in Geospatial Contexts

Offset-based pagination assumes a stable, linear ordering. Spatial queries violate this in three ways:

1. Non-deterministic ordering. Functions like ST_Distance return floating-point distances. Ties in distance values cause row shuffling between requests, producing duplicate results or silently missing features at page boundaries.
2. Index bypass. OFFSET n forces the planner to scan and discard n rows before returning the next batch. GiST indexes cannot skip scanned tuples, so cost grows linearly with page depth.
3. Dynamic spatial filters. When users pan, zoom, or adjust proximity radii, the underlying result set shifts. Page numbers become meaningless and the client must restart from zero, multiplying database load.

Cursor pagination resolves these issues by encoding the exact boundary position of the last returned record into a stateless token. The next request uses that token to resume scanning directly from the index boundary, guaranteeing O(log n) traversal and consistent ordering regardless of dataset size.

# Core Principles of Spatial Cursor Design

A robust spatial cursor system depends on three design rules:

• Composite determinism. Distance values alone are insufficient. Cursors must combine the spatial metric with a stable unique identifier (UUID or auto-incrementing integer) to break ties among equidistant geometries.
• Stateless opacity. Clients must never parse cursor contents. Tokens must be URL-safe, versioned, and tamper-resistant to prevent injection or state leakage.
• Index boundary alignment. The cursor must map directly to the ORDER BY sort keys. Misalignment forces the query planner to use temporary tables or disk-based sorts instead of GiST seeks.

# Step-by-Step Implementation

# Step 1: Schema Configuration & Index Alignment

Define a composite index that matches your primary access pattern. For proximity searches, pair the geometry GiST index with a B-tree index on the primary key:

-- GiST index for spatial operations and KNN ordering
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- B-tree index for deterministic keyset tiebreaking
CREATE INDEX idx_locations_id ON locations USING btree (id);

Use GEOMETRY(Point, 4326) or the geography type on your geom column. The geography type computes accurate spherical distances in metres without an explicit ST_Transform, which simplifies cursor value comparison. Avoid storing pre-calculated distances as a column; compute them at query time to maintain index selectivity.

For a complete schema design reference including multi-geometry tables and SRID conventions, see Spatial Resource Modeling Patterns.

# Step 2: Secure Cursor Encoding & Decoding

Cursors should be opaque, URL-safe, and versioned. Encode the composite key as JSON, compress lightly for large payloads, then Base64URL-encode the result:

import base64
import json
import zlib
from typing import Any, Tuple


def encode_cursor(*keys: Any, version: int = 1) -> str:
    """Encode composite sort keys into a URL-safe, versioned cursor token."""
    payload = json.dumps({"v": version, "k": list(keys)}).encode("utf-8")
    compressed = zlib.compress(payload, level=1)
    # Strip trailing '=' padding; restore it on decode
    return base64.urlsafe_b64encode(compressed).decode("utf-8").rstrip("=")


def decode_cursor(cursor: str) -> Tuple[int, Tuple[Any, ...]]:
    """Decode and validate a cursor token; raises ValueError on tampered input."""
    # Restore stripped padding
    padded = cursor + "=" * (4 - len(cursor) % 4)
    try:
        compressed = base64.urlsafe_b64decode(padded)
        payload = json.loads(zlib.decompress(compressed))
    except Exception as exc:
        raise ValueError(f"Invalid cursor: {exc}") from exc
    if payload.get("v") != 1:
        raise ValueError(f"Unsupported cursor version: {payload.get('v')}")
    return payload["v"], tuple(payload["k"])

In FastAPI, wrap decode_cursor in a dependency that raises HTTPException(status_code=400) on ValueError. Never let raw cursor errors surface to clients.

# Step 3: Query Construction with SQLAlchemy & PostGIS

Build async queries that use ST_Distance for deterministic ordering and keyset WHERE boundaries. The boundary condition requires an OR clause — not AND — to correctly advance past tied distances:

from sqlalchemy import and_, func, or_, select
from sqlalchemy.ext.asyncio import AsyncSession

from .models import Location
from .cursors import decode_cursor, encode_cursor


async def fetch_proximity_page(
    session: AsyncSession,
    lat: float,
    lon: float,
    limit: int = 50,
    cursor: str | None = None,
) -> dict:
    """Return one page of locations ordered by proximity, with a next-cursor."""
    ref = func.ST_SetSRID(func.ST_MakePoint(lon, lat), 4326)
    distance_expr = func.ST_Distance(
        func.ST_Transform(ref, 3857),
        func.ST_Transform(Location.geom, 3857),
    )

    stmt = (
        select(Location, distance_expr.label("dist"))
        .order_by(distance_expr, Location.id)
        .limit(limit)
    )

    if cursor:
        _, (last_dist, last_id) = decode_cursor(cursor)
        # Rows where distance is strictly greater, OR same distance and higher id
        stmt = stmt.where(
            or_(
                distance_expr > last_dist,
                and_(distance_expr == last_dist, Location.id > last_id),
            )
        )

    result = await session.execute(stmt)
    rows = result.all()  # list of (Location, dist) tuples

    next_cursor = None
    if rows:
        last_loc, last_dist_val = rows[-1]
        next_cursor = encode_cursor(float(last_dist_val), last_loc.id)

    return {"items": [r[0] for r in rows], "next_cursor": next_cursor}

The ST_Transform to EPSG:3857 converts distances to metres for consistent cursor comparison regardless of input geometry units. For bounding box scans that use bounding box spatial index queries instead of proximity, replace ST_Distance ordering with a stable primary key sort — the cursor then encodes only the id value.

For deep optimisation of the KNN operator and <-> vs <#> trade-offs, see Optimizing KNN Queries with the PostGIS Operator.

# Step 4: Response Serialization & Next-Cursor Injection

Define strict Pydantic models and wire them into the FastAPI route. Attach next_cursor so clients can request the following page without any server state:

from typing import Optional
from pydantic import BaseModel, Field
from fastapi import APIRouter, Depends, HTTPException, Query

router = APIRouter()


class LocationFeature(BaseModel):
    id: int
    name: str
    geom_geojson: dict  # serialized with ST_AsGeoJSON at query time

    model_config = {"from_attributes": True}


class ProximityPage(BaseModel):
    items: list[LocationFeature]
    next_cursor: Optional[str] = Field(None, description="Opaque token for next page")
    has_more: bool


@router.get("/locations/nearby", response_model=ProximityPage)
async def get_nearby_locations(
    lat: float = Query(..., ge=-90, le=90),
    lon: float = Query(..., ge=-180, le=180),
    limit: int = Query(50, ge=1, le=200),
    cursor: Optional[str] = Query(None),
    session: AsyncSession = Depends(get_session),
) -> ProximityPage:
    try:
        data = await fetch_proximity_page(session, lat, lon, limit, cursor)
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc))

    return ProximityPage(
        items=[LocationFeature.model_validate(loc) for loc in data["items"]],
        next_cursor=data["next_cursor"],
        has_more=data["next_cursor"] is not None,
    )

When response payload size is a concern — especially for large feature collections — evaluate whether binary formats reduce wire cost. The GeoJSON vs GeoParquet Serialization decision matrix covers the trade-offs between text-based GeoJSON and columnar formats for cursor-paginated endpoints.

For API versioning on cursor endpoints, encode the cursor version ("v": 1) so you can introduce new sort keys in a future API version without breaking existing client tokens.

# Production Code Example

The following is a self-contained, copy-runnable FastAPI application demonstrating the full spatial cursor pattern with a geometry table and async session:

"""
Full spatial cursor pagination example.
Requires: fastapi, uvicorn, sqlalchemy[asyncio], asyncpg, geoalchemy2
PostGIS table: CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    geom GEOMETRY(Point, 4326) NOT NULL
);
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);
"""
import base64
import json
import zlib
from contextlib import asynccontextmanager
from typing import Any, Optional, Tuple

from fastapi import FastAPI, HTTPException, Query
from geoalchemy2 import Geometry
from pydantic import BaseModel
from sqlalchemy import Column, Integer, String, Text, func, and_, or_, select, text
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase


DATABASE_URL = "postgresql+asyncpg://user:password@localhost/gisdb"
engine = create_async_engine(DATABASE_URL, pool_size=10, max_overflow=5)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)


class Base(DeclarativeBase):
    pass


class Location(Base):
    __tablename__ = "locations"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    geom = Column(Geometry("POINT", srid=4326), nullable=False)


# --- Cursor helpers ---

def encode_cursor(*keys: Any, version: int = 1) -> str:
    payload = json.dumps({"v": version, "k": list(keys)}).encode()
    return base64.urlsafe_b64encode(zlib.compress(payload, 1)).decode().rstrip("=")


def decode_cursor(token: str) -> Tuple[int, tuple]:
    padded = token + "=" * (4 - len(token) % 4)
    try:
        payload = json.loads(zlib.decompress(base64.urlsafe_b64decode(padded)))
    except Exception as exc:
        raise ValueError(f"Malformed cursor: {exc}") from exc
    if payload.get("v") != 1:
        raise ValueError("Unsupported cursor version")
    return payload["v"], tuple(payload["k"])


# --- Pydantic schemas ---

class NearbyItem(BaseModel):
    id: int
    name: str
    distance_m: float


class NearbyPage(BaseModel):
    items: list[NearbyItem]
    next_cursor: Optional[str]
    has_more: bool


# --- Application ---

@asynccontextmanager
async def lifespan(app: FastAPI):
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    yield

app = FastAPI(lifespan=lifespan)


@app.get("/api/v1/locations/nearby", response_model=NearbyPage)
async def nearby(
    lat: float = Query(..., ge=-90, le=90),
    lon: float = Query(..., ge=-180, le=180),
    limit: int = Query(50, ge=1, le=200),
    cursor: Optional[str] = Query(None),
):
    ref = func.ST_SetSRID(func.ST_MakePoint(lon, lat), 4326)
    dist_m = func.ST_Distance(
        func.ST_Transform(ref, 3857),
        func.ST_Transform(Location.geom, 3857),
    ).label("distance_m")

    stmt = select(Location, dist_m).order_by(dist_m, Location.id).limit(limit)

    if cursor:
        try:
            _, (last_dist, last_id) = decode_cursor(cursor)
        except ValueError as exc:
            raise HTTPException(status_code=400, detail=str(exc))
        dist_expr = func.ST_Distance(
            func.ST_Transform(ref, 3857),
            func.ST_Transform(Location.geom, 3857),
        )
        stmt = stmt.where(
            or_(
                dist_expr > last_dist,
                and_(dist_expr == last_dist, Location.id > last_id),
            )
        )

    async with SessionLocal() as session:
        rows = (await session.execute(stmt)).all()

    items = [
        NearbyItem(id=loc.id, name=loc.name, distance_m=round(dist, 2))
        for loc, dist in rows
    ]
    next_cursor = (
        encode_cursor(float(rows[-1][1]), rows[-1][0].id) if rows else None
    )
    return NearbyPage(items=items, next_cursor=next_cursor, has_more=next_cursor is not None)

# Verification & Testing

# curl Walkthrough

Fetch the first page, then use next_cursor to retrieve the second:

# First page (no cursor)
curl -s "http://localhost:8000/api/v1/locations/nearby?lat=51.5074&lon=-0.1278&limit=5" | jq .

# Second page using returned next_cursor token
CURSOR=$(curl -s "http://localhost:8000/api/v1/locations/nearby?lat=51.5074&lon=-0.1278&limit=5" \
  | jq -r '.next_cursor')
curl -s "http://localhost:8000/api/v1/locations/nearby?lat=51.5074&lon=-0.1278&limit=5&cursor=${CURSOR}" | jq .

Confirm that no id appears in both responses, and that the minimum distance_m on the second page is greater than or equal to the maximum on the first.

# EXPLAIN ANALYZE

Verify the query planner uses the GiST index rather than a sequential scan:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, name,
       ST_Distance(
           ST_Transform(ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326), 3857),
           ST_Transform(geom, 3857)
       ) AS distance_m
FROM locations
WHERE ST_Distance(
          ST_Transform(ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326), 3857),
          ST_Transform(geom, 3857)
      ) > 1234.56
   OR (
      ST_Distance(
          ST_Transform(ST_SetSRID(ST_MakePoint(-0.1278, 51.5074), 4326), 3857),
          ST_Transform(geom, 3857)
      ) = 1234.56 AND id > 999
   )
ORDER BY distance_m, id
LIMIT 50;

Expected output should include Index Scan using idx_locations_geom and Buffers: shared hit=… with a low heap-fetch count. If you see Seq Scan instead, confirm work_mem is at least 64 MB and that the GiST index exists on the transformed geometry. For a detailed walkthrough of query plan analysis, see Reading EXPLAIN ANALYZE for Spatial Query Optimization.

# Unit Test Skeleton

import pytest
from .cursors import decode_cursor, encode_cursor


def test_cursor_round_trip():
    token = encode_cursor(1234.567, 42)
    version, keys = decode_cursor(token)
    assert version == 1
    assert keys == (1234.567, 42)


def test_cursor_rejects_tampered_token():
    with pytest.raises(ValueError, match="Malformed cursor"):
        decode_cursor("not-a-real-cursor")


def test_cursor_rejects_wrong_version():
    import base64, json, zlib
    payload = json.dumps({"v": 99, "k": [1.0, 1]}).encode()
    token = base64.urlsafe_b64encode(zlib.compress(payload, 1)).decode().rstrip("=")
    with pytest.raises(ValueError, match="Unsupported cursor version"):
        decode_cursor(token)

# Failure Modes & Edge Cases

1. Float precision drift in cursor comparison. ST_Distance in EPSG:3857 returns double-precision floats. Serialising to JSON loses sub-nanometre precision. In practice this is safe; the composite (dist, id) tiebreaker guarantees forward progress even if two rows compare equal on distance. Do not use NUMERIC cast on the cursor value — it makes equality comparisons unreliable across parameter changes.

2. Cursor used with changed query parameters. A cursor is valid only for the same lat, lon, and limit combination. If a client sends a mismatched filter, the keyset boundary is meaningless. Return HTTP 400 with: {"detail": "Cursor is invalid for the current query parameters. Restart from the first page."}.

3. Empty last page and has_more false positive. If the last page returns exactly limit rows, next_cursor will be non-null but the following request will return zero rows. Clients must handle an empty items array gracefully, even when has_more is true. Some teams reduce limit by 1 and use the extra slot as a lookahead.

4. GiST index bloat after heavy writes. GiST indexes degrade under high-write workloads. Monitor pg_stat_user_indexes for bloat; schedule VACUUM (ANALYZE) nightly and run REINDEX INDEX CONCURRENTLY idx_locations_geom during maintenance windows if traversal latency spikes unexpectedly.

5. Cursor replay and abuse. Stateless cursors can be replayed indefinitely. If your threat model requires expiration, embed a Unix timestamp in the payload and reject tokens older than a configurable TTL. Apply rate limiting on cursor endpoints independently from first-page requests — cursor-driven scraping is faster than offset scraping.

6. Null geometry rows breaking sort order. Rows with NULL in the geometry column cause ST_Distance to return NULL, which sorts last in PostgreSQL. Add a WHERE geom IS NOT NULL constraint to the query and enforce NOT NULL at the schema level.

# Performance Notes

Key levers:

• work_mem: raise from the PostgreSQL default (4 MB) to at least 64–128 MB for sessions running spatial sort operations. Set it per-connection via SET LOCAL work_mem = '128MB' in a SQLAlchemy @event.listens_for(engine, "connect") hook to avoid global impact.
• Async sessions: each FastAPI request should borrow a pooled connection from asyncpg. Avoid synchronous session.execute() calls in async routes — they block the event loop.
• Connection pool sizing: use pgBouncer in transaction mode for high-concurrency deployments. See Connection Pooling & pgBouncer Setup for spatial-workload pool configuration.
• Avoid SELECT *: always project only the columns you need. Fetching the geometry column as WKB and converting client-side is 30–60% slower than using ST_AsGeoJSON at the SQL layer.

# Frequently Asked Questions

# Related

• Implementing Cursor-Based Pagination for Spatial Queries — deep implementation walkthrough with async connection patterns
• GeoJSON vs GeoParquet Serialization — choose the right response format for paginated spatial endpoints
• Spatial Resource Modeling Patterns — geometry column design and SRID conventions
• API Versioning for GIS Endpoints — version cursor tokens alongside API changes
• Bounding Box Spatial Index Queries — cursor pagination adapted for ST_Within and ST_Intersects scans

← Back to Core Geospatial API Architecture