What is a Geospatial Database? Definition, Examples

A geospatial database is a database that stores the shapes and locations of things on Earth and answers questions about them in SQL. It has three parts: geometry types, a spatial index, and spatial SQL functions. This article tests one claim about those parts: the spatial index, or the bounding boxes a table format stores for each file, sets how much data a spatial query reads. On 2,533,842,612 Overture buildings in Wherobots, a filter the scan could use limited the read to 7 of 5,141 files.

Key takeaways

• A geospatial database has three parts: geometry types, a spatial index, and spatial SQL functions.
• Most geometry types follow the Open Geospatial Consortium's Simple Features standard: points, lines, polygons and their multi-part forms.
• Spatial indexes such as the R-tree work on bounding boxes. Table formats such as Apache Iceberg store one bounding box per data file.
• A box filter on Overture's bbox column left 7 files (0.46 GB) to read. The same box written as a centroid test left all 5,141 files (253.2 GB).
• The best geospatial database depends on the workload, from a single file to a distributed engine.

What is a geospatial database?

Ralf Hartmut Güting gave the classic geospatial database definition in An introduction to spatial database systems (The VLDB Journal, 1994). A spatial database system is a database system first. It offers spatial data types in its data model and query language. Its implementation supports those types with, at least, spatial indexing and efficient spatial join algorithms. The PostGIS workshop on spatial indexing calls the spatial index "one of the three key features of a spatial database".

Spatial database and geospatial database often mean the same thing. A spatial database can store shapes on any plane, including one with no units. A geospatial database ties coordinates to the Earth through a coordinate reference system (CRS), the definition that places each coordinate on the planet.

A GIS (geographic information system) is software for viewing, editing and analyzing map layers. A geospatial database stores those layers as tables and answers queries from many programs, including a GIS. Our article on geospatial data covers the data itself.

Geospatial database management is the work around the three parts. The PostGIS data management chapter covers it: geometry types, reference systems, validation, loading, and spatial indexes.

Geometry types and the Simple Features model

Most geospatial databases share one geometry model. The Open Geospatial Consortium (OGC), a standards body for geospatial data, publishes it as Simple Feature Access, Part 1: Common Architecture. The International Organization for Standardization (ISO) publishes the same model as ISO 19125. Its base Geometry class has four subclasses: Point, Curve, Surface and GeometryCollection. Each geometry belongs to a spatial reference system.

The PostGIS manual lists the concrete types:

• Point: one location.
• LineString: a line of connected straight segments.
• Polygon: an area bounded by an outer ring, with zero or more holes.
• MultiPoint, MultiLineString and MultiPolygon: collections of one type.
• GeometryCollection: a collection that mixes types.

Each geometry also has an envelope, the smallest upright box that contains it. Wherobots' Havasu documentation calls it the minimum bounding rectangle (MBR), or bounding box. Every index in the next section works on these boxes. Part 2 of the standard, the SQL option, defines a SQL schema to store, retrieve, query and update features.

Vector types describe discrete features. Raster data stores a grid of cells, such as a satellite image. The Havasu table spec defines a raster type beside the geometry type, and raster vs vector data compares the two models.

One query on three Overture Maps tables shows the vector types in use. The box around King County, Washington, runs from longitude -122.6 to -120.9 and latitude 47.0 to 47.8. Overture stores each feature's bounding box in a bbox column.

SELECT 'buildings_building' AS source, ST_GeometryType(geometry) AS geometry_type, COUNT(*) AS features
FROM wherobots_open_data.overture_maps_foundation.buildings_building
WHERE bbox.xmin >= -122.6 AND bbox.xmax <= -120.9 AND bbox.ymin >= 47.0 AND bbox.ymax <= 47.8
GROUP BY ST_GeometryType(geometry)
UNION ALL
SELECT 'places_place', ST_GeometryType(geometry), COUNT(*)
FROM wherobots_open_data.overture_maps_foundation.places_place
WHERE bbox.xmin >= -122.6 AND bbox.xmax <= -120.9 AND bbox.ymin >= 47.0 AND bbox.ymax <= 47.8
GROUP BY ST_GeometryType(geometry)
UNION ALL
SELECT 'transportation_segment', ST_GeometryType(geometry), COUNT(*)
FROM wherobots_open_data.overture_maps_foundation.transportation_segment
WHERE bbox.xmin >= -122.6 AND bbox.xmax <= -120.9 AND bbox.ymin >= 47.0 AND bbox.ymax <= 47.8
GROUP BY ST_GeometryType(geometry)

Table	Geometry type	Features
buildings_building	ST_Polygon	1,283,254
buildings_building	ST_MultiPolygon	78
places_place	ST_Point	177,411
transportation_segment	ST_LineString	551,134

Buildings are polygons, places are points, and road segments are lines.

Spatial indexes set how much data a query reads

A B-tree, the usual database index, sorts rows by one value. A location has two values, longitude and latitude. A B-tree on either one returns a whole strip of the map.

Spatial indexes work on bounding boxes. The PostGIS workshop describes two passes: the index finds features whose boxes overlap the query box, then an exact test runs on those features only. Oracle documents the same two-tier query model. Our spatial join article shows how joins use these filter and refine steps.

• R-tree. Antonin Guttman introduced R-trees: a dynamic index structure for spatial searching (ACM SIGMOD, 1984). The tree nests nearby boxes inside larger rectangles, and a search enters only rectangles that overlap its box. Norbert Beckmann, Hans-Peter Kriegel, Ralf Schneider and Bernhard Seeger improved how nodes are chosen and split in The R*-tree (ACM SIGMOD, 1990). PostGIS builds its R-tree on GiST, the Generalized Search Tree framework of PostgreSQL.
• Quadtree. Raphael Finkel and Jon Bentley's Quad trees (Acta Informatica, 1974) split space into four quadrants and split each crowded quadrant again. PostGIS offers quadtrees through its space-partitioned GiST method, SP-GiST.
• Grid cells. A grid gives each feature a cell ID, and an ordinary index on the ID finds the features in an area. H3 is a hexagonal grid of this kind, and Oracle Spatial documents H3 indexing.
• Block and file bounding boxes. A PostGIS BRIN (block range index) stores one box for each range of table blocks. The Apache Iceberg table spec stores lower and upper bounds for each column of each data file. Its version 3 adds geometry types whose file bounds form a bounding box of every object in the file.

Havasu, the Wherobots table format built on Iceberg, records each file's MBR at write time. According to the Havasu clustering documentation, a query then skips every file whose MBR misses its spatial filter. Wherobots calls this spatial filter push-down.

Box indexes share one condition. The PostGIS manual says a BRIN index is efficient only if the boxes of the block ranges overlap little. The Havasu documentation says push-down "works best when the spatial data near to each other were stored in the same file". In both, the order of rows on disk sets how much a query can skip. The PostGIS manual adds a cost: GiST indexes perform well while they fit in memory and their update cost on each write is acceptable.

Spatial SQL: functions and predicates

Spatial SQL is SQL with functions that take or return geometry. Most systems name them with an ST_ prefix. Constructors such as ST_GeomFromText read well-known text (WKT), a plain-text geometry format. Measurements include ST_Area and ST_Distance. Predicates are true or false tests such as ST_Intersects and ST_Contains. Operations such as ST_Buffer and ST_Union return new geometry.

Two standards define them: the OGC SQL option, and ISO/IEC 13249-3, SQL Multimedia Part 3: Spatial (SQL/MM Spatial). PostGIS uses SQL/MM for curved types with circular arcs. The OGC group that maintains Simple Features is working to synchronize it with SQL/MM Part 3.

The predicates come from topology research. Max Egenhofer and Robert Franzosa's Point-set topological spatial relations (International Journal of Geographical Information Systems, 1991) defined the relations between two regions from their boundaries and interiors.

Only some predicates use an index. The PostGIS workshop lists the functions that use one automatically, among them ST_Intersects, ST_Contains, ST_Within and ST_DWithin. The index holds boxes of the stored geometry. It has no entries for a value computed at query time, such as the longitude of a building's centroid. The Wherobots test below measures what that costs on a data lake table.

Geospatial database examples

A familiar geospatial database example is PostGIS. Each description below comes from the product's own documentation or paper.

Single-node databases

• PostGIS. PostGIS extends the PostgreSQL relational database with storage, indexing and querying of geospatial data. It has geometry and geography types; GiST, SP-GiST and BRIN spatial indexes; and raster support.
• Oracle Spatial. Oracle describes Spatial as a set of functions, procedures, data types and data models in the Oracle database. Vector geometry uses the SDO_GEOMETRY type.
• SQL Server. Microsoft's SQL Server spatial data has a geometry type for flat coordinates and a geography type for round-earth coordinates. Both support spatial indexes.
• SpatiaLite. SpatiaLite extends SQLite, a SQL engine in one library. A whole database is one file. Its developers call it mostly compliant with the OGC SQL option.

Cloud data warehouse

• BigQuery. Google's BigQuery geospatial analytics adds a geography data type and GoogleSQL geography functions to a data warehouse.

Distributed engines

A distributed engine splits a table into partitions and processes them on many machines at once.

• Hadoop-GIS. Ablimit Aji and colleagues described Hadoop GIS: a high performance spatial data warehousing system over MapReduce (PVLDB, 2013). MapReduce is the Hadoop model that splits a job into tasks across a cluster.
• SpatialHadoop. Ahmed Eldawy and Mohamed Mokbel described SpatialHadoop: a MapReduce framework for spatial data (IEEE ICDE, 2015).
• Apache Sedona. Jia Yu, Jinxuan Wu and Mohamed Sarwat introduced it as GeoSpark (ACM SIGSPATIAL, 2015), a cluster computing framework for spatial data on Apache Spark. Apache Sedona now runs on one machine, on Spark clusters, on Apache Flink streams and inside Snowflake. It lists more than 300 spatial functions for raster and vector data.
• WherobotsDB. Yu and Sarwat founded Wherobots. WherobotsDB, its distributed spatial SQL engine, comes from the Sedona lineage and reads and writes Havasu tables of geometry and raster columns.

File and table formats

A format stores spatial data, and an engine queries it. GeoParquet is an incubating OGC standard that adds geospatial types to Apache Parquet, a column-oriented file format. Apache Iceberg is a table format: metadata that groups many Parquet files into one table with per-file statistics. The Havasu Catalog article explains how Wherobots catalogs such tables.

Single-node database vs distributed engine vs table format

	Single-node spatial database	Distributed spatial engine	Spatial file or table format
Examples	PostGIS, Oracle Spatial, SQL Server, SpatiaLite	Apache Sedona, WherobotsDB, SpatialHadoop, Hadoop-GIS	GeoParquet, Apache Iceberg version 3, Havasu
Where data sits	The database's own storage; for SpatiaLite, one file	Files in shared storage, read by many workers	Data files plus metadata in object storage
Spatial index	R-tree (GiST), quadtree (SP-GiST), or BRIN block boxes	Spatial partitions, plus an R-tree or quadtree built for a join	A bounding box per file in the metadata
Spatial SQL	ST_ functions in the database	ST_ functions run across the cluster	None; an engine supplies it
Grows by	A larger server	More workers	More files

Object storage is cloud file storage, such as Amazon S3, that many machines read at once. A spatial partition is a region of the map whose features go to one worker. The partitioning tax article covers the edges between partitions.

When one machine stops being enough

Choosing a database for geospatial data starts with the workload. There is no single best geospatial database. These rules match workloads to the system types in the table:

1. If many users edit single features, as in a parcel editor, use a relational database with spatial types, such as PostGIS, Oracle Spatial or SQL Server. The PostGIS manual notes that a GiST index is updated on each write.
2. If one person or one app needs one portable file, use SpatiaLite for edits or GeoParquet for analysis.
3. If the data already sits in a warehouse and spatial queries are occasional, use its spatial type, such as BigQuery's geography type.
4. If a table's R-tree no longer fits in one server's memory, the PostGIS manual suggests BRIN on rows stored in spatial order. Past that, or to join two large tables, use a distributed engine. Varun Pandey, Andreas Kipf, Thomas Neumann and Alfons Kemper compared such engines in How good are modern spatial analytics systems? (PVLDB, 2018).
5. If the data lives as files in object storage, keep per-file bounding boxes in the metadata and sort rows so nearby features share files.

The test below checks rule 5 on one table.

Geospatial databases in Wherobots

The test

• Claim: the spatial index or the file bounds set how much data a spatial query reads.
• What would support it: a box filter the scan can use leaves few files to read. The same box as a computed test leaves all of them. A spatial join plan shows a spatial index.
• What would break it: both filters leave the same files, or the join plan has no spatial index.

Source	Rows	Role
Overture buildings	2,533,842,612 in 5,141 files	Building polygons with a bbox column
Overture division areas	Washington county polygons	Counties for the spatial join

Query 1: the table's files and the files the box touches

The .files table is an Apache Iceberg metadata table, with one row per data file. Each row holds the file's row count, size, and lower and upper bounds for each column. Reading it opens no data file.

SELECT COUNT(*) AS files, SUM(record_count) AS rows, ROUND(SUM(file_size_in_bytes) / 1e9, 1) AS gb
FROM wherobots_open_data.overture_maps_foundation.buildings_building.files

The buildings table has 5,141 files holding 2,533,842,612 rows and 253.2 GB. The next query counts the files whose bbox bounds overlap the King County box:

WITH f AS (
  SELECT record_count, file_size_in_bytes AS bytes,
    readable_metrics.`bbox.xmin`.lower_bound AS xmin, readable_metrics.`bbox.xmax`.upper_bound AS xmax,
    readable_metrics.`bbox.ymin`.lower_bound AS ymin, readable_metrics.`bbox.ymax`.upper_bound AS ymax
  FROM wherobots_open_data.overture_maps_foundation.buildings_building.files
)
SELECT COUNT(*) AS files_total,
  SUM(CASE WHEN xmin IS NULL THEN 1 ELSE 0 END) AS files_without_bounds,
  SUM(CASE WHEN xmax >= -122.6 AND xmin <= -120.9 AND ymax >= 47.0 AND ymin <= 47.8 THEN 1 ELSE 0 END) AS files_touching_king_county_box,
  SUM(CASE WHEN xmax >= -122.6 AND xmin <= -120.9 AND ymax >= 47.0 AND ymin <= 47.8 THEN record_count ELSE 0 END) AS rows_in_those_files,
  ROUND(SUM(CASE WHEN xmax >= -122.6 AND xmin <= -120.9 AND ymax >= 47.0 AND ymin <= 47.8 THEN bytes ELSE 0 END) / 1e9, 2) AS gb_in_those_files
FROM f

Files	Files without bounds	Files touching the box	Rows in those files	GB in those files
5,141	0	7	3,702,731	0.46

Every file has bounds, and 7 overlap the box. The query ran in 2.9 seconds. Five of the 7 files cover parts of western Washington. One of the other two spans longitude -179.80 to -90.10 and latitude 23.87 to 79.42, with 517,022 rows. The Havasu Catalog article found a file with the same bounds near Lake Havasu City, Arizona. The last spans longitude -122.54 to -2.06 and latitude -7.74 to 70.85, with 599,661 rows.

Query 2: a box filter the scan can use

SELECT COUNT(*) AS buildings_in_box
FROM wherobots_open_data.overture_maps_foundation.buildings_building
WHERE bbox.xmin >= -122.6 AND bbox.xmax <= -120.9 AND bbox.ymin >= 47.0 AND bbox.ymax <= 47.8

The query returned 1,283,332 buildings whose bounding box lies inside the King County box, in 5.4 seconds. EXPLAIN FORMATTED prints the query plan, the steps WherobotsDB runs. The plan's scan line holds the four bounds:

IcebergScan(table=...buildings_building, ..., filters=bbox.xmin IS NOT NULL, ..., bbox.xmin >= -122.6, bbox.xmax <= -120.9, bbox.ymin >= 47.0, bbox.ymax <= 47.8, ...)

A filter inside IcebergScan is compared with each file's bounds before the file opens. Files whose bounds miss the box are skipped, so the scan reads at most the 7 files from Query 1.

Query 3: the same box as a test on each centroid

This version tests each building's centroid, computed from the geometry with ST_Centroid, ST_X and ST_Y:

EXPLAIN FORMATTED
SELECT COUNT(*) AS buildings_in_box
FROM wherobots_open_data.overture_maps_foundation.buildings_building
WHERE ST_X(ST_Centroid(geometry)) BETWEEN -122.6 AND -120.9
  AND ST_Y(ST_Centroid(geometry)) BETWEEN 47.0 AND 47.8

The scan line now has an empty filter, and a separate Filter step follows the scan:

IcebergScan(table=...buildings_building, ..., filters=, runtimeFilters=, ...)
Filter (ST_X(ST_Centroid(geometry)) ... ST_Y(ST_Centroid(geometry)) ...)

With no filter in the scan, all 5,141 files (253.2 GB) would be read, and a centroid computed for each of 2,533,842,612 rows before the box test. We ran EXPLAIN only, to avoid a full read on a small shared session.

Query 4: a spatial join with an R-tree

A spatial join matches rows of two tables by a spatial predicate. This one assigns each building's centroid to the Washington county polygon that contains it:

WITH county AS (
  SELECT names.primary AS county, geometry AS g FROM wherobots_open_data.overture_maps_foundation.divisions_division_area
  WHERE country = 'US' AND region = 'US-WA' AND subtype = 'county' AND class = 'land'
),
b AS (
  SELECT ST_Centroid(geometry) AS c FROM wherobots_open_data.overture_maps_foundation.buildings_building
  WHERE bbox.xmin >= -122.6 AND bbox.xmax <= -120.9 AND bbox.ymin >= 47.0 AND bbox.ymax <= 47.8
)
SELECT /*+ BROADCAST(county) */ county.county, COUNT(*) AS buildings
FROM b JOIN county ON ST_Intersects(county.g, b.c)
GROUP BY county.county ORDER BY buildings DESC

County	Buildings
King County	845,001
Pierce County	352,515
Kitsap County	42,095
Snohomish County	31,351
Kittitas County	12,194
Chelan County	138
Yakima County	38

The join ran in 14.8 seconds. The other counties extend past the box, so their counts cover only the part inside it. The plan shows BroadcastIndexJoin ... INTERSECTS with SpatialIndex g: geometry, RTREE. The BROADCAST hint copies the small table, the county polygons, to every worker. WherobotsDB builds an R-tree on those polygons and tests the 1,283,332 building centroids against it. The spatial join article compares broadcast and partitioned joins, and the nearest-neighbor join announcement covers a related join type.

Limits of the test

• Query 3 was not executed. Its full read comes from its plan and the file metadata in Query 1.
• Query 1 counts files whose bounds overlap the box. It does not measure bytes read inside each file.
• Query 2 counts buildings whose box lies wholly inside the King County box, and Query 3 tests centroids, so the two sets differ at the edges.
• Each run time comes from one run on a small shared session, so none is a benchmark. We ran no comparison with any other system.

Verdict

The results support the claim. A box filter the scan could use left 7 of 5,141 files, 0.46 GB of 253.2 GB. The same box as a centroid test left the scan with no filter and all 5,141 files to read. In the spatial join, WherobotsDB built an R-tree and counted 845,001 buildings in King County. The bounds are coarse, though: the 7 files hold 3,702,731 rows for 1,283,332 results, and two of them span most of a continent. That matches the Havasu documentation's condition that nearby features share files.

Geospatial database research history

The first OGC specification for spatial SQL was the Simple Features Implementation Specification for SQL 1.1 (OGC document 99-049). Both parts of Simple Feature Access now stand at version 1.2.1. The working group that maintains them is adding new geometry types and support for 3D coordinates.

Jignesh Patel and David DeWitt's Partition based spatial-merge join (ACM SIGMOD, 1996) joined two inputs that have no index by splitting both into spatial partitions. Hadoop-GIS and SpatialHadoop carried partitioning and indexing to clusters. Yu, Zongsi Zhang and Sarwat described GeoSpark's partitioning, indexing and joins in Spatial data management in Apache Spark: the GeoSpark perspective and beyond (GeoInformatica, 2018). GeoSpark became Apache Sedona.

The newest step puts geometry into open table formats, with bounding boxes per file. In the Iceberg spec, a geometry column with no stated CRS stores longitude and latitude on the World Geodetic System 1984 (WGS 84) datum, named OGC:CRS84. Wherobots described its own format in Havasu: a table format for spatial attributes in a data lake architecture.

Conclusions

1. A geospatial database is geometry types, a spatial index and spatial SQL. Güting's definition requires spatial types in the query language and spatial indexing, and the Overture tables in Wherobots use all three parts.
2. If a query filters by area, write the filter so the scan can use it. A filter on the bbox columns left 7 of 5,141 files. A computed centroid test left all 5,141 files (253.2 GB). The Havasu Catalog article shows an ST_Intersects filter on a constant polygon inside the scan too.
3. Per-file bounds skip data only as well as the files are laid out. Two of the 7 files span most of a continent. If file bounds are wide, sort the table so nearby features share files, with CREATE SPATIAL INDEX in Havasu. This test did not measure a re-sorted table, so the size of the gain is untested here.
4. If a join pairs a large table with a small one, broadcast the small one and index it. With the hint, WherobotsDB built an R-tree on the county polygons and counted 845,001 King County buildings in 14.8 seconds.
5. Choose the system by workload. Use a relational database for many small edits, a file for one user, a warehouse type for occasional spatial queries, and a distributed engine for billions of rows. These rules rest on each product's documentation. We ran no cross-product test.

Related terms

• Geospatial data: the vector and raster data a geospatial database stores
• Spatial join: matching rows of two tables by location
• H3: a hexagonal grid used as a spatial index
• Havasu Catalog: the Wherobots catalog of spatial Iceberg tables
• Partitioning tax: the cost of splitting spatial data by hand
• Raster vs vector data: the two data models
• Coordinate reference system: what ties coordinates to the Earth
• Building footprint: the polygons in the test above

Read more from Wherobots

• Spatial data processing platforms
• Spatial database cost comparison: PostGIS vs Wherobots
• Using PostGIS with a Wherobots spatial data lakehouse
• Building a spatial data lakehouse

Query Overture buildings with spatial SQL in a Wherobots free trial at cloud.wherobots.com.