Spatial Data Management, Databases and Standards
Questions test whether you can design a schema, index spatial data and exchange it without silent loss.
Relational foundations
Normalisation removes redundancy: 1NF requires atomic values, 2NF removes partial dependencies on part of a composite key, 3NF removes transitive dependencies. A parcel table keyed by parcel_id but carrying owner attributes belongs in two tables. ACID transactions guarantee atomicity, consistency, isolation and durability, so a failed bulk edit rolls back cleanly.
Spatial types and indexing
OGC Simple Features defines Geometry, Point, LineString, Polygon and their collections, plus validity rules (rings closed, correct orientation, no self-intersection). Predicates include ST_Intersects, ST_Contains, ST_Within, ST_Touches and ST_Crosses. Spatial indexes (R-tree, GiST, quadtree) build bounding-box hierarchies so a query prunes by extent before testing true geometry.
Standards and exchange
EPSG codes identify coordinate reference systems: EPSG:4326 is WGS 84 geographic; EPSG:25832 is ETRS89 / UTM zone 32N. ISO 19115 covers metadata, ISO 19107 geometry, ISO 19152 LADM for land administration. Formats: GeoPackage (OGC, SQLite-based), GML (XML), Shapefile (multi-file, 2 GB limit, 10-character field names, no null support in the DBF) and GeoJSON (WGS 84 only).
Worked example: to find every building inside a flood polygon, write SELECT b.* FROM buildings b JOIN flood f ON ST_Intersects(b.geom, f.geom) WHERE f.id = 3. Filter on f.id first, ensure both layers share a CRS, and if they do not, wrap the building geometry in ST_Transform(b.geom, 25832).
Traps
- Comparing geometries stored in different coordinate reference systems.
- Treating a bounding-box test as a true intersection test.
- Using Shapefile where field names exceed 10 characters, or GeoJSON for projected coordinates.