12.1 Attribute and Spatial Query Languages: SQL WHERE Clauses and Spatial Predicates

12.1 Attribute and Spatial Query Languages: SQL WHERE Clauses and Spatial Predicates

Quick Summary: Vector analysis begins with feature selection. Geospatial information systems integrate two distinct query paradigms: relational attribute queries governed by Structured Query Language (SQL) and geometric topological queries governed by spatial predicates. Relational queries evaluate alphanumeric properties using three-valued boolean logic (TRUE, FALSE, UNKNOWN), requiring explicit handling of NULL states and careful management of operator precedence (NOT > AND > OR). Spatial queries evaluate geometric relationships defined by the Open Geospatial Consortium (OGC) Simple Features standard and the Dimensionally Extended 9-Intersection Model (DE-9IM). Understanding the rigorous mathematical constraints of predicates such as ST_Intersects, ST_Contains, ST_Touches, ST_Crosses, and ST_Overlaps is essential for constructing compound spatial-attribute workflows.

---

1. Relational Attribute Querying: ANSI SQL Architecture in GIS

Geospatial datasets fundamentally link two data domains: geometric coordinate primitives representing location and tabular records describing real-world properties. The standard mechanism for interrogating attribute tables is Structured Query Language (SQL). In desktop GIS environments, web mapping application programming interfaces (APIs), and enterprise spatial database management systems (such as PostGIS, Oracle Spatial, and Microsoft SQL Server Spatial), SQL provides the declarative syntax required to isolate target feature records.

   +-------------------------------------------------------------------------+
   |                             SQL QUERY ENGINE                            |
   |                                                                         |
   |   SELECT [Fields / Columns]       --> Output Projection                 |
   |   FROM   [Spatial Table / View]   --> Data Source                       |
   |   WHERE  [Filter Predicates]      --> Row-level Boolean Evaluation      |
   +-------------------------------------------------------------------------+

The Standard SQL Query Anatomy

In standard spatial SQL, an attribute query comprises three core clauses:

1. SELECT: Specifies which attribute fields or calculated expressions are returned. In desktop selection tools, this is often implicit (selecting the entire feature geometry and record).
2. FROM: Identifies the target table, view, or feature class from which records are extracted.
3. WHERE: Establishes the conditional filtering criteria. Only records for which the expression evaluates to TRUE are selected or passed to downstream geoprocessing tools.

Comparison Operators and Syntax

Relational attribute filters evaluate values using standard mathematical comparison operators:

String Pattern Matching: The LIKE Operator and Wildcards

When text strings cannot be matched exactly due to variable suffixes, naming conventions, or typographical inconsistencies, SQL provides pattern matching through the LIKE operator alongside wildcard characters:

• Percent Sign (%): Represents any sequence of zero, one, or multiple arbitrary characters. For example, PARCEL_ID LIKE 'TX-2024-%' matches 'TX-2024-1', 'TX-2024-9984', and 'TX-2024-'.
• Underscore (_): Represents exactly one single character. For example, LOT_CODE LIKE 'Zone__' matches 'Zone01' and 'ZoneAB', but does not match 'Zone1' or 'Zone001'.

   Target String:   'Highway 101 North'
   Pattern 'High%'  --> MATCH (Wildcard matches 'way 101 North')
   Pattern 'H_gh%'  --> MATCH (Underscore matches 'i', % matches remainder)
   Pattern 'H_way%' --> NO MATCH ('i' and 'g' represent two characters, requiring '__')

[!NOTE] While ANSI SQL establishes % and _ as universal standards, desktop desktop software and file-based geodatabases historically exhibited variations. For example, Microsoft Access-based Personal Geodatabases (.mdb) utilized * and ?, while shapefile queries executed within dBase tables were strictly case-sensitive. Enterprise spatial engines (PostGIS, Oracle, SQL Server) strictly enforce ANSI % and _.

---

2. Boolean Logic, Operator Precedence & Three-Valued Logic

Constructing multi-criteria attribute queries requires combining atomic expressions using boolean logical operators: AND, OR, and NOT.

The Standard Operator Precedence Hierarchy

When multiple logical operators appear in a single WHERE clause without explicit grouping, the SQL query parser evaluates them according to strict operator precedence:

1. Arithmetic Operators: Multiplication (*), Division (/), Addition (+), Subtraction (-)
2. Comparison Operators: =, <>, <, >, <=, >=, LIKE, IN, BETWEEN
3. Logical NOT: Negates the immediate subsequent condition.
4. Logical AND: Evaluated before any OR operations (conjunction takes priority).
5. Logical OR: Evaluated last (disjunction).

   Unparenthesized Query: WHERE LandUse = 'Forest' OR LandUse = 'Wetland' AND Slope > 15
   Implicit Evaluation:   WHERE LandUse = 'Forest' OR (LandUse = 'Wetland' AND Slope > 15)
   Intended Evaluation:   WHERE (LandUse = 'Forest' OR LandUse = 'Wetland') AND Slope > 15

Failing to use parentheses to override default precedence is one of the most common analytical errors in geospatial filtering. In the example above, the unparenthesized query returns all forest parcels regardless of slope, alongside wetland parcels that possess steep slopes, directly corrupting site suitability models.

Three-Valued Logic and NULL Value Handling

In relational databases, NULL represents the absolute absence of data—it signifies an unknown, unrecorded, or inapplicable state. NULL is mathematically distinct from zero (0), a blank string (''), or whitespace.

SQL operates under Three-Valued Logic (3VL), where boolean expressions evaluate to one of three states:

• TRUE
• FALSE
• UNKNOWN

Whenever any standard arithmetic or comparison operator encounters a NULL operand, the result is automatically UNKNOWN:

$\text{NULL} = \text{NULL} \implies \text{UNKNOWN}$ $\text{NULL} <> 'Residential' \implies \text{UNKNOWN}$ $\text{NULL} + 10 \implies \text{NULL}$

[!CAUTION] The NULL Exclusion Trap in WHERE Clauses: A SQL WHERE clause filters out any record whose condition does NOT evaluate strictly to TRUE. If an expression evaluates to UNKNOWN, that record is discarded. Consequently, the query WHERE ZONING <> 'Commercial' will eliminate all parcels zoned 'Commercial', but it will also silently drop all parcels where ZONING IS NULL, because UNKNOWN fails the truth test. To include unassigned parcels, the query must be explicitly constructed as: WHERE ZONING <> 'Commercial' OR ZONING IS NULL.

To test for the presence or absence of data, analysts must use the explicit unary operators:

• IS NULL (returns TRUE if the field contains no value)
• IS NOT NULL (returns TRUE if a populated value exists)

---

3. Spatial Queries and Topological Relationship Predicates

While attribute queries interrogate the relational table, spatial queries evaluate topological relationships between geometries in coordinate space. The Open Geospatial Consortium (OGC) and ISO/IEC 13249-3 (SQL/MM) define standardized spatial predicates that take two geometries ($A$ and $B$) as input and return a boolean TRUE or FALSE.

These predicates are formally derived from the Dimensionally Extended 9-Intersection Model (DE-9IM), which evaluates the intersections between the Interior ($I$), Boundary ($B$), and Exterior ($E$) of two geometric features:

             +-------------------------------------------------+
             |                  DE-9IM MATRIX                  |
             |                                                 |
             |               I(B)       B(B)       E(B)        |
             |   I(A)   [ dim(I∩I)  dim(I∩B)  dim(I∩E) ]       |
             |   B(A)   [ dim(B∩I)  dim(B∩B)  dim(B∩E) ]       |
             |   E(A)   [ dim(E∩I)  dim(E∩B)  dim(E∩E) ]       |
             +-------------------------------------------------+

The Core OGC Spatial Predicates

   ST_Intersects: Shares ANY coordinate space (Boundary or Interior)
   [ Geometry A ] <---- Shared Points ----> [ Geometry B ]
   
   ST_Touches: Meets ONLY at Boundaries (Interiors do NOT intersect)
   [ Poly A ]|[ Poly B ]        (Point/Point touch is mathematically impossible)
   
   ST_Crosses: Geometries cross; intersection dimension < max dimension
   ======[ Line A ]======>
       |  [ Poly B ]   |
       +---------------+
       
   ST_Overlaps: Same dimension; partial intersection; neither contains the other
   [ Poly A [ Shared Area ] Poly B ]

1. ST_Intersects and ST_Disjoint

• ST_Intersects(A, B): Returns TRUE if geometries $A$ and $B$ have at least one point in common. This occurs whenever their interiors intersect, their boundaries intersect, or an interior intersects a boundary. It is the most computationally efficient and widely used spatial filter.
• ST_Disjoint(A, B): The exact mathematical inverse of ST_Intersects. Returns TRUE if geometries $A$ and $B$ share no points whatsoever ($A \cap B = \emptyset$). If ST_Intersects is TRUE, ST_Disjoint is guaranteed to be FALSE.

2. ST_Contains and ST_Within

• ST_Contains(A, B): Returns TRUE if no points of geometry $B$ lie in the exterior of geometry $A$, and at least one point of the interior of $B$ lies in the interior of $A$. In formal terms: $B \subset A$ and $I(A) \cap I(B) \ne \emptyset$.
• ST_Within(A, B): The exact inverse of ST_Contains. ST_Within(A, B) = ST_Contains(B, A). If a point lies strictly on the boundary of a polygon, standard OGC ST_Contains returns FALSE because the point's interior does not intersect the polygon's interior.

3. ST_Touches

• Returns TRUE if geometries $A$ and $B$ have at least one boundary point in common, but their interiors do not intersect ($I(A) \cap I(B) = \emptyset$).
• Supported Dimension Combinations: Point/Line, Point/Polygon, Line/Line, Line/Polygon, Polygon/Polygon.
• Geometric Constraint: Two Point features cannot touch because a Point has an interior (dimension 0) but possesses no boundary (empty set). The intersection of two identical points is an interior intersection, satisfying ST_Intersects, not ST_Touches.

4. ST_Crosses

• Returns TRUE if geometries $A$ and $B$ share some, but not all, interior points, and the dimension of the resulting intersection is strictly less than the maximum dimension of the two inputs.
• Applicable Geometries: Line/Line (intersecting at a 0D point), Line/Polygon (line passes through polygon interior and crosses its boundary), Point/Line (point lies in line interior).
• Geometric Constraint: ST_Crosses never applies to Polygon/Polygon combinations. Two intersecting polygons produce an intersection that has dimension 2 (area), which equals the dimension of the inputs, thereby violating the definition.

5. ST_Overlaps

• Returns TRUE if geometries $A$ and $B$ have the same topological dimension, share some but not all interior points, and the dimension of their intersection equals the dimension of the inputs, such that neither geometry is completely contained within the other.
• Applicable Geometries: Polygon/Polygon (overlapping area), Line/Line (lines sharing a linear segment, not just a junction point), Point/Point (multipoints sharing points).
• Geometric Constraint: A Line and a Polygon can never overlap under OGC definitions because their dimensions (1 and 2) are not equal.

Spatial Predicate Dimensional Compatibility Matrix

---

4. Compound Queries: Synthesizing Spatial and Attribute Criteria

In practical GIS analysis, problems are rarely resolved by attribute or spatial queries alone. Geospatial analysts construct compound queries that simultaneously evaluate non-spatial attributes and topological relationships.

Multi-Step Desktop Selection Workflows

In desktop GIS interfaces, compound queries are traditionally executed through sequential selection mechanisms:

1. Step 1: Initial Selection by Attribute: Query the parcel layer where ZONING = 'Industrial' AND ASSESSED_VAL > 1000000.
2. Step 2: Selection by Location (Subset): Select from the currently selected parcel features those that ST_Within or ST_Intersects a 500-meter buffer of a rail freight corridor.
3. Step 3: Remove from Selection: De-select any features that ST_Intersects a regulated wetland polygon layer.

Programmatic Spatial SQL Compound Queries

In enterprise spatial databases, multi-step operations are unified into a single declarative SQL query leveraging spatial functions and joins:

-- Identify candidate commercial redevelopment parcels within 250 meters of transit stations
-- that are not constrained by flood hazard zones
SELECT 
    p.parcel_id,
    p.owner_name,
    p.assessed_value,
    p.geom
FROM 
    cadastral_parcels AS p
INNER JOIN 
    transit_stations AS t
    ON ST_DWithin(p.geom, t.geom, 250.0)
WHERE 
    p.zoning_type IN ('C-1', 'C-2', 'CBD')
    AND p.building_sqft >= 10000
    AND NOT EXISTS (
        SELECT 1 
        FROM fema_floodplains AS f 
        WHERE ST_Intersects(p.geom, f.geom) 
          AND f.zone_code = 'AE'
    );

In this compound query, ST_DWithin calculates metric spatial proximity using underlying spatial R-Tree indices, IN and >= filter alphanumeric records, and the negated subquery (NOT EXISTS with ST_Intersects) eliminates parcels subject to base floodplain inundation.

---

5. Practical Geospatial Scenario: Municipal Hazard Assessment

Scenario Context

A municipal GIS department is tasked with identifying commercial structures at risk during hazardous materials transit incidents. The workflow requires identifying parcels that meet three distinct criteria:

1. The property must have a primary structural use classified as Educational, Healthcare, or Public Assembly.
2. The assessed valuation must exceed $250,000, or the occupant capacity must exceed 100 persons.
3. The property boundary must fall completely or partially within a 300-meter evacuation buffer of a designated chemical transportation route.

Analytical Pitfall and Execution

The analyst writes the initial query without parentheses:

WHERE USE_TYPE = 'School' OR USE_TYPE = 'Hospital' AND VALUATION > 250000

Because AND takes precedence over OR, the database evaluates this as: "Select all schools regardless of valuation, plus hospitals valued over $250,000." Low-value healthcare facilities are completely omitted, while unpopulated administrative school district storage buildings are erroneously selected.

Furthermore, the analyst attempts to determine whether utility pipes intersect parcel boundaries using ST_Overlaps. Because utility pipes are LineStrings (dimension 1) and parcel boundaries are Polygons (dimension 2), ST_Overlaps evaluates to FALSE for every single feature because their topological dimensions are unequal. The analyst corrects the predicate to ST_Crosses or ST_Intersects, immediately resolving the query.

---

6. Common Exam Traps & Pitfalls

[!CAUTION] Exam Trap 12.1.1: The NULL Comparison Failure. Never use = NULL or <> NULL. In SQL, testing WHERE STATUS = NULL is syntactically invalid or mathematically returns UNKNOWN, resulting in zero records returned. You must use IS NULL or IS NOT NULL. Remember that any standard inequality query such as WHERE TAX_EXEMPT <> 'Y' will discard all records where TAX_EXEMPT is NULL.

[!CAUTION] Exam Trap 12.1.2: Misapplying ST_Crosses vs. ST_Overlaps. An exam question will ask which spatial predicate determines if two polygon forest stands share common area without either containing the other. The incorrect distractor is ST_Crosses. Remember the strict DE-9IM rule: ST_Crosses cannot be applied to polygon/polygon pairs. Polygons sharing partial area must be queried using ST_Overlaps or ST_Intersects.

[!CAUTION] Exam Trap 12.1.3: Topological Boundaries and ST_Touches. Two point features can never satisfy ST_Touches. Points have dimension 0 and possess an interior, but their boundary is the empty set ($\emptyset$). Because ST_Touches requires intersection strictly at boundaries without interior intersection, points can only intersect (ST_Intersects) or be disjoint (ST_Disjoint).