Question 23 of 23: SQL in a GIS

Question 23: SQL in a GIS (5 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

SQL (Structured Query Language) is the standard declarative language for retrieving and manipulating data held in a relational (or spatial) database, and a GIS uses it to interrogate both attribute and spatial data. In its classic role, SQL performs attribute queries — selecting, filtering, joining and aggregating feature records by their attribute values through SELECT … FROM … WHERE statements. In a spatial DBMS, SQL is extended with spatial operators and functions (following the OGC Simple Features standard — e.g., ST_Intersects, ST_Within, ST_Contains, ST_Buffer, ST_Distance) so that the same language can pose spatial questions directly against feature geometry. This lets a GIS combine thematic and spatial conditions in one statement, run the query efficiently inside the database, and reuse it across applications. An attribute-query example — select all residential parcels larger than 500 m²:

SELECT parcel_id, owner
FROM   parcels
WHERE  zoning = 'residential' AND area_m2 > 500;

A spatial-query example — find every parcel that intersects the mapped flood zone, using a spatial function:

SELECT p.parcel_id
FROM   parcels p, flood_zone f
WHERE  ST_Intersects(p.geom, f.geom);

The first filters on attributes alone; the second uses the geometry column and a spatial predicate, illustrating how spatial SQL unifies "what" and "where" in a single query.