18-Geom-A7 Geospatial Information Systems · December 2018
Nivaar worked solution (AI-drafted; not reviewed by a licensed engineer)
Paper format. National Exams, December 2018 — 04-Geom-A7, Geospatial Information Systems. Closed book (one approved Casio or Sharp calculator permitted); duration 3 hours. Fifteen (15) questions are provided and any ten (10) constitute a complete paper; each question is of equal value (10 marks), so a complete paper totals 100 marks. Most answers are essay-format; clarity and organization count. All fifteen questions are solved below for completeness.
Longley, Goodchild, Maguire & Rhind, Geographic Information Systems and Science (4th ed.); P. Bolstad, GIS Fundamentals (5th ed.); Worboys & Duckham, GIS: A Computing Perspective (2nd ed.); Burrough, McDonnell & Lloyd, Principles of Geographical Information Systems (3rd ed.); de Smith, Goodchild & Longley, Geospatial Analysis; OGC Simple Feature Access (ISO 19125); ISO 19157 Geographic information — Data quality; ISO 19115 Metadata.
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.
(a) SQL in geospatial data management. SQL is the standard language for defining and manipulating data in relational (and object-relational) databases, which is where most GIS attribute data — and, in a spatial database, the geometry itself — resides. Its data-definition part (DDL: CREATE, ALTER, DROP) builds the tables and their schema, and its data-manipulation part (DML: INSERT, UPDATE, DELETE, SELECT) adds, edits, removes and retrieves records. Spatial extensions (the OGC Simple-Feature SQL implemented by PostGIS, Oracle Spatial, SpatiaLite, etc.) add a geometry column type and spatial functions and predicates — ST_Intersects, ST_Within, ST_Buffer, ST_Area — so a single SQL statement can query both attributes and spatial relationships (e.g., "select parcels whose geometry intersects a flood polygon and whose zoning = residential").
(b) Row-by-row distinction.
| Row | Script 1 | Script 2 | Distinction |
|---|---|---|---|
| 1 | INSERT INTO building VALUES (700, '12 Queen W St, Toronto', 'commercial'); | UPDATE building SET type='commercial' WHERE id=700; | Create vs edit. Script 1 adds a brand-new row (id 700 with all three field values); it fails or duplicates if id 700 already exists. Script 2 modifies an existing row, changing only its type field where id = 700; it changes nothing if that row does not exist. |
| 2 | DELETE FROM building WHERE id=700; | INSERT INTO building VALUES (700, '12 Queen W St, Toronto', 'commercial'); | Inverse operations. Script 1 removes the record with id 700 (the row is gone). Script 2 creates that record. One destroys a row, the other adds it — they undo each other. |
| 3 | SELECT * FROM building; | SELECT address FROM building; | All columns vs a projection. Both are read-only queries that change no data. Script 1 returns every column (id, address, type) for all rows; Script 2 returns only the address column — a relational "projection" onto one attribute. |
| 4 | DROP TABLE building; | DELETE FROM building; | Remove the table vs empty it. Script 1 (DDL) deletes the entire table — its rows, its schema and the object itself cease to exist. Script 2 (DML, no WHERE) deletes all rows but keeps the empty table structure ready for new records. |
