Spatial Indexing

SpatialFlow uses PostGIS GIST indexes on every geometry column for fast spatial lookups. This guide explains how spatial indexes work, which indexes SpatialFlow creates, when to use each index type, and how to optimize spatial queries.

For the spatial query patterns these indexes accelerate, see the Spatial Queries guide.

How Spatial Indexes Work

Standard B-tree indexes handle scalar comparisons (=, <, >), but spatial queries need to test bounding-box overlaps and geometric relationships. PostGIS solves this with GIST (Generalized Search Tree) indexes.

A GIST index on a geometry column builds an R-tree structure of bounding boxes. When you run a query like ST_Contains(geometry, point), PostGIS first checks bounding boxes in the index to eliminate non-overlapping geometries, then runs the exact test only on candidates that pass the bounding-box filter.

GeoDjango automatically creates a GIST index on every GeometryField, PointField, and LineStringField. The migration-generated SQL looks like:

CREATE INDEX geofences_geometry_id ON geofences USING GIST (geometry);

You do not need to create these manually -- Django migrations handle them for you.

SpatialFlow Index Strategy

SpatialFlow combines two index types: GIST for spatial columns and B-tree for filtering columns. Every spatial query uses both.

GIST Indexes (Automatic)

These are created automatically by GeoDjango for all geometry columns:

Model	Column	Type	Purpose
Geofence	geometry	GeometryField	Polygon/MultiPolygon/Point containment and proximity
Device	last_location	PointField	Current device position lookups
DeviceLocation	location	PointField	Historical location point queries
DeviceSession	track_geometry	LineStringField	Simplified track display

B-tree Indexes (Manual)

These are defined explicitly in model Meta.indexes for non-spatial filtering:

Model	Column(s)	Purpose
Geofence	workspace_id	Tenant isolation
Geofence	is_active, created_at	Active geofence filtering
Device	workspace_id, device_id	Device lookups within workspace
DeviceLocation	device_id, timestamp	Track history ordered by time
GeofenceEvent	device_id, geofence_id, timestamp	Event lookups

Compound Queries

SpatialFlow always combines workspace filtering (B-tree) with spatial queries (GIST). This serves two purposes: performance (B-tree narrows the dataset before the spatial scan) and tenant isolation (no cross-workspace data leaks).

-- From Device.update_location(): find geofences containing a point
SELECT g.id, g.name
FROM geofences g
WHERE g.workspace_id = %s              -- B-tree: narrow to workspace
  AND g.is_active = true               -- B-tree: only active geofences
  AND ST_Contains(g.geometry,          -- GIST: spatial containment test
      ST_SetSRID(ST_MakePoint(%s, %s), 4326))

The B-tree filter on workspace_id runs first, reducing the candidate set from all geofences in the database to just those in the current workspace. The GIST index then handles the spatial test on the remaining rows.

When to Use Each Index Type

Query Type	Index	Example
Point-in-polygon	GIST	geometry__contains=point
Proximity search	GIST	geometry__distance_lte=(point, D(km=1))
Filter by workspace	B-tree	workspace_id=uuid
Time range	B-tree	timestamp__gte=start
Spatial + filter	GIST + B-tree	WHERE workspace_id = X AND ST_Contains(...)

Query Optimization Patterns

Geography vs. Geometry Casting

PostGIS geometry operations work in the coordinate reference system of the data (EPSG:4326 = degrees). To get distances in meters, cast to geography:

-- Degrees (not useful for real-world measurements)
SELECT ST_Distance(a.geometry, b.geometry);

-- Meters (accurate, uses WGS84 ellipsoid)
SELECT ST_Distance(a.geometry::geography, b.geometry::geography);

SpatialFlow uses ::geography for all distance calculations and circle geofence checks (ST_DWithin). The geography type is slower than geometry because it uses ellipsoid math, so SpatialFlow reserves it for operations that need meter accuracy.

Simplification for Display

When a device session has hundreds or thousands of track points, rendering them all is expensive. SpatialFlow applies Douglas-Peucker simplification to reduce vertex count:

# From Device._compute_track_geometry()
# Simplify tracks with > 500 points for efficient display

reduction_ratio = point_count / 500
tolerance = 0.00001 * (reduction_ratio ** 1.5)

cursor.execute("""
    WITH track AS (
        SELECT ST_MakeLine(location ORDER BY timestamp) as line
        FROM device_locations
        WHERE id = ANY(%s)
    )
    SELECT ST_AsHexEWKB(ST_Force2D(ST_Simplify(line, %s)))
    FROM track
    WHERE line IS NOT NULL
""", [location_ids, tolerance])

The tolerance parameter controls how much the line is simplified. Higher values produce fewer points but less accurate shapes.

Set-Based Queries

SpatialFlow tests a point against all workspace geofences in a single SQL query rather than looping in Python. This lets PostGIS use the GIST index once and return all results:

-- From GeofenceManager.bulk_test_point()
-- Tests one point against ALL active geofences in a workspace
SELECT
    g.id,
    g.name,
    CASE
        WHEN g.radius_meters IS NULL
            THEN ST_Contains(g.geometry, ST_SetSRID(ST_MakePoint(%s, %s), 4326))
        ELSE ST_DWithin(
            g.geometry::geography,
            ST_SetSRID(ST_MakePoint(%s, %s), 4326)::geography,
            g.radius_meters
        )
    END AS contains,
    ST_Distance(g.geometry::geography,
        ST_SetSRID(ST_MakePoint(%s, %s), 4326)::geography) AS distance_meters
FROM geofences g
WHERE g.is_active = true
  AND g.workspace_id = %s
ORDER BY contains DESC, distance_meters ASC

Diagnosing slow spatial queries

Use EXPLAIN ANALYZE to see whether PostGIS is using the GIST index:

EXPLAIN ANALYZE
SELECT id FROM geofences
WHERE ST_Contains(geometry, ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));

Look for Index Scan using geofences_geometry_id in the output. If you see Seq Scan, the index is not being used -- check that the SRID matches (both sides must be 4326).

Common Pitfalls

Avoid these spatial query mistakes

Forgetting ::geography cast -- Without it, ST_Distance returns degrees instead of meters. A result of 0.001 looks small but is actually about 111 meters.

Missing SRID -- Always wrap raw coordinates with ST_SetSRID(..., 4326). Without an SRID, PostGIS cannot match the coordinate system of indexed columns and may skip the index entirely.

Using ST_Contains for circle geofences -- Circle geofences in SpatialFlow are stored as a Point with radius_meters. Use ST_DWithin(geometry::geography, point::geography, radius_meters) instead of ST_Contains, which would only check if the point is exactly at the center.

Further Reading

• Spatial Queries Guide -- Containment, proximity, intersection, and distance query patterns
• PostGIS Documentation -- Full PostGIS function reference
• GeoDjango Documentation -- Django spatial field and lookup reference