Planetary-scale answers, unlocked.
A Hands-On Guide for Working with Large-Scale Spatial Data. Learn more.
Authors
A geospatial database stores the shapes and locations of things on Earth, such as buildings and roads, and lets you query them with SQL (Structured Query Language). It has three parts. Geometry types hold the shapes. A spatial index finds the shapes near a place without reading every row. Spatial SQL functions measure and compare shapes.
The spatial index sets how much data a query reads. In Wherobots, the Overture Maps buildings table holds 2,533,842,612 buildings in 5,141 files. Each file records a bounding box: the smallest rectangle around the buildings inside it. A query that filtered on those boxes left 7 of the 5,141 files to read.
bbox
Ralf Hartmut Güting gave the classic geospatial database definition in An introduction to spatial database systems (The VLDB (Very Large Data Bases) Journal, 1994). In it, a spatial database system is a database system first. Its data model and query language include spatial data types. Its software supports those types with, at least, spatial indexing and efficient spatial join algorithms. A spatial join matches the rows of two tables by location.
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. It does so through a coordinate reference system (CRS), the definition that places each coordinate on the planet. Our article on the coordinate reference system explains it.
A GIS (geographic information system) is software for viewing, editing and analyzing map layers. A geospatial database stores those layers as tables. It returns answers to queries from many programs, a GIS among them. 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.
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. The model defines one general Geometry type with four subtypes: Point, Curve, Surface and GeometryCollection. Each geometry belongs to a spatial reference system, another name for a CRS.
The PostGIS manual lists the concrete 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. A feature is one mapped thing, such as a building, with its shape and its facts. Part 2 of the standard, the SQL option, defines a SQL schema, a standard table layout, to store, retrieve, query and update features.
These geometry types are vector types. Each one describes a separate feature. Raster data stores a grid of cells, such as a satellite image. The Havasu table spec defines a raster type beside the geometry type. Raster vs vector data compares the two models.
One query on three Overture Maps tables shows the vector types in use. Overture Maps is an open map dataset. 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)
Buildings are polygons, places are points, and road segments are lines.
An index is a lookup structure that finds rows without reading the whole table. 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. First, the index finds the 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 two passes, called filter and refine.
Havasu is the Wherobots table format built on Iceberg. It records each file's bounding box (MBR) when the file is written. According to the Havasu clustering documentation, the engine then skips every file whose bounding box misses the query's spatial filter. Wherobots calls this spatial filter push-down: the filter is checked against the file boxes before any file is read.
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 names a cost of GiST indexes too. A GiST index performs well while it fits in memory and while the cost of updating it on each write is acceptable.
Spatial SQL is SQL with functions that take or return geometry. Most systems name them with an ST_ prefix. The functions include constructors, measurements, predicates and operations:
Two standards define these functions. One is the OGC SQL option. The other is ISO/IEC 13249-3, SQL Multimedia Part 3: Spatial (SQL/MM Spatial). PostGIS uses SQL/MM for curved types built from 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. Topology, here, is the math of how shapes touch, overlap and contain each other. Max Egenhofer and Robert Franzosa wrote Point-set topological spatial relations (International Journal of Geographical Information Systems, 1991). It 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.
A data lake table is a table stored as plain files in cloud storage. The Wherobots test below measures what a computed value costs on one.
A familiar geospatial database example is PostGIS. Each description below comes from the product's own documentation or paper.
A single-node database runs on one server.
A data warehouse is a database built for analysis of large tables.
A distributed engine splits a table into parts, called partitions. It processes them on many machines at once.
A format stores spatial data, and an engine queries it. GeoParquet is an incubating OGC standard. It adds geospatial types to Apache Parquet, a file format that stores data column by column.
Apache Iceberg is a table format. Its metadata groups many Parquet files into one table, with statistics for each file. The Havasu Catalog article explains how Wherobots catalogs such tables.
A worker is one machine in a cluster. 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.
Choosing a database for geospatial data starts with the workload. There is no single best geospatial database. These rules match each workload to a system type in the table:
We tested rule 5 on one table in Wherobots.
A query plan lists the steps the engine runs for a query. The scan is the step that reads the table's files.
Query 1 reads the .files table. It is an Apache Iceberg metadata table, with one row per data file. Each row holds the file's row count, its size, and the lowest and highest value of each column. Reading it opens no data file.
.files
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. They hold 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
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. The other two span most of a continent:
Query 2 counts the buildings whose bounding box lies inside the King County box.
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
It returned 1,283,332 buildings in 5.4 seconds. EXPLAIN FORMATTED prints the query plan, the steps WherobotsDB runs. The plan's scan line holds the four bounds:
EXPLAIN FORMATTED
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, ...)
WherobotsDB compares a filter inside IcebergScan with each file's bounds before it opens the file. It skips the files whose bounds miss the box. So the scan reads at most the 7 files from Query 1.
IcebergScan
Query 3 tests each building's centroid, the center point of its shape. ST_Centroid computes the centroid from the geometry. ST_X and ST_Y return its two coordinates:
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. A separate Filter step follows the scan:
Filter
IcebergScan(table=...buildings_building, ..., filters=, runtimeFilters=, ...) Filter (ST_X(ST_Centroid(geometry)) ... ST_Y(ST_Centroid(geometry)) ...)
With no filter in the scan, WherobotsDB would read all 5,141 files (253.2 GB). It would then compute a centroid for each of 2,533,842,612 rows before the box test. We ran EXPLAIN only. That avoided a full read on a small shared session, the compute that ran our queries.
EXPLAIN
Query 4 is a spatial join. 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
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. A hint is an instruction to the engine written inside the SQL. The BROADCAST hint copies the small table, the county polygons, to every worker. WherobotsDB builds an R-tree on those polygons. It then tests the 1,283,332 building centroids against the tree.
BroadcastIndexJoin ... INTERSECTS
SpatialIndex g: geometry, RTREE
BROADCAST
The spatial join article compares broadcast and partitioned joins. The nearest-neighbor join announcement covers a related join type.
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 file 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 condition in the Havasu documentation: push-down works best when nearby features share files.
The OGC first set out spatial SQL in its Simple Features Specification for SQL. Revision 1.1 (OGC document 99-049) came out on 5 May 1999. 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, which add height.
Jignesh Patel and David DeWitt described Partition based spatial-merge join (ACM SIGMOD, 1996). Their method joined two inputs that have no index by splitting both into spatial partitions. Hadoop-GIS and SpatialHadoop then used partitioning and indexing on 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 stores geometry in open table formats, with a bounding box for each file. In the Iceberg spec, a geometry column with no stated CRS stores longitude and latitude on the WGS 84 (World Geodetic System 1984) datum, named OGC:CRS84. A datum is the model of the Earth's shape that coordinates are measured against. Wherobots described its own format in Havasu: a table format for spatial attributes in a data lake architecture.
ST_Intersects
CREATE SPATIAL INDEX
Query Overture buildings with spatial SQL in a Wherobots free trial at cloud.wherobots.com.
A geospatial database stores the shapes and locations of things on Earth and lets you query them. It has three parts. Geometry types store points, lines and polygons. A spatial index finds the rows in an area without reading every row. You ask the questions with spatial SQL functions, such as ST_Intersects and ST_Distance.
PostGIS is a common example. It adds geometry types, spatial indexes and spatial functions to the PostgreSQL database. Others are Oracle Spatial, the geometry and geography types in Microsoft SQL Server, SpatiaLite for SQLite, and the geography type in Google BigQuery. Apache Sedona and WherobotsDB run spatial SQL across a cluster of machines.
The best choice depends on the workload. For many small edits to single features, use a relational database with spatial types, such as PostGIS. For one user or one app, a single file such as SpatiaLite or GeoParquet works. For billions of rows or joins between large tables, use a distributed engine such as Apache Sedona or WherobotsDB. Run it over files with per-file bounding boxes.
The usual answer is vector data, raster data and attribute data. Vector data stores features as points, lines and polygons. Raster data stores a grid of cells, such as a satellite image. Attribute data is the table of facts about each feature, such as a building’s height. Within vector data, points, lines and polygons are also called the three geometry types.
The terms are often used for the same thing. A spatial database can store shapes in any coordinate space, including a plane with no units. A geospatial database ties coordinates to places on Earth through a coordinate reference system. One example is longitude and latitude on WGS 84, the World Geodetic System 1984 that the Global Positioning System (GPS) uses.
A GIS (geographic information system) is software for viewing, editing and analyzing map layers, often on a desktop. A geospatial database stores those layers in tables and returns answers to queries from many programs. A GIS often reads its layers from a geospatial database.
Spatial SQL is SQL with functions that take or return geometry. Most systems name them with an ST_ prefix. ST_Intersects and ST_Contains test how two shapes relate. ST_Area and ST_Distance measure. ST_Buffer and ST_Union build new shapes. Two standards define these functions: the Simple Features SQL option of the Open Geospatial Consortium (OGC), and ISO/IEC 13249-3 (SQL/MM Spatial).
Yes. PostGIS is an extension of the PostgreSQL relational database. It adds geometry and geography types, spatial indexes and spatial SQL functions. Its spatial indexes are an R-tree on GiST, a quadtree on SP-GiST, and BRIN boxes for each block range. It also stores raster data.
Yes, if a table format stores geometry and per-file bounds, and an engine runs spatial SQL over the table. A data lake is a store of plain files in cloud storage. Apache Iceberg version 3 adds geometry and geography types with a bounding box for each data file. In Wherobots, a box filter on 2,533,842,612 Overture buildings used those bounds to limit the read to 7 of 5,141 files.
Geospatial database management is the work of running a spatial database. It covers loading data, choosing coordinate reference systems, validating geometry and building spatial indexes. It also covers storing nearby features together. That layout sets how many files or blocks an area query can skip.
What is Spatial Intelligence? Definition, AI, Earth Scale
Spatial intelligence: AI that understands 3D space. Open map data holds a height for 96% of San Francisco's buildings and 0.13% of Nairobi's.
What Is PySpark? Definition, Tutorial, and Spatial Data
PySpark is the Python API for Apache Spark. Learn how it runs, with a short tutorial. Then see a spatial join that took 6 minutes without Apache Sedona's join planning and under a second with it.
What is a Semantic Layer? Definition, Examples, Spatial Rules
A semantic layer defines business terms and metrics once, so every tool gives the same answer. For location data it must also set the boundary, the distance and what counts as inside.
share this article
Awesome that you’d like to share our articles. Where would you like to share it to:
