14.1 Relational Database Foundations & Geospatial Extensions (SQL, PostGIS, SpatiaLite)

Relational Database Foundations & Geospatial Extensions (SQL, PostGIS, SpatiaLite)

Quick Summary: A geospatial database builds directly on classical relational database management system (RDBMS) principles. By augmenting standard relational tables with geometry and geography data types, spatial indexes, and OGC-compliant spatial SQL functions, spatial databases allow complex topological and geometric queries to execute directly within the database engine. Understanding normalization, referential integrity, planar versus geodetic calculations, and the mechanics of spatial SQL is essential for designing resilient enterprise spatial systems.

---

Relational Database Management System (RDBMS) Architecture

The relational database model, formulated by Edgar F. Codd in 1970, structures information into formal mathematical relations. In modern database terminology, these theoretical concepts translate directly into tables, rows, and columns:

• Relation (Table): A two-dimensional structure composed of rows and columns representing an entity set (e.g., parcels, water_valves, environmental_sensors).
• Tuple (Row / Record): A single, ordered instance of data within a relation, representing a specific discrete object or observation.
• Attribute (Column / Field): A named property or characteristic of the relation possessing a specific data type and domain constraint (e.g., valve_id as an integer, install_date as a date, geometry as a spatial object).

+-----------------------------------------------------------------------------------+
| RELATION: municipal_water_valves                                                  |
+------------+------------------+-------------------+----------------+--------------+
| valve_id   | valve_type       | operating_status  | facility_id    | geom (Point) |
| (PK: int)  | (varchar: 50)    | (varchar: 20)     | (FK: int)      | (Geometry)   |
+------------+------------------+-------------------+----------------+--------------+
| 1001       | Gate             | Open              | 402            | [Point 2D]   |
| 1002       | Butterfly        | Closed            | 402            | [Point 2D]   |
| 1003       | Ball             | Open              | 510            | [Point 2D]   |
+------------+------------------+-------------------+----------------+--------------+
   ^                                                    ^
   | PRIMARY KEY                                        | FOREIGN KEY
   (Enforces Entity Integrity)                          (Enforces Referential Integrity)

Primary Keys, Foreign Keys, and Referential Integrity

Data integrity within an RDBMS depends on formal constraints that prevent orphaned records and semantic corruption:

1. Primary Key (PK): A column or combination of columns that uniquely identifies each tuple within a relation. A primary key enforces entity integrity, meaning no primary key value can be null, and every record must possess a distinct identifier.
2. Candidate Key: Any column or set of columns capable of uniquely identifying a record. The primary key is chosen from among the candidate keys, while remaining candidates become alternate keys.
3. Foreign Key (FK): An attribute in one relation that references the primary key of another relation. Foreign keys enforce referential integrity, ensuring that relationships between tables remain valid.

Referential Action Behaviors on Delete and Update

When a parent row containing a primary key is deleted or modified, foreign key constraints dictate how the child table responds:

---

Database Normalization Theory

Normalization is the systematic design process of organizing attributes and relations in a relational database to minimize data redundancy and prevent modification anomalies. Unnormalized tables lead to three distinct failure modes:

1. Insertion Anomaly: Inability to record certain facts without artificially inventing unrelated data (e.g., unable to add a new contractor into the database until they are assigned to an active construction parcel).
2. Update Anomaly: Redundant storage of the same fact across multiple rows, where updating one record but missing another produces contradictory data (e.g., changing an inspector's phone number in one row while leaving old numbers in other rows).
3. Deletion Anomaly: Unintended loss of crucial information when deleting an unrelated record (e.g., deleting the last remaining fire hydrant in a subdivision inadvertently deletes the entire subdivision district definition).

                            THE NORMALIZATION PROGRESSION

   [Unnormalized Data] 
            |  (Eliminate repeating groups; enforce atomic scalar values)
            v
   [First Normal Form (1NF)]
            |  (Enforce 1NF + eliminate partial functional dependencies on composite keys)
            v
   [Second Normal Form (2NF)]
            |  (Enforce 2NF + eliminate transitive dependencies on non-key attributes)
            v
   [Third Normal Form (3NF)] ---> [Boyce-Codd Normal Form (BCNF)]

The Three Core Normal Forms

First Normal Form (1NF)

A relation is in 1NF if and only if:

• Every attribute value is atomic (indivisible; contains no comma-separated lists, JSON arrays, or multi-part structures stored in a single cell).
• There are no repeating groups or duplicate columns representing the same attribute.
• Each column has a unique name and a defined data type, and each tuple is uniquely identifiable via a primary key.

Violation Example: A parcel table containing a column named owner_names with values like "John Smith, Jane Doe" or multiple columns like phone_1, phone_2, phone_3.

Second Normal Form (2NF)

A relation is in 2NF if and only if:

• It is in First Normal Form (1NF).
• Every non-key attribute is fully functionally dependent on the complete primary key, eliminating partial functional dependencies.

[!NOTE] Second Normal Form is only relevant when a relation has a composite primary key (a primary key made of two or more columns). If a table's primary key consists of a single column, meeting 1NF automatically satisfies 2NF.

Violation Example: Consider a utility inspection table with a composite primary key consisting of (pole_id, inspection_date):

$\text{Primary Key} = (\text{pole\_id}, \text{inspection\_date})$

If this table includes an attribute called pole_material, a partial dependency exists: pole_material depends only on pole_id, not on the inspection_date. To resolve to 2NF, pole_material must be extracted into a separate utility_poles relation.

Third Normal Form (3NF)

A relation is in 3NF if and only if:

• It is in Second Normal Form (2NF).
• There are no transitive functional dependencies, meaning non-key attributes must depend solely on the primary key, and never on other non-key attributes.

$\text{Primary Key} \longrightarrow \text{Non-Key Column A} \longrightarrow \text{Non-Key Column B} \quad (\text{Transitive Dependency: Violates 3NF})$

Violation Example: An environmental sample site table with primary key site_id containing the attributes site_id, site_name, watershed_code, and watershed_name. While watershed_code depends on site_id, watershed_name functionally depends on watershed_code. To achieve 3NF, the watershed data must be separated into a distinct watersheds table.

Normalization Summary and Trade-Offs in Spatial Systems

[!TIP] Transactional spatial databases often benefit from normalization through 3NF to reduce anomalies, but physical design can deliberately denormalize when measured requirements justify it; spatial data warehouses and map tile caching pipelines (OLAP) frequently use deliberate denormalization (star or snowflake schemas) to minimize expensive SQL joins during high-throughput map rendering.

---

Geospatial Database Extensions & OGC Standards

Traditional relational databases cannot natively store or query spatial features because multidimensional geometries cannot be sorted or evaluated using standard scalar relational operators ($=, <, >$). To bridge this gap, modern database engines implement spatial extensions governed by the Open Geospatial Consortium (OGC) Simple Features for SQL (SFS) specification (ISO 19125).

Core Tenets of OGC Simple Features for SQL

The OGC SFS standard defines:

1. A formal geometric class hierarchy rooted in the abstract Geometry class (subclasses include Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection).
2. Standard spatial text and binary interchange formats: Well-Known Text (WKT) and Well-Known Binary (WKB).
3. Spatial metadata catalogs that track spatial references: geometry_columns (cataloging table name, column name, coordinate dimension, spatial reference ID, and geometry type) and spatial_ref_sys (storing EPSG codes, coordinate system names, and PROJ/WKT projection parameters).
4. A standardized library of SQL functions prefixed with ST_ (Spatial Type).

Major Geospatial Database Implementations

+-----------------------------------------------------------------------------------+
| ENTERPRISE RDBMS WITH SPATIAL EXTENSIONS                                          |
+-----------------------+------------------------+----------------------------------+
| Database Engine       | Spatial Extension      | Key Architectural Characteristics|
+-----------------------+------------------------+----------------------------------+
| PostgreSQL            | PostGIS                | Open-source gold standard; GEOS, |
|                       |                        | PROJ, and GDAL integration;      |
|                       |                        | supports GiST spatial indexing.  |
+-----------------------+------------------------+----------------------------------+
| Oracle Database       | Oracle Spatial /       | Object-relational SDO_GEOMETRY   |
|                       | Oracle Locator         | type; SDO_GTYPE and SDO_ORDINATES|
|                       |                        | arrays; R-tree spatial indexing. |
+-----------------------+------------------------+----------------------------------+
| Microsoft SQL Server  | Native Spatial Engine  | Built-in geometry (planar) and   |
|                       |                        | geography (geodetic) CLR types;  |
|                       |                        | multi-level grid indexing.       |
+-----------------------+------------------------+----------------------------------+
| SQLite                | SpatiaLite             | Lightweight, single-file server- |
|                       |                        | less database; C extension; ideal|
|                       |                        | for mobile and standalone GIS.   |
+-----------------------+------------------------+----------------------------------+

PostGIS Architecture

PostGIS extends PostgreSQL by introducing spatial data types (geometry, geography, raster), spatial indexing algorithms via Generalized Search Trees (GiST and SP-GiST), and hundreds of analytical functions. Under the hood, PostGIS links to robust open-source geometry libraries:

• GEOS (Geometry Engine Open Source): Executes 2D topological operators, spatial predicates, and geometry algorithms.
• PROJ: Handles coordinate reference system transformations and on-the-fly reprojections.
• SFCGAL: Extends PostGIS with advanced 3D volumetric operations, polyhedral surfaces, and TIN calculations.

SpatiaLite Architecture

SpatiaLite provides spatial database capabilities to SQLite. Because SQLite is a self-contained, serverless, single-file database engine, SpatiaLite delivers full OGC compliance without administrative overhead. It stores spatial tables in a single .sqlite file, making it the premier format for mobile field collection applications, embedded systems, and lightweight spatial tools.

---

Geometry vs. Geography Data Types

One of the most consequential decisions when designing a spatial database schema is choosing between the planar geometry and geodetic geography data types.

        PLANAR GEOMETRY                         GEODETIC GEOGRAPHY
     (Flat Euclidean Plane)                  (Curved Reference Ellipsoid)
     
       Y ^                                           N. Pole
         |                                           /     \
         |     Line AB                             /         \
         |  +-----------+                         |     AB    |
         |                                         \         /
         +--------------> X                          \     /
                                                     S. Pole
   * Coordinates: (X, Y) Projected             * Coordinates: (Lon, Lat) Spherical
   * Calculations: Flat Pythagorean            * Calculations: Geodesic Great Circles
   * Units: Meters / Feet                      * Units: Meters (Angles in Degrees)
   * High Performance; Local Scale             * Computationally Intensive; Global

Technical Comparison: Geometry vs. Geography

[!CAUTION] The Degree Trap: Storing latitude and longitude in a planar GEOMETRY column with SRID 4326 causes spatial functions to compute distances in decimal degrees. Executing ST_Buffer(geom, 100) on such a column does not create a 100-meter buffer; it creates a buffer of 100 decimal degrees (over 11,000 kilometers!), crashing database operations and corrupting spatial analysis.

---

Common Spatial SQL Functions and Queries

Spatial SQL functions operate directly on geometric attributes. They can be organized into four primary functional categories:

1. Spatial Constructors and Converters

Constructors instantiate geometric objects from text, binary, or external representations:

-- Create a planar point in State Plane Texas Central (EPSG 2277, US Survey Feet)
SELECT ST_GeomFromText('POINT(3105000.50 10050000.25)', 2277);

-- Create a geodetic point in WGS84 (EPSG 4326) and cast to GEOGRAPHY
SELECT ST_SetSRID(ST_MakePoint(-97.7431, 30.2672), 4326)::geography;

-- Export geometry to GeoJSON
SELECT ST_AsGeoJSON(geom) FROM municipal_boundaries WHERE city_name = 'Austin';

2. Spatial Measurement Functions

Measurement functions extract quantitative physical metrics from spatial objects:

-- Calculate area of parcel polygons in square meters
SELECT parcel_id, ST_Area(geom) AS area_sq_meters FROM cadastral_parcels;

-- Calculate length of water distribution mains in feet
SELECT pipe_id, ST_Length(geom) AS pipe_length_feet FROM water_network_pipes;

-- Calculate geodesic distance between two points on the WGS84 ellipsoid
SELECT ST_Distance(
    ST_MakePoint(-73.935242, 40.730610)::geography,
    ST_MakePoint(-118.243683, 34.052235)::geography
) AS nyc_to_la_meters;

3. Spatial Processing and Transformation Functions

These functions ingest geometries and output modified geometric structures:

-- Generate a 50-foot safety buffer around underground gas pipelines
SELECT pipeline_id, ST_Buffer(geom, 50.0) AS buffer_geom FROM gas_mains;

-- Extract the spatial intersection between flood zones and parcel polygons
SELECT 
    p.parcel_id, 
    ST_Intersection(p.geom, f.geom) AS flooded_geom
FROM cadastral_parcels p
JOIN flood_hazard_zones f 
    ON ST_Intersects(p.geom, f.geom);

4. Spatial Predicates and the DE-9IM Model

Spatial predicates return boolean (TRUE / FALSE) values based on topological relationships defined by the Dimensionally Extended 9-Intersection Model (DE-9IM):

• ST_Intersects(geomA, geomB): Returns true if any boundary, interior, or exterior space intersects.
• ST_Disjoint(geomA, geomB): Returns true if geometries have no points in common (opposite of intersects).
• ST_Contains(geomA, geomB): Returns true if Geometry B lies entirely within the interior or boundary of Geometry A, and their interiors intersect.
• ST_Within(geomA, geomB): Topologically equivalent to ST_Contains(geomB, geomA).
• ST_Touches(geomA, geomB): Returns true if geometries share a boundary point, but their interiors do not intersect.

-- Identify all industrial facilities located within a protected groundwater basin
SELECT 
    f.facility_id,
    f.facility_name
FROM industrial_facilities f
JOIN aquifer_protection_zones a
    ON ST_Within(f.geom, a.geom)
WHERE a.protection_level = 'High';

---

Summary of Common Exam Traps

[!CAUTION] Exam Trap 14.1.1: Order of Arguments in ST_Contains versus ST_Within. The spatial predicate ST_Contains(A, B) tests if A encloses B. Conversely, ST_Within(A, B) tests if A is inside B. Inverting the arguments produces the opposite topological test. Remember: ST_Contains(A, B) is mathematically identical to ST_Within(B, A).

[!CAUTION] Exam Trap 14.1.2: Confusing 2NF and 3NF Violations on Composite Keys. A table cannot violate 2NF unless it has a composite primary key. If a test question describes a table with a single-column primary key (such as parcel_id) and an attribute that depends on another non-key attribute (e.g., zoning_code determining zoning_description), this is strictly a 3NF violation (transitive dependency), NOT a 2NF violation.

[!CAUTION] Exam Trap 14.1.3: Referential Integrity Actions on Parent Record Deletions. Be prepared to distinguish between CASCADE, RESTRICT, and SET NULL. Deleting a parent row under RESTRICT aborts the operation if child records exist. Under CASCADE, child rows are automatically erased without warning. Under SET NULL, child rows remain but their foreign keys become null.