Skip to content

Chapter 8: Spatial Databases and Query Systems

Spatial databases and query systems

Spatial databases make geospatial data queryable, transactional, indexed, secure, and operational. They are often the center of production geospatial systems because they combine geometry, attributes, relationships, permissions, and repeatable query logic.

Companion visual reference: Spatial Data Structure Visual Atlas

Learning Goals

  • Understand spatial columns, SRIDs, indexes, and query planning.
  • Write common spatial SQL patterns.
  • Tune spatial queries for correctness and performance.
  • Compare PostGIS, DuckDB Spatial, SpatiaLite, BigQuery GIS, and cloud warehouses.

Theory

A spatial database extends ordinary relational concepts with geometry types, spatial predicates, spatial functions, and spatial indexes. The database can answer questions like "which parcels intersect this floodplain?" or "which events occurred within 500 meters of a school?"

Indexes reduce the search space, usually through bounding boxes first, then exact geometry checks. Query plans matter because spatial operations can be computationally expensive.

Research and Standards Foundations

Spatial databases operationalize the Simple Features model in SQL. PostGIS extends PostgreSQL with geometry storage, spatial predicates, spatial processing functions, and GiST-based R-tree indexing. DuckDB Spatial brings similar geospatial processing into an analytical database engine and explicitly uses a GEOMETRY type based on Simple Features.

The engineering pattern is a two-stage query: first use an index-friendly bounding-box or distance prefilter, then run an exact predicate such as ST_Intersects, ST_Contains, or ST_DWithin. This is why query formulation matters. A logically correct spatial SQL query may still be operationally wrong if it prevents index use, transforms every row before filtering, or returns full-resolution geometries to a user interface.

Math

Spatial database math includes bounding boxes, R-tree and GiST indexing, predicate evaluation, distance calculations, topology, aggregation, and coordinate transformation. Many queries use a two-step strategy: fast approximate filter, then exact predicate.

Key computation patterns:

bbox = [minx, miny, maxx, maxy]

bbox_intersects =
  bbox1.minx <= bbox2.maxx and
  bbox1.maxx >= bbox2.minx and
  bbox1.miny <= bbox2.maxy and
  bbox1.maxy >= bbox2.miny

two-stage spatial query:
candidate_geometries = index_filter(query_bbox)
final_results = exact_predicate(candidate_geometries, query_geometry)

In SQL, this often appears as a bounding-box or index-assisted candidate search followed by exact predicates such as ST_Intersects, ST_Contains, or ST_DWithin.

See also: Math and Algorithms Reference

Tools of the Trade

  • PostGIS, PostgreSQL, pgRouting.
  • DuckDB Spatial.
  • SpatiaLite.
  • BigQuery GIS.
  • Snowflake geospatial functions.
  • SQL Server spatial and Oracle Spatial.
  • EXPLAIN plans, vacuum/analyze, spatial indexes.

Examples of Real-World Solutions

  • Public safety dispatch finds the nearest available unit.
  • A real estate platform calculates school districts and hazards for properties.
  • A utility company traces affected customers after an outage.
  • A health department aggregates cases by census tract while protecting privacy.

Working Practice Examples

  1. Load a GeoJSON file into PostGIS and create a GiST index.
  2. Write queries using ST_Intersects, ST_Contains, ST_DWithin, and ST_Transform.
  3. Compare query time before and after indexing.
  4. Build a materialized view for map tiles or dashboard summaries.

Common Failure Modes

  • Missing spatial index.
  • Wrong SRID or mixed SRIDs.
  • Using ST_Distance where ST_DWithin would use an index.
  • Returning unsimplified geometry to clients.
  • Confusing geometry and geography types.

Works Cited

Obe, Regina O., and Leo S. Hsu. PostGIS in Action. 3rd ed., Manning, 2021.

"PostGIS Manual." PostGIS, https://postgis.net/docs/. Accessed 9 May 2026.

"PostGIS Official Manual." PostGIS, https://postgis.net/documentation/manual/. Accessed 9 May 2026.

"DuckDB Spatial Extension." DuckDB, https://duckdb.org/docs/current/core_extensions/spatial/overview.html. Accessed 9 May 2026.

"BigQuery Geography Functions." Google Cloud, https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions. Accessed 9 May 2026.

GeoInformatica Consulting logo