Skip to content
Tutorials Center
My learning

Topics

Lesson 69 of 79 ยท Distributed Databases, NoSQL and Analytics

Temporal, Spatial and Other Specialized Databases

Learn temporal databases (valid time, transaction time, bitemporal tables), spatial data and indexes, plus mobile and multimedia databases, with live demos.

A general-purpose relational database stores what is true now about simple things: numbers, strings, dates. Some applications ask for more. A bank must be able to say what an account looked like on the last day of the quarter. A delivery app must find the five restaurants nearest to a phone. A video archive must search by what a picture looks like. For such needs, database research and products developed specialized databases with new data types, new operators and new indexes.

This lesson covers the two most important families, temporal and spatial databases, with live examples, and then gives a short tour of mobile, multimedia and other specialized systems. It assumes you know basic SQL and the B+ tree idea from Indexing Basics.

Specialized databases at a glance

Temporal Remember when facts were true.
Spatial Points, lines, areas and distance.
Mobile Sync data that is often offline.
Multimedia Search images, audio and video.
1 / 2

Temporal databases

An ordinary table keeps only the latest value. When a salary changes, UPDATE overwrites the old one, and the history is gone. A temporal database keeps history in a way the database understands, so you can ask questions about the past without building your own bookkeeping.

There are two independent notions of time:

  • Valid time is when a fact is true in the real world. "Ana earned 3,500 from 1 July 2024" is a valid-time statement. It can be set in the future (a planned raise) or in the past (a retroactive correction).
  • Transaction time is when the fact was stored in the database, and it is set by the system. It tells you what the database believed at a given moment, which matters for audits: "what did the payroll report say on 1 August?"

A table with only valid time is a valid-time table. One with only transaction time is a transaction-time (or system-versioned) table, which is how SQL:2011 temporal tables and products such as SQL Server and MariaDB work. A table with both is bitemporal.

Representing intervals

The usual representation is a half-open interval [from, to): the row is true from valid_from up to but not including valid_to. A row that is still current uses a far-future sentinel such as 9999-12-31 (some systems use NULL). The half-open form means consecutive periods meet with no gap and no overlap, and the condition for "is true at date d" is simply valid_from <= d AND d < valid_to.

Typical integrity rules are that periods of the same entity must not overlap and, where it matters, must have no gaps. Plain SQL cannot declare that directly, so engines either offer period constraints (PostgreSQL has exclusion constraints over range types) or the application checks it.

A bitemporal table in action

Ana's salary history has a twist. She was hired at 3,000. A raise to 3,500 starting 1 July was recorded on 20 June. Then, on 15 September, payroll noticed a mistake: the raise should have been 3,600 starting on the same 1 July. The correction does not erase the earlier belief; it adds a new fact about what is now believed. The four rows below capture the whole story.

SQL A bitemporal salary table

Press Run (or Ctrl + Enter) to see the result here.

Query 1 returns 3,600 and query 2 returns 3,500: the same real-world date gets two answers depending on when you ask. That is exactly what an auditor needs. Queries 3 and 4 show the corrected history and the trail of beliefs. Updating a bitemporal table never deletes rows. Instead you close the current belief by setting recorded_to and insert the new one.

SQL:2011 standardized the idea with PERIOD FOR columns and FOR SYSTEM_TIME AS OF queries, and several products implement system-versioned tables. The mechanics are those you just wrote by hand.

Spatial databases

A spatial database stores and queries geometric objects: points (a shop), lines (a road), polygons (a country) and collections of them, usually with a coordinate reference system that says how numbers map to the Earth. Standards such as the OGC Simple Features model define the geometry types and the operators, and products such as PostGIS (PostgreSQL), Oracle Spatial, SQL Server and SpatiaLite implement them.

Typical operations fall into three groups:

Group Examples Question answered
Measurement distance, length, area How far is it? How big?
Relationships contains, within, intersects, touches Is this point inside that region?
Search range query, nearest neighbor What is in this window? What are the 5 closest?

Two difficulties set spatial data apart. There is no natural order for 2-D objects, so an ordinary B+ tree cannot index them. And exact geometry tests, such as polygon intersection, are expensive. Spatial engines therefore work in two steps:

  1. Filter. Use a cheap approximation, the object's minimum bounding rectangle (MBR), together with a spatial index to find candidates.
  2. Refine. Run the exact, costly geometry test on the candidates only.

Spatial index structures

  • Grid files and fixed grids cut the plane into cells and list the objects in each cell. Simple and good for evenly spread data.
  • Quadtrees split a cell into four quadrants recursively wherever data is dense.
  • R-trees group nearby objects into nested bounding rectangles, with a shape like a B+ tree (see R-tree indexes). They are the default in most spatial databases, often as a GiST index.
  • Space-filling curves such as the Z-order curve or geohash map 2-D positions to a 1-D number or string so an ordinary B+ tree can be used. A prefix of a geohash is a bigger cell, so nearby places usually share a prefix.

Spatial queries in plain SQL

Even without spatial types, you can see the filter-and-refine idea with coordinates in ordinary columns. The table below holds cafes on a map measured in kilometres. The first query is a range query with a bounding box. The second finds the nearest cafes to a point by computing straight-line distance. The third does a radius search in two steps: a cheap box test first, the exact circle test afterwards.

SQL Range, nearest-neighbor and radius queries

Press Run (or Ctrl + Enter) to see the result here.

Notice that the bounding box is a square of side 4 around the centre, which contains the whole circle of radius 2 but also corners that are farther than 2 km. The box returns candidates, and the exact test removes the false positives. A real engine does the same, with an R-tree answering the box test without scanning the table.

On a real sphere you would use geographic distance, not the flat formula above; PostGIS offers a geography type for exactly that, and also ST_DWithin for the radius search.

Why a grid index helps

The next program places 2,000 pseudo-random points on a 100 by 100 map and answers a small window query two ways: by scanning every point and by looking only at the grid cells that overlap the window.

Python Grid index versus full scan

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both methods find the same points, but the grid examines a small fraction of them. R-trees and quadtrees refine the same idea so that dense regions are split into smaller pieces and empty regions cost nothing.

Other specialized databases

Several more families are worth knowing by name:

Family Characteristics Typical features
Mobile databases Run on phones or devices that are often offline or have little power and memory Small embedded engines (SQLite), local storage, synchronization and conflict resolution when the device reconnects
Multimedia databases Store images, audio and video, which are large and unstructured Content-based retrieval using feature vectors, similarity search, streaming and large-object storage
Time-series databases Append-only measurements with timestamps Compression, down-sampling, retention policies (see Time-Series, Queues and Caching)
Graph databases Data is nodes and relationships Traversal queries, path finding, recommendation (see NoSQL Databases)
Scientific and array databases Large multi-dimensional arrays Chunked storage, array slicing, domain-specific operators
Real-time and in-memory databases Predictable response times Main-memory storage, deadline-aware scheduling
Vector databases Embeddings for similarity search Approximate nearest-neighbor indexes

What they share is the pattern you saw with space and time: pick a data model that fits the problem, give it new operators, and build indexes that suit it. The general-purpose engines absorb popular ideas over time, which is why PostgreSQL now has range types for periods, PostGIS for space and extensions for vectors.

Mobile databases in practice

A mobile database lives on a device that moves around, runs on a battery and loses its network connection without warning: a phone, a tablet, a delivery driver's scanner, a sensor. Two things make it different from a server database. The device holds a local replica of the data it needs, so the app keeps working in a tunnel or on a plane (disconnected operation). And the replica must be synchronized with a central server whenever a connection returns.

The usual architecture has three parts: a small embedded engine on the device (SQLite is by far the most common), a server database that holds the master copy, and a sync layer between them that sends changes in both directions. The design problems that follow are the interesting part:

  • Limited resources. Little memory, little storage and a battery to protect, so queries must be cheap and the device stores only a subset (for example, this driver's route for today).
  • Intermittent, slow links. Sync sends only what changed (deltas), batches changes and tolerates interruption: a half-finished sync must be safe to repeat.
  • Conflicts. Two devices change the same record while both are offline. The system needs a rule to decide which change survives.
  • Security. A lost phone holds a copy of the data, so the local database should be encrypted and be able to be wiped remotely.
  • Transactions. A long disconnected session cannot hold locks on the server, so mobile systems use optimistic approaches: commit locally, validate at sync time, and compensate if validation fails.

Common conflict rules are last write wins (the later timestamp survives; simple, but it silently loses data), server wins, field-level merge (changes to different columns are combined), application-specific resolution (ask the user), and, for collaborative apps, data types designed to merge automatically (CRDTs, see Replication and Partitioning). The example below simulates two phones editing offline and shows why a version number is more honest than a timestamp.

Python Offline edits, sync and conflict detection

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Alice reconnects first and her change is accepted, moving the record from version 1 to version 2. When Bob's phone reconnects, his edit started from version 1, but the server is at version 2, so the sync layer detects a conflict instead of overwriting. What happens next is a business decision: the app could keep Alice's change, ask Bob, or merge. A timestamp-only rule would have hidden the conflict.

A geographic information system (GIS) combines a spatial database with tools to capture, analyse and display geographic data. It organizes data in layers, for example roads, rivers, buildings and land use, which can be overlaid and queried together. Layers are vector data (points, lines and polygons with attributes) or raster data (grids of cells, such as satellite images or elevation). The spatial operators and indexes from earlier in this lesson are what make a GIS possible, and the same features now power everyday services: maps, ride-hailing, delivery routing, "stores near me" and fleet tracking.

These are the issues that make GIS data hard to work with:

  • Coordinate systems. Latitude and longitude are angles, not distances. One degree of longitude is about 111 km at the equator and nothing at the poles. Distance and area calculations need either spherical formulas or a projected coordinate system, and mixing systems silently gives wrong answers.
  • Scale and volume. Satellite and sensor data is huge; engines tile it and keep multiple levels of detail.
  • Accuracy and uncertainty. GPS errors of several metres mean that "inside the polygon" can be wrong near a border.
  • Topological consistency. Neighbouring parcels should share borders with no gaps or overlaps; constraints and validation tools check this.
  • Moving objects and streaming updates. Vehicles report positions every second, so indexes must absorb many updates.
  • Privacy. A location trail is personal data, and aggregating or blurring it is often needed.
  • Interoperability. Standards (OGC Simple Features, GeoJSON, WKT/WKB) let data move between tools.

The common location-based queries are nearest neighbour ("closest five cafes"), range ("everything in this window"), geofence ("is the vehicle inside the delivery zone?") and route queries. The example below answers a radius question and a nearest-neighbour question on latitude and longitude with the haversine formula, using a cheap bounding-box test first, just like the filter-and-refine pattern described above.

Python Radius search on latitude and longitude

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first two lines show the coordinate-system trap: a degree of longitude is about 111 km at the equator but only about 68 km at Amsterdam's latitude, which is why the box uses a different width for longitude than for latitude. The box rejects Rotterdam without any trigonometry; the exact test then keeps only points inside the circle (here all three candidates pass, but a point near a corner of the box would be dropped). In a real GIS, an R-tree or geohash index replaces the loop, and the library does the maths with a proper spheroid.

Check your understanding

Quick check ยท 7 questions

of 7 answered You scored / 7 ยท perfect!

  1. 1. What is the difference between valid time and transaction time?

    Valid time describes reality and can be chosen by the user, including retroactive corrections. Transaction time is assigned by the system when the data is stored.
  2. 2. Why is the half-open interval [valid_from, valid_to) commonly used?

    With half-open intervals, one period can end exactly where the next begins, and the test for a date d is simply from <= d AND d < to.
  3. 3. In the bitemporal example, why does a correction add a new row instead of changing the old one?

    Auditing requires being able to reconstruct what the database said at any earlier moment, so the old belief is closed (recorded_to is set) rather than erased.
  4. 4. What does the filter step of filter-and-refine do in a spatial query?

    The cheap approximation produces a candidate set. The refine step then applies the expensive exact test only to those candidates.
  5. 5. Which index structure is most commonly used for spatial data in databases?

    R-trees group nearby objects into nested bounding rectangles, and implementations often appear as GiST or similar indexes.
  6. 6. Two phones edit the same record offline. Why is a version number better than a timestamp for deciding what to do at sync time?

    A timestamp only says which edit was later. The base version says whether the two edits were made without knowledge of each other, which is the definition of a conflict.
  7. 7. Why must a radius search on latitude and longitude use a different box width for longitude than for latitude?

    Meridians converge towards the poles, so one degree of longitude shrinks with the cosine of the latitude, while a degree of latitude stays near 111 km.

Summary

  • Temporal databases remember history: valid time is when a fact is true in the world, transaction time is when the database knew it, and bitemporal tables keep both.
  • Time periods are usually half-open intervals, and corrections add rows instead of overwriting them.
  • Spatial databases store points, lines and polygons and answer measurement, relationship and search queries.
  • Spatial queries run in two steps: filter with bounding boxes and a spatial index, then refine with exact geometry.
  • R-trees, quadtrees, grids and geohash-style curves are the main spatial index structures.
  • Mobile, multimedia, time-series, graph and vector databases follow the same pattern of new data types, operators and indexes.
  • Mobile databases keep a local replica for disconnected operation and synchronize later; version numbers detect conflicts, and a conflict rule (last write wins, server wins, merge, ask the user) resolves them.
  • A GIS layers vector and raster data; its hard parts are coordinate systems, scale, accuracy, topology and privacy, and location queries use a cheap box filter before an exact distance test.

Next, see how large collections are analyzed in Data Mining and Knowledge Discovery, or go back to OLTP, OLAP and Data Warehouses.