Query Result Caching for Streamlit and Panel Spatial Dashboards

Spatial dashboards execute some of the most expensive database operations in the Python analytics ecosystem: bounding box filters across millions of geometries, spatial joins between parcel boundaries and watershed polygons, raster band extractions, and network routing matrices. Without a caching layer, every panel re-render or dropdown change fires a redundant round-trip to PostGIS or DuckDB, saturating connection pools and making the UI feel sluggish under even modest concurrent load. The problem compounds when a widget re-render that should be cheap instead retriggers the full query path — the interaction between caching and the widget lifecycle is where most spatial dashboards lose their responsiveness.

This page covers the full caching workflow for spatial query results inside Streamlit and Panel applications — from identifying what is worth caching, through deterministic key construction, storage backend selection, and serialization, to the invalidation policies and observability instrumentation that keep a production dashboard reliable. It sits under Caching Strategies & Async Performance Tuning and works hand-in-hand with @st.cache_data Implementation for decorator-level configuration and Memory Limit Management for keeping cached GeoDataFrame payloads within a container’s memory ceiling.

Jump to heading# Architecture overview

The diagram below shows where the caching layer sits between user interaction and the spatial database. Cache hits bypass the database entirely; misses hydrate the cache before returning to the caller. A stampede guard (lock or semaphore) prevents the thundering-herd problem when the cache is cold and multiple sessions request the same geometry simultaneously.

Jump to heading# Prerequisites

Before implementing query result caching in a spatial dashboard, confirm the following are in place:

• Python 3.10+ with streamlit>=1.32 or panel>=1.3
• Geospatial stack: geopandas>=0.14, shapely>=2.0, sqlalchemy>=2.0, and either psycopg2-binary or asyncpg for database connectivity
• Spatial database access: PostgreSQL with PostGIS, DuckDB with the spatial extension, or SQLite/SpatiaLite
• CRS fundamentals: familiarity with EPSG codes (e.g., EPSG:4326 for WGS84, EPSG:3857 for Web Mercator) — covered in the Async Data Loading Patterns page
• Serialization awareness: understanding of Pickle’s per-process scope, WKB/WKT string formats, and the session isolation model described in Session State Patterns

Jump to heading# Core implementation workflow

Jump to heading# Step 1 — Identify cacheable query boundaries

Not every spatial operation benefits from caching. Profile your dashboard first to isolate deterministic, computationally expensive queries. Strong candidates share two traits: they are expensive (execution time > 500 ms in query logs) and their inputs repeat across users or page refreshes.

Prime candidates:

• Static or semi-static choropleth aggregations (e.g., census tract summaries updated weekly)
• Pre-computed spatial joins: parcel-to-watershed mapping, address-to-zone lookups
• Raster tile extractions at fixed zoom levels
• Network routing matrices with static edge weights

Do not cache: real-time sensor feeds that update every few seconds, user-drawn polygons unique to a session, or queries that depend on rapidly changing external APIs. If a result is volatile, cap the TTL at 60–300 seconds rather than skipping caching entirely — even a short window reduces database load dramatically under concurrent users.

Jump to heading# Step 2 — Construct deterministic cache keys

Framework-level hashing (the default for @st.cache_data and @pn.cache) breaks silently on geospatial inputs because:

• Floating-point coordinate drift (differences at the 12th decimal place) produces different hashes for logically identical geometries.
• MultiPolygon vertex ordering changes between library versions.
• GeoDataFrame equality is expensive and non-deterministic across Python sessions.

Build cache keys manually. Normalize geometries to a canonical CRS, round coordinates to a precision that matters for your use case, serialize to WKT, and hash the resulting string:

import hashlib
import geopandas as gpd
from shapely.geometry import shape
from shapely.ops import transform
import pyproj

def _normalize_geom_wkt(geom, target_crs: str = "EPSG:4326", precision: int = 6) -> str:
    """Reproject to target_crs and round coords before WKT serialization."""
    project = pyproj.Transformer.from_crs(
        "EPSG:4326", target_crs, always_xy=True
    ).transform
    projected = transform(project, geom)
    # Round to `precision` decimal places to absorb floating-point drift
    rounded_wkt = projected.wkt  # shapely>=2.0 rounds on export
    return rounded_wkt

def build_spatial_cache_key(
    geom,
    crs: str,
    start_date: str,
    end_date: str,
    layer_id: str,
) -> str:
    normalized_wkt = _normalize_geom_wkt(geom, target_crs=crs)
    canonical = f"{normalized_wkt}|{crs}|{start_date}|{end_date}|{layer_id}"
    return hashlib.sha256(canonical.encode()).hexdigest()

Pass the resulting hex string as a plain str argument to the caching decorator rather than passing the raw geometry. This sidesteps framework hashing entirely.

Jump to heading# Step 3 — Select and configure a storage backend

Storage backend choice depends on your deployment topology and the memory budget covered in Memory Limit Management.

For Redis backends, avoid raw Pickle (version-sensitive across worker processes). Instead, serialize GeoDataFrame to Parquet bytes via BytesIO and store the bytes:

import io
import redis
import geopandas as gpd

_pool = redis.ConnectionPool(host="localhost", port=6379, db=0)

def cache_geodataframe(key: str, gdf: gpd.GeoDataFrame, ttl_seconds: int = 3600) -> None:
    buf = io.BytesIO()
    gdf.to_parquet(buf, index=False)
    r = redis.Redis(connection_pool=_pool)
    r.setex(key, ttl_seconds, buf.getvalue())

def read_cached_geodataframe(key: str) -> gpd.GeoDataFrame | None:
    r = redis.Redis(connection_pool=_pool)
    raw = r.get(key)
    if raw is None:
        return None
    return gpd.read_parquet(io.BytesIO(raw))

Configure Redis maxmemory-policy to allkeys-lru so the server evicts least-recently-used entries automatically rather than blocking on memory exhaustion.

Jump to heading# Step 4 — Implement framework-native caching decorators

For single-process Streamlit, wrap query functions with @st.cache_data. Pass only primitive, hashable arguments (strings, ints, tuples) — never raw geometry objects:

import streamlit as st
import geopandas as gpd
from sqlalchemy import create_engine, text

@st.cache_data(ttl=3600, show_spinner="Fetching administrative boundaries…")
def fetch_admin_boundaries(region_key: str, crs: str = "EPSG:4326") -> gpd.GeoDataFrame:
    engine = create_engine("postgresql+psycopg2://user:pass@db:5432/gis")
    sql = "SELECT geom, name, region_id FROM boundaries WHERE region_id = :rid"
    with engine.connect() as conn:
        gdf = gpd.read_postgis(
            text(sql), conn, geom_col="geom", params={"rid": region_key}
        )
    return gdf.to_crs(crs)

region_key must be a pre-validated string, not raw user input. Always use parameterized queries (text() with params=) to prevent SQL injection — the database connection here has no awareness of the cache.

For Panel, bind @pn.cache to a reactive function that takes hashable widget values rather than widget objects:

import panel as pn
import geopandas as gpd
from sqlalchemy import create_engine, text

pn.extension()

region_select = pn.widgets.Select(name="Region", options=["north", "south", "east"])

@pn.cache(max_items=20, ttl=3600)
def load_region_gdf(region: str, crs: str = "EPSG:3857") -> gpd.GeoDataFrame:
    engine = create_engine("postgresql+psycopg2://user:pass@db:5432/gis")
    with engine.connect() as conn:
        gdf = gpd.read_postgis(
            text("SELECT geom, name FROM regions WHERE slug = :slug"),
            conn, geom_col="geom", params={"slug": region},
        )
    return gdf.to_crs(crs)

@pn.depends(region_select)
def map_view(region):
    gdf = load_region_gdf(region)
    return gdf.explore()

For async dashboards, pair caching with Async Data Loading Patterns to avoid blocking the event loop during cache misses — the concurrent map-tile loading walkthrough shows the same lock-per-key pattern applied to tile fetches. Wrap async fetchers in asyncio.Lock per cache key to prevent stampedes:

import asyncio
import asyncpg
import geopandas as gpd

_locks: dict[str, asyncio.Lock] = {}
_cache: dict[str, gpd.GeoDataFrame] = {}

async def fetch_with_lock(cache_key: str, query: str, bind_params: dict) -> gpd.GeoDataFrame:
    if cache_key in _cache:
        return _cache[cache_key]
    lock = _locks.setdefault(cache_key, asyncio.Lock())
    async with lock:
        if cache_key in _cache:   # re-check after acquiring lock
            return _cache[cache_key]
        conn = await asyncpg.connect("postgresql://user:pass@db:5432/gis")
        try:
            rows = await conn.fetch(query, *bind_params.values())
        finally:
            await conn.close()
        gdf = gpd.GeoDataFrame.from_records(rows, geometry="geom")
        _cache[cache_key] = gdf
        return gdf

Jump to heading# Step 5 — Optimize spatial serialization and memory footprint

A GeoDataFrame of 50,000 complex watershed polygons can exceed 200 MB in memory. Before storing in the cache, strip unused columns and downcast numeric dtypes:

def slim_for_cache(gdf: gpd.GeoDataFrame, keep_cols: list[str]) -> gpd.GeoDataFrame:
    """Drop non-essential columns and repair invalid geometries before caching."""
    gdf = gdf[keep_cols + ["geometry"]].copy()
    # Repair self-intersecting rings
    invalid_mask = ~gdf.geometry.is_valid
    if invalid_mask.any():
        gdf.loc[invalid_mask, "geometry"] = gdf.loc[invalid_mask, "geometry"].buffer(0)
    # Drop geometries that are still invalid after repair
    gdf = gdf[gdf.geometry.is_valid].reset_index(drop=True)
    # Downcast float columns to float32 to halve memory
    for col in gdf.select_dtypes("float64").columns:
        gdf[col] = gdf[col].astype("float32")
    return gdf

When the cache backend is disk-based, compress with zlib at level 6 — a reasonable balance between compression ratio and CPU cost. Benchmark: for a 50 MB Parquet buffer, zlib level 6 typically reduces payload to ~18 MB in under 80 ms, well within acceptable overhead for queries that take seconds to recompute.

For WKB serialization at the geometry column level when using Redis:

import zlib
import io

def compress_gdf(gdf: gpd.GeoDataFrame) -> bytes:
    buf = io.BytesIO()
    gdf.to_parquet(buf, index=False, compression=None)  # compress externally
    return zlib.compress(buf.getvalue(), level=6)

def decompress_gdf(data: bytes) -> gpd.GeoDataFrame:
    return gpd.read_parquet(io.BytesIO(zlib.decompress(data)))

Jump to heading# Step 6 — Establish cache invalidation and TTL policies

Static TTLs suit stable datasets: administrative boundaries updated once a quarter need a TTL of 86400 (24 h) or longer. For data with irregular update schedules, layer version tracking into the cache key itself:

import hashlib

def versioned_cache_key(base_key: str, dataset_version: str) -> str:
    """Embed a dataset version so stale keys expire automatically."""
    return hashlib.sha256(f"{base_key}|v={dataset_version}".encode()).hexdigest()

Store the current dataset version in a lightweight metadata table and read it once at application startup — then any data refresh increments the version and old keys are never fetched again (they expire via TTL).

For dynamic spatial queries — user-drawn polygons, real-time buffer expansions, sliding time windows — TTL-based expiry alone is insufficient. See Fixing cache invalidation for dynamic spatial queries for coordinate rounding strategies and event-driven invalidation patterns that handle parameterized geometries without cache explosion.

Jump to heading# Advanced patterns

Jump to heading# Redis-backed shared cache with connection pooling

When multiple Streamlit workers run behind a load balancer, each process has its own in-memory @st.cache_data silo. Move to a Redis-backed shared cache so all workers benefit from the same warm entries:

import functools
import io
import redis
import geopandas as gpd

_redis = redis.Redis(host="redis", port=6379, decode_responses=False)

def shared_spatial_cache(ttl: int = 3600):
    """Decorator: check Redis first, fall back to executing the wrapped function."""
    def decorator(fn):
        @functools.wraps(fn)
        def wrapper(*args, **kwargs):
            key = f"sp:{fn.__name__}:{args}:{sorted(kwargs.items())}"
            raw = _redis.get(key)
            if raw:
                return gpd.read_parquet(io.BytesIO(raw))
            result = fn(*args, **kwargs)
            buf = io.BytesIO()
            result.to_parquet(buf, index=False)
            _redis.setex(key, ttl, buf.getvalue())
            return result
        return wrapper
    return decorator

@shared_spatial_cache(ttl=7200)
def load_census_tracts(state_fips: str, crs: str = "EPSG:4326") -> gpd.GeoDataFrame:
    # ... query PostGIS ...
    pass

Jump to heading# Lazy GeoDataFrame loading with partial caching

For very large datasets where full GeoDataFrame caching exceeds memory limits (see Memory Limit Management), cache only the bounding box index (a lightweight spatial grid) and load full geometry on demand:

import geopandas as gpd
import pandas as pd
from shapely.geometry import box

@st.cache_data(ttl=86400)
def load_feature_index(table: str, crs: str = "EPSG:4326") -> pd.DataFrame:
    """Cache the bounding-box index only (~1% of full dataset size)."""
    engine = create_engine("postgresql+psycopg2://user:pass@db:5432/gis")
    sql = f"""
        SELECT id, ST_XMin(geom) AS xmin, ST_YMin(geom) AS ymin,
               ST_XMax(geom) AS xmax, ST_YMax(geom) AS ymax
        FROM {table}
    """
    with engine.connect() as conn:
        return pd.read_sql(text(sql), conn)

def fetch_visible_features(table: str, viewport_bbox: tuple, crs: str = "EPSG:4326") -> gpd.GeoDataFrame:
    """Use the cached index to narrow what is fetched from the DB at full resolution."""
    idx = load_feature_index(table, crs)
    minx, miny, maxx, maxy = viewport_bbox
    candidate_ids = idx[
        (idx.xmax >= minx) & (idx.xmin <= maxx) &
        (idx.ymax >= miny) & (idx.ymin <= maxy)
    ]["id"].tolist()
    if not candidate_ids:
        return gpd.GeoDataFrame()
    engine = create_engine("postgresql+psycopg2://user:pass@db:5432/gis")
    sql = f"SELECT * FROM {table} WHERE id = ANY(:ids)"
    with engine.connect() as conn:
        return gpd.read_postgis(text(sql), conn, geom_col="geom", params={"ids": candidate_ids})

Jump to heading# Cross-tab cache coherence in Streamlit multi-tab sessions

Streamlit gives each browser tab its own session state. If a user refreshes data in one tab, the other tab’s cached reference is stale. Coordinate invalidation by storing a version token in a shared Redis key and checking it on each rerun:

import streamlit as st
import redis
import time

_redis = redis.Redis(host="redis", port=6379)

def get_dataset_version(dataset_id: str) -> str:
    raw = _redis.get(f"version:{dataset_id}")
    return raw.decode() if raw else "0"

def invalidate_dataset(dataset_id: str) -> None:
    _redis.set(f"version:{dataset_id}", str(time.time()))

# In the dashboard rerun path:
version = get_dataset_version("watershed_zones")
if st.session_state.get("watershed_version") != version:
    st.cache_data.clear()
    st.session_state["watershed_version"] = version

Jump to heading# Verification and testing

Confirm the caching layer works correctly before deploying to production:

import streamlit as st
import geopandas as gpd
import time

@st.cache_data(ttl=300)
def timed_spatial_query(region_key: str) -> gpd.GeoDataFrame:
    # ... your real query ...
    return gpd.GeoDataFrame()

# Measure cold vs warm path
start = time.perf_counter()
gdf_cold = timed_spatial_query("NL")
cold_ms = (time.perf_counter() - start) * 1000

start = time.perf_counter()
gdf_warm = timed_spatial_query("NL")
warm_ms = (time.perf_counter() - start) * 1000

assert warm_ms < cold_ms * 0.1, f"Cache miss on warm path (cold={cold_ms:.0f}ms, warm={warm_ms:.0f}ms)"
assert gdf_cold.crs == gdf_warm.crs, "CRS mismatch between cold and warm results"
assert len(gdf_cold) == len(gdf_warm), "Row count mismatch: serialization may have dropped features"

Profile memory retention after repeated calls:

import tracemalloc

tracemalloc.start()
for region in ["NL", "BE", "DE", "FR"]:
    timed_spatial_query(region)
current, peak = tracemalloc.get_traced_memory()
tracemalloc.stop()
print(f"Peak memory for 4 cached regions: {peak / 1e6:.1f} MB")
# Expect < 200 MB for typical administrative boundary datasets

For Panel, use pn.state.cache inspection to assert entries are populated:

import panel as pn

load_region_gdf("north")
assert "north" in str(pn.state.cache), "Panel cache miss — check @pn.cache decorator arguments"

Jump to heading# Troubleshooting

UnhashableTypeError: unhashable type: 'GeoDataFrame' when calling a cached function : You passed a raw GeoDataFrame as an argument. Replace it with a string cache key computed via build_spatial_cache_key() (Step 2). Framework decorators hash all arguments by default; geometry objects are not hashable.

Cache always misses despite identical inputs : Floating-point geometry coordinates differ at the tail digits. Normalize with geom.wkt after re-projecting to a fixed CRS, or round coordinates to 6 decimal places before serialization. Also check that the CRS string is always canonical ("EPSG:4326", not "WGS84" or "epsg:4326") — string comparison is case-sensitive.

ModuleNotFoundError or AttributeError on Pickle deserialization from disk cache : The cached Pickle was written by a different Python environment or library version. Clear the cache directory (st.cache_data.clear() or delete the disk cache folder) and repopulate. Switch to Parquet-based serialization (Steps 3 and 5) to avoid Pickle version binding entirely.

Redis WRONGTYPE Operation against a key holding the wrong kind of value : A prior key was stored as a Redis string but your code attempted a hash (HSET) operation, or vice versa. Namespace your keys with a prefix (sp:gdf:) and ensure all cache writers use the same data type. Flush the namespace with redis-cli --scan --pattern "sp:*" | xargs redis-cli del after changing the storage format.

MemoryError or Streamlit OOM crash on cache warm-up : Too many large GeoDataFrame objects accumulate simultaneously. Apply slim_for_cache() (Step 5) before storing, reduce max_entries on @pn.cache, or switch to the lazy bounding-box index pattern in the Advanced patterns section. The Memory Limit Management page provides container-level limits and eviction configuration.

Jump to heading# Performance considerations

Payload size thresholds: Keep individual cached entries below 50 MB. Entries above this threshold take >100 ms to deserialize from disk, which approaches the perceived-latency threshold for interactive dashboards. If your polygons are complex, simplify with gdf.geometry.simplify(tolerance=0.0001, preserve_topology=True) before caching display-only datasets.

Cache key collision risk: SHA-256 produces 64 hex characters; collision probability for the number of cache entries typical in a spatial dashboard (< 10,000) is effectively zero. Do not truncate to fewer than 16 characters.

Async vs sync trade-offs: @st.cache_data is synchronous and blocks the Streamlit script thread during a cache miss. For operations taking > 2 seconds, use Async Data Loading Patterns with asyncpg to keep the UI responsive and show a progress indicator during hydration.

Storage backend latency comparison:

Latencies assume a 20 MB GeoDataFrame serialized as Parquet. Pickle adds 30–60% to write time due to Python object traversal.

Jump to heading# Observability and production reliability

Instrument the caching layer before deploying to shared environments. Log cache outcomes alongside query execution time so you can detect regressions:

import logging
import time

logger = logging.getLogger("spatial.cache")

def cached_query_with_telemetry(cache_key: str, query_fn, *args, **kwargs):
    start = time.perf_counter()
    hit = cache_key in _in_memory_cache
    if hit:
        result = _in_memory_cache[cache_key]
    else:
        result = query_fn(*args, **kwargs)
        _in_memory_cache[cache_key] = result
    elapsed_ms = (time.perf_counter() - start) * 1000
    logger.info(
        "cache_event",
        extra={"key": cache_key[:16], "hit": hit, "elapsed_ms": round(elapsed_ms, 1)},
    )
    return result

A sudden drop in hit ratio — from 80% to 20% overnight — typically signals a schema change in the underlying PostGIS table (column rename, geometry column type change) or a CRS normalization regression. Alert on this metric.

Wrap all cache retrieval in a fallback:

def safe_cache_read(key: str) -> gpd.GeoDataFrame | None:
    try:
        return read_cached_geodataframe(key)
    except Exception as exc:
        logger.warning("Cache read failed, falling back to DB", exc_info=exc)
        return None

Never let a deserialization failure surface to the end user as a traceback. The cache miss is recoverable; the experience of a blank dashboard with a Python stack trace is not.

Jump to heading# Frequently asked questions

Back to Caching Strategies & Async Performance Tuning

Jump to heading# Related

• @st.cache_data Implementation — in-depth decorator configuration, hash overrides, and Streamlit-specific pitfalls
• Memory Limit Management — container memory ceilings, GeoDataFrame eviction policies, and per-session budgets
• Async Data Loading Patterns — asyncpg connection pooling, concurrent tile fetching, and cache-miss hydration without blocking the UI
• Fixing Cache Invalidation for Dynamic Spatial Queries — coordinate rounding, versioned keys, and event-driven invalidation for user-drawn geometries