Spatial databases

A GIS for a single analyst can live in a folder of files. The district's GIS could not. The office staff would take water orders into it, the ditch riders would read and update it from their trucks, the maintenance foreman would log inspections, and Coulee Geomatics would run analyses on it, all at the same time and all on the same canal network. A water order entered in the morning had to appear on a ditch rider's tablet within minutes, and two people editing the same turnout must never overwrite each other's work silently. Those requirements call for a database management system. This lesson covers the relational model on which most databases are built, how a spatial database adds geometry and spatial indexes to it, and how the district's database was designed.

Why a database

A database management system (DBMS) stores data in a structured way and manages access to it. Compared with a set of files, it offers integrity rules that prevent invalid data from being stored, concurrency control so that many users can read and write at once without corrupting the data, transactions so that a group of changes either all happen or none do, security so that each user sees and edits only what they should, backup and recovery, and a query language for asking questions of the data. A spreadsheet of water users has none of these: two people saving the same file overwrite each other, nothing stops a typing error in a field number, and there is no record of who changed what.

The relational model

The relational model, introduced by Edgar Codd in 1970, organizes data in tables (relations) of rows (records) and columns (attributes). Each table describes one kind of thing, each row one instance, and each column one property with a defined type. A primary key is a column or set of columns whose value identifies each row uniquely. A foreign key is a column in one table that holds the primary key of a row in another, which is how relationships are expressed: each water order holds the identifier of the field it is for, and each field holds the identifier of the turnout that serves it. Relationships may be one-to-one, one-to-many (one lateral has many turnouts) or many-to-many (a field may have several owners, and an owner many fields). Many-to-many relationships are represented by a linking table, such as the ownership table that holds a field identifier, a user identifier and a share.

Normalization is the process of organizing tables so that each fact is stored once. In the district's old spreadsheet, each row of water orders repeated the water user's name, address and phone number. When a user changed phone numbers, some rows were updated and others were not, and the district had three different numbers for the same person. In a normalized design the user's details are stored once, in the water user table, and orders refer to the user only by key. The first three normal forms capture most of the practical rules: each cell holds a single value, each non-key column depends on the whole key, and no non-key column depends on another non-key column. Analysis databases are sometimes deliberately denormalized for speed, but an operational database that many people edit should be normalized.

SQL

Relational databases are queried with SQL, the Structured Query Language, standardized by ISO. A SELECT statement names the columns wanted, the tables they come from, the conditions rows must meet and how the results are grouped and ordered. Joins combine tables by matching keys. The query below, in plain words, asks for each water user's total ordered volume in July, weighted by their share of each field:

SELECT u.name, SUM(o.volume_af * w.share) FROM water_order o JOIN ownership w ON w.field_id = o.field_id JOIN water_user u ON u.user_id = w.user_id WHERE o.order_date LIKE '2026-07-%' GROUP BY u.name;

SQL also defines and changes the structure of the database (CREATE TABLE, ALTER TABLE), changes data (INSERT, UPDATE, DELETE) and controls access (GRANT, REVOKE). Constraints declared in the table definitions, such as NOT NULL, UNIQUE, CHECK and foreign keys, are enforced by the database for every user and every program, which is far more reliable than relying on each application to check its own input.

Adding geometry

A spatial database extends a relational database with geometry types, spatial functions and spatial indexes. The ISO SQL/MM Spatial standard and the OGC Simple Features for SQL specification define how this is done, and PostGIS, the spatial extension of the open-source PostgreSQL database, is the most widely used implementation. Commercial databases offer similar extensions, and SQLite has SpatiaLite and GeoPackage. In a spatial database a field is simply a row in the field table with a geometry column, and spatial questions become SQL queries using functions such as ST_Intersects, ST_Contains, ST_Distance, ST_Buffer and ST_Area. Asking which field contains a reported washout is a query, not a manual search through a map.

PostGIS distinguishes two kinds of spatial column. A geometry column holds coordinates in a stated CRS and computes with plane geometry, appropriate for projected data over a limited area. A geography column holds longitude and latitude and computes on the ellipsoid, appropriate for global data. The district used geometry columns in its projected CRS throughout, with every column constrained to one geometry type and one CRS, so that a polygon in the wrong system could not be inserted by mistake.

Spatial indexes

Without an index, finding the field that contains a point means testing every field, and a point-in-polygon test on a field with hundreds of vertices is not cheap. Databases speed up attribute queries with B-tree indexes, which sort values so that a search takes a logarithmic number of steps. Coordinates in two dimensions cannot be sorted usefully along one key, so spatial databases use spatial indexes based on minimum bounding rectangles (MBRs), the smallest axis-aligned rectangle enclosing each geometry. The R-tree, proposed by Antonin Guttman in 1984, groups nearby rectangles into larger rectangles, those into larger ones again, and so on up to a root. A search descends only into branches whose rectangles overlap the query, so most of the data are never touched. Quadtrees, which divide space recursively into four quadrants, and grid indexes are alternatives.

Spatial queries therefore run in two steps. The filter step uses the index to find the candidate features whose bounding rectangles satisfy the query, a fast but approximate test. The refine step applies the exact geometric test to the candidates only. For compact shapes such as fields, the filter step leaves very few candidates; for long diagonal lines such as a lateral, the bounding rectangle covers much empty space and the filter is less effective, which is one reason long lines are sometimes split into segments for indexing.

import sqlite3, random
random.seed(7)
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE water_user (user_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE field (field_id INTEGER PRIMARY KEY, field_no TEXT UNIQUE, area_ha REAL,
                    xmin REAL, ymin REAL, xmax REAL, ymax REAL, wkt TEXT);
CREATE TABLE ownership (field_id INTEGER REFERENCES field, user_id INTEGER REFERENCES water_user,
                        share REAL, PRIMARY KEY (field_id, user_id));
CREATE TABLE water_order (order_id INTEGER PRIMARY KEY, field_id INTEGER REFERENCES field,
                          order_date TEXT, volume_af REAL);
CREATE INDEX field_bbox ON field (xmin, xmax, ymin, ymax);
""")
db.executemany("INSERT INTO water_user VALUES (?, ?)",
               [(1, "Berg Farms Ltd."), (2, "M. and J. Okafor"), (3, "Prairie Seed Co-op"), (4, "D. Sauer")])
# 400 fields in 800 m blocks; block edges lean east or west, and each block is split into two fields
S = [random.randint(-150, 150) for _ in range(11)]       # east shift of each block row boundary
fields = []
for i in range(20):
    for j in range(10):
        x, y = i * 800.0, j * 800.0
        cut = 300 + random.randint(0, 200)
        for k, (a, b) in enumerate(((x, x + cut), (x + cut, x + 800))):
            poly = [(a + S[j], y), (b + S[j], y), (b + S[j + 1], y + 800), (a + S[j + 1], y + 800)]
            fid = len(fields) + 1
            wkt = "POLYGON((" + ", ".join(f"{p:.0f} {q:.0f}" for p, q in poly + poly[:1]) + "))"
            xs = [p for p, q in poly]
            fields.append((fid, f"{i:02d}-{j:02d}{'ab'[k]}", (b - a) * 800 / 10000, min(xs), y, max(xs), y + 800, wkt))
db.executemany("INSERT INTO field VALUES (?, ?, ?, ?, ?, ?, ?, ?)", fields)
db.executemany("INSERT INTO ownership VALUES (?, ?, ?)",
               [(f[0], 1 + f[0] % 4, 1.0) for f in fields if f[0] != 57] + [(57, 2, 0.5), (57, 3, 0.5)])
db.executemany("INSERT INTO water_order (field_id, order_date, volume_af) VALUES (?, ?, ?)",
               [(random.randint(1, 400), f"2026-07-{d:02d}", round(random.uniform(4, 32), 1)) for d in range(1, 31) for _ in range(6)])

# Attribute query with joins: July volume ordered by each water user, weighted by ownership share
q = """SELECT u.name, COUNT(o.order_id), ROUND(SUM(o.volume_af * w.share), 1)
       FROM water_order o JOIN ownership w ON w.field_id = o.field_id JOIN water_user u ON u.user_id = w.user_id
       GROUP BY u.name ORDER BY 3 DESC"""
for name, n, v in db.execute(q):
    print(f"{name:20s} {n:3d} orders  {v:7.1f} acre-ft")

# Spatial query in two steps: bounding-box filter (uses the index), then exact test on the candidates
def contains(wkt, x, y):
    pts = [tuple(map(float, p.split())) for p in wkt[9:-2].split(", ")]
    c = False
    for (x1, y1), (x2, y2) in zip(pts, pts[1:]):
        if (y1 > y) != (y2 > y) and x < x1 + (y - y1) * (x2 - x1) / (y2 - y1): c = not c
    return c
x, y = 1745.0, 6830.0                      # reported washout
cand = db.execute("SELECT field_no, wkt FROM field WHERE xmin <= ? AND xmax >= ? AND ymin <= ? AND ymax >= ?",
                  (x, x, y, y)).fetchall()
hit = [f for f, w in cand if contains(w, x, y)]
print(f"\nwashout at ({x:.0f}, {y:.0f}): {len(cand)} candidates from the box filter out of 400 fields, {len(hit)} after the exact test -> {hit[0]}")
owners = db.execute("""SELECT u.name, w.share FROM field f JOIN ownership w ON w.field_id = f.field_id
                       JOIN water_user u ON u.user_id = w.user_id WHERE f.field_no = ?""", (hit[0],)).fetchall()
print("call:", "; ".join(f"{n} ({s:.0%})" for n, s in owners))
print("query plan:", db.execute("EXPLAIN QUERY PLAN SELECT field_no FROM field WHERE xmin <= 1 AND xmax >= 1").fetchall()[0][3])

Prairie Seed Co-op    50 orders   1024.0 acre-ft
D. Sauer              54 orders    966.9 acre-ft
M. and J. Okafor      41 orders    686.4 acre-ft
Berg Farms Ltd.       36 orders    659.2 acre-ft

washout at (1745, 6830): 2 candidates from the box filter out of 400 fields, 1 after the exact test -> 02-08a
call: M. and J. Okafor (50%); Prairie Seed Co-op (50%)
query plan: SEARCH field USING INDEX field_bbox (xmin<?)

The join totals each user's July orders, counting only half of the orders on field 02-08a for each of its two owners. The washout query used the bounding-box index to reduce 400 fields to 2 candidates, and the exact test kept one, whose two owners are listed with their shares. The simple index here is an ordinary B-tree on the rectangle coordinates; the query plan shows it can use only one coordinate to narrow the search. A true R-tree uses both axes at once, and GeoPackage files and spatial databases build one automatically for each geometry column.

Transactions, concurrency and versions

A transaction groups operations that must succeed or fail together. When a ditch rider replaces a culvert, the old culvert's record is closed with a removal date, a new record is created, the inspection is logged and the lateral's network connectivity is updated. If the tablet loses its connection halfway, a transaction ensures that the database is left either with all of these changes or none, never with a culvert removed and no replacement. Databases guarantee the properties summarized as ACID: atomicity, consistency, isolation and durability.

Concurrent editing raises a further problem. Two people may open the same turnout and change different attributes, or the same attribute differently. Databases use locking or multi-version concurrency control so that each transaction sees a consistent snapshot, and conflicts are detected when changes are saved. For long editing sessions, such as a technician redrawing a lateral over several days, GIS software offers versioning or branch editing, in which edits are made in a separate version and reconciled with the main version when finished, with conflicts reviewed by a person. Field apps that work offline (Lesson 12) synchronize their changes the same way.

Every change should also be traceable. The district's database recorded, for every edit, who made it, when, and through which application, and kept removed or replaced features with their end dates. That history answered questions such as when a culvert was last replaced and who changed a field's irrigated area, and it allowed mistakes to be undone.

Designing the district's database

Database design starts from the needs assessment, not from the data that happen to exist. Leah and Theo listed the entities the district deals with (laterals, structures, turnouts, fields, parcels, water users, orders, deliveries, inspections, wells), their attributes and the relationships among them, and drew a conceptual model with the district's staff. They then translated it into tables with keys, types, domains and constraints, the logical model, and finally created the physical database with indexes, permissions and storage settings. The public parcel layer was loaded into its own schema and refreshed monthly; the district's tables referred to parcels only through the legal description, so a refresh could never break district data.

Personal information such as names, addresses, phone numbers and water use by person needs protection. Montana's constitution recognizes a right of individual privacy, the state's public records laws govern what a public agency must release, and contracts with water users may set further limits. Permissions in the database are the technical means of limiting who can see such information; Lesson 12 returns to privacy when data are published.