Spatial Data Management System and Spatial Data Infrastructure

1

This section covers the terminology of databases, the components and functions of a DBMS and of a spatial DBMS, the three-schema architecture, and database access languages.

2

Terminology Term Meaning Data / information Raw facts / data processed and given meaning for a purpose Database An organised, integrated and shared collection of logically related data with minimum redundancy DBMS The software that defines, creates, stores, retrieves, updates, secures and controls access to a database (PostgreSQL, Oracle, SQL Server, MySQL) SDBMS A DBMS extended with spatial data types, spatial operators/functions, spatial indexing and a spatial query language Relation (table), tuple (row/record), attribute (column/field) The structures of the relational model; domain = the permitted set of values of an attribute Schema / instance The structure (definition) of the database / the data held in it at a moment Primary key, foreign key, index Unique identifier of a row; reference to the primary key of another table; structure that speeds up search Transaction / ACID A logical unit of work that must be Atomic, Consistent, Isolated and Durable View, data dictionary (catalogue) A virtual table defined by a query; metadata about the database itself Why a Database Instead of Files? • The traditional file-based approach suffers from data redundancy and inconsistency, isolation of data in incompatible files, difficulty of sharing and of enforcing standards, weak security and integrity, and programs tied to the physical file format. • The database approach gives controlled redundancy, consistency, integrity, sharing and concurrent multi-user access, security, backup and recovery, enforcement of standards and data independence — at the price of cost, complexity and the need for skilled administration.

3

Components and Functions • Components: hardware (servers, storage, network); software (the DBMS with its query processor, storage manager and transaction manager, plus the operating system and application programs); data (the database and its metadata); people (database administrator, database designer, application developers, end users); and procedures (rules for use, backup and maintenance). • Functions of a DBMS: data definition (schemas, data types, constraints); data storage, retrieval and update; transaction management with concurrency control (locking) and recovery from failure; integrity enforcement; security and authorisation; a data dictionary; query optimisation; and utilities for backup, import/export and monitoring. • A spatial DBMS adds: geometry (and raster) data types, coordinate reference system handling (SRID), spatial indexes, spatial predicates and functions, topology and network support, and the ability to serve spatial data to GIS clients and web services. • ANSI/SPARC three-schema architecture: the external schemas (the views seen by different users), the conceptual (logical) schema (the whole logical structure) and the internal (physical) schema (how the data are actually stored).

4

The separation provides logical and physical data independence — the storage or even the logical structure can change without rewriting applications.

5

Database Access Languages • SQL is the standard language, divided into:

6

DDL — CREATE, ALTER, DROP (definition of schemas, tables, indexes, views);

7

DML — SELECT, INSERT, UPDATE, DELETE (manipulation of data);

8

DCL — GRANT, REVOKE (privileges); and TCL — COMMIT, ROLLBACK, SAVEPOINT (transaction control). • Other access methods:

9

QBE (query by example — form-based), report and form generators, programmatic interfaces (ODBC/JDBC, Python/psycopg2, ORMs), stored procedures and triggers, and, for spatial data, GIS clients (QGIS, ArcGIS) and OGC web services that generate SQL behind the scenes. • The spatial extension of SQL is standardised as OGC Simple Feature Access — SQL option and ISO SQL/MM Spatial, which define geometry types and the ST_ functions used in 7.5.

1

This section covers the principles and components of a spatial data infrastructure, standards, metadata, data accessibility and data interoperability.

2

Definition and Principles • A spatial data infrastructure (SDI) is the collection of technologies, policies, standards, institutional arrangements and human resources that make it possible to acquire, process, store, distribute, discover and use geospatial data efficiently.

3

Its purpose is to avoid duplicated collection, to allow data from many producers to be combined, and to support decision making at every level (local, national, regional, global). • Guiding principles (from INSPIRE and the GSDI Cookbook): data should be collected once and maintained where this can be done most effectively; it should be possible to combine data seamlessly from different sources and share them among many users; data should be available on conditions that do not restrict their extensive use; it should be easy to discover what data exist, to evaluate their fitness for purpose and to know the conditions of use; and the SDI must be built on partnership, standards and sustained funding, driven by user needs.

4

Components Component Content People and institutional framework The organisations, mandates, custodianship arrangements, coordination body, skills and training — the hardest component to build Data (framework/fundamental datasets) Geodetic control, administrative boundaries, cadastral parcels, elevation/terrain, transport, hydrography, imagery/orthophoto, land cover, place names and addresses — the base layers on which others are built Technology Networks, servers and storage, databases, GIS software, geoportal, web services and APIs Standards For data models, quality, metadata, services and exchange formats — the glue of interoperability Policy and access arrangements Data-sharing, pricing/licensing, open-data, privacy, security and copyright policies, and the legal mandate Standards and Metadata • Standards: the ISO/TC 211 19100 series — including ISO 19107 (spatial schema), ISO 19115 (metadata), 19119 (services), 19136 (GML) and 19157 (data quality); the OGC service and encoding standards (WMS, WMTS, WFS, WCS, CSW, SLD, GeoPackage, OGC API); plus national profiles that adapt them to local needs. • Metadata is data about data: identification (title, abstract, keywords, theme), spatial and temporal extent, coordinate reference system, lineage and data quality, resolution/scale, distribution format and access point, use constraints and licence, responsible party and dates.

5

Levels: discovery metadata (enough to find the dataset), exploration metadata (enough to judge fitness for purpose) and exploitation metadata (enough to use it correctly). • Metadata is written to a standard profile (ISO 19115/19139, or Dublin Core for simple cases), stored in a catalogue and searched through a CSW service.

6

Without metadata a dataset is effectively invisible and its quality unknowable — hence the rule that metadata must be created with the data, not afterwards.

7

Accessibility and Interoperability • Data accessibility depends on: a catalogue/geoportal for discovery; clear and simple licensing and pricing (increasingly open data — free, machine-readable, with an open licence); standard formats and services for delivery; bandwidth and infrastructure; protection of privacy, security and intellectual property where necessary; and the users' capacity to use the data. • Interoperability — the ability of systems and data to work together — has three levels: syntactic (common formats and protocols, e.g., GML over WFS); structural/schematic (common data models and schemas, e.g., the same feature catalogue and attribute structure); and semantic (the same meaning of terms and classifications — the hardest, addressed by feature catalogues, code lists and ontologies).

8

A common coordinate reference system (or reliable transformation), common feature identifiers and consistent classifications are practical prerequisites.

1

This section covers the components of a data model and the main types — hierarchical, network, relational, object-oriented and object-relational — together with entity-relationship modelling, UML and normalization.

2

Components and Levels • A data model is an abstract description of how data are structured and related.

3

It has three components: the structure (entities, attributes and relationships), the operations that may be performed, and the integrity rules/constraints that must be satisfied. • Design proceeds through three levels: the conceptual model (what the data are, independent of software — usually an ER or UML diagram), the logical model (tables, keys and types in the chosen model) and the physical model (storage, indexes, partitions in the chosen DBMS).

4

Model Structure, merits and limitations Hierarchical Data in a tree of parent-child (1:N) records, navigated by pointers (IMS).

5

Fast for predictable hierarchical queries, but rigid, with redundancy, no direct support for many-to-many relations, and difficult to change Network (CODASYL) A graph of records connected by 'sets', supporting many-to-many relations and faster navigation.

6

Flexible but complex to design and program, with the application tied to the physical links Relational Data in tables (relations) of rows and columns, related through primary and foreign keys rather than pointers; manipulated declaratively by SQL, based on set theory (Codd, 1970).

7

Simple, flexible, standard and dominant — but joins can be costly and complex objects are awkward Object-oriented (OODBMS) Data as objects with attributes and methods, supporting classes, inheritance, encapsulation and complex objects; suits CAD, multimedia and complex spatial objects, but is less standardised and less widely supported Object-relational (ORDBMS) A relational core extended with user-defined types, functions and inheritance — the basis of modern spatial databases (PostgreSQL/PostGIS, Oracle Spatial) NoSQL (key-value, document, column, graph) Schema-flexible stores for very large or loosely structured data; graph databases suit network analysis Entity-Relationship Modelling • The ER model describes the world as entities (things about which data are kept — Parcel, Owner, Road), their attributes (simple, composite, derived or multivalued; one of them the key) and the relationships between them (owns, crosses, belongs to). • Relationships have a cardinality — one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N) — and a participation (total or partial).

8

In an ER diagram (Chen notation) entities are rectangles, attributes ellipses and relationships diamonds; a weak entity depends on another for its identity. • Mapping to tables: each entity becomes a table with a primary key; a 1:N relationship is implemented by placing a foreign key in the table on the 'many' side; an M:N relationship requires a separate associative (junction) table holding the two foreign keys.

9

UML and Normalization • UML class diagrams are the modern standard for conceptual modelling and are used throughout the ISO 19100 series: classes (with attributes and operations) are linked by associations with multiplicities (1, 0..1, 1..*, 0..*), and by generalization/inheritance, aggregation and composition; stereotypes and packages structure large models.

10

Use-case, sequence and state diagrams describe behaviour. • Normalization removes redundancy and update anomalies:

11

1NF — all attribute values are atomic (no repeating groups);

12

2NF — 1NF and no partial dependency of a non-key attribute on part of a composite key;

13

3NF — 2NF and no transitive dependency (non-key attributes depend only on the key);

14

BCNF is a stricter form of 3NF.

15

Higher normal forms mean more tables and more joins, so a degree of denormalization is sometimes accepted for performance.

1

This section gives an overview of SQL and the relational DBMS, and covers constraints and keys, SQL syntax and data types.

2

Overview and the Relational DBMS • SQL (Structured Query Language, originally SEQUEL at IBM) is the declarative, standard language of relational databases — the user states what is wanted and the DBMS decides how to obtain it (query optimisation).

3

It is standardised by ISO/ANSI, with vendor extensions. • In an RDBMS data are held in relations (tables) in which: every row is unique, the order of rows and columns is immaterial, and every value is atomic.

4

Integrity rules: entity integrity — the primary key must be unique and not null; referential integrity — a foreign key must match an existing primary key or be null; and domain integrity — values must belong to the declared domain.

5

Data Types and Constraints Category Examples Numeric INTEGER, SMALLINT, BIGINT, NUMERIC/DECIMAL(p,s), REAL, DOUBLE PRECISION — use NUMERIC for exact values such as areas and money Character CHAR(n) (fixed length), VARCHAR(n) (variable), TEXT Date and time DATE, TIME, TIMESTAMP, INTERVAL Other BOOLEAN, BYTEA/BLOB (binary), UUID, JSON/JSONB, arrays, and in PostGIS the GEOMETRY / GEOGRAPHY / RASTER types Constraint / key Purpose PRIMARY KEY Uniquely identifies each row; implies UNIQUE and NOT NULL (entity integrity) FOREIGN KEY … REFERENCES Enforces referential integrity; options ON DELETE/UPDATE CASCADE, SET NULL or RESTRICT UNIQUE No two rows may hold the same value (but nulls are allowed) NOT NULL A value must always be present CHECK A condition every row must satisfy (e.g., CHECK (area > 0)) DEFAULT Value used when none is supplied Candidate / composite / surrogate key Any attribute set that could serve as the primary key; a key of several columns; an artificial identifier such as a serial number SQL Syntax • Definition:

6

CREATE TABLE parcel (id SERIAL PRIMARY KEY, kitta VARCHAR(20) NOT NULL, area NUMERIC(10,2) CHECK (area > 0), owner_id INTEGER REFERENCES owner(id)); — with ALTER TABLE … ADD/DROP COLUMN, DROP TABLE and CREATE INDEX. • Query:

7

SELECT column_list FROM table [JOIN …] [WHERE condition] [GROUP BY …] [HAVING …] [ORDER BY … ASC|DESC] [LIMIT n];

8

The logical order of evaluation is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, so WHERE filters individual rows before grouping while HAVING filters the groups afterwards. • Operators: comparison, AND/OR/NOT, BETWEEN, IN, LIKE ('%' and '_' wildcards), IS NULL (nulls need three-valued logic — they cannot be compared with =), DISTINCT. • Aggregate functions:

9

COUNT, SUM, AVG, MIN, MAX, normally used with GROUP BY. • Joins:

10

INNER JOIN (matching rows only), LEFT/RIGHT/FULL OUTER JOIN (keep unmatched rows of one or both sides), CROSS JOIN (Cartesian product) and self-joins; set operators UNION, INTERSECT, EXCEPT; subqueries in WHERE/FROM, with EXISTS and IN. • Update:

11

INSERT INTO … VALUES …, UPDATE … SET … WHERE …, DELETE FROM … WHERE … (a missing WHERE clause affects every row — a classic and costly mistake). • Other:

12

CREATE VIEW for virtual tables, CREATE INDEX for performance, GRANT/REVOKE for privileges, and COMMIT/ROLLBACK for transactions.

1

This section covers OODBMS and ORDBMS, spatial data types and models, operations on spatial data, spatial joining and indexing, spatial data mining, spatial query language, OGIS standards, the basics of PostgreSQL and PostGIS, and spatial storage and access.

2

OODBMS and ORDBMS • An object-oriented DBMS stores objects with their attributes and methods, supporting classes, inheritance, encapsulation and complex objects — attractive for spatial and CAD data, but with limited standardisation and market support. • An object-relational DBMS keeps the proven relational engine and SQL but allows user-defined data types and functions.

3

This is how spatial support is implemented today: the geometry is a column type with its own operators, indexes and functions.

4

PostgreSQL/PostGIS, Oracle Spatial, SQL Server Spatial, SpatiaLite (SQLite).

5

Spatial Data Types and Models • The OGC Simple Feature model defines a geometry hierarchy:

6

Geometry → Point, LineString, Polygon and the collections MultiPoint, MultiLineString, MultiPolygon, GeometryCollection (with curves and surfaces in the extended model).

7

Each geometry carries an SRID identifying its coordinate reference system, and is exchanged as WKT (e.g., POINT(85.32 27.71)) or the binary WKB. • Two complementary views of space: the object (feature/entity) view — discrete objects with geometry and attributes (parcels, roads); and the field view — continuous variation over space, stored as rasters or coverages (elevation, temperature).

8

A topological model additionally stores the shared nodes, edges and faces so that adjacency and connectivity are explicit and geometry is not duplicated.

9

Operations on Spatial Data Group Examples (PostGIS names) Properties and constructors ST_Area, ST_Length, ST_Perimeter, ST_Centroid, ST_Envelope, ST_Buffer, ST_ConvexHull, ST_Simplify, ST_Transform (change CRS), ST_IsValid Topological relationships (predicates) ST_Intersects, ST_Contains, ST_Within, ST_Touches, ST_Crosses, ST_Overlaps, ST_Disjoint, ST_Equals — formally defined by the DE-9IM (dimensionally extended nine-intersection model) Set (overlay) operations ST_Union, ST_Intersection, ST_Difference, ST_{SymDifference} — the database equivalents of GIS overlay Measurement and proximity ST_Distance, ST_DWithin (within a given distance — index-assisted), ST_ClosestPoint, nearest-neighbour ordering with the <-> operator Aggregation and output ST_Collect, ST_Union (aggregate), STₐₛₜₑₓₜ, ST_AsGeoJSON, ST_AsMVT for web tiles Spatial Join and Spatial Indexing • A spatial join combines two tables using a spatial relationship instead of a common key — for example, assigning each well to the ward polygon that contains it:

10

SELECT w.id, p.ward FROM well w JOIN ward p ON ST_Within(w.geom, p.geom);

11

It is computationally expensive and depends entirely on indexing. • Spatial indexing: ordinary B-tree indexes cannot order two-dimensional data, so spatial DBMSs use R-trees (and R*-trees) — hierarchies of minimum bounding rectangles (MBR) — or quadtrees, grid indexes, k-d trees and space-filling curves (Hilbert, Morton/geohash).

12

PostGIS implements the R-tree over the generalised GiST index. • Queries are answered by a filter-and-refine strategy: the index quickly returns candidates whose bounding boxes satisfy the condition (the cheap filter step), and the exact geometric test is applied only to those candidates (the expensive refine step).

13

Without a spatial index, a spatial join degenerates into comparing every pair of geometries.

14

Spatial Data Mining and Query Language • Spatial data mining extracts previously unknown and useful patterns from spatial data, taking account of spatial dependence — Tobler's first law of geography: 'everything is related to everything else, but near things are more related than distant things'.

15

Techniques: spatial clustering (K-means, DBSCAN for arbitrary shapes and noise), spatial autocorrelation (Moran's I), hot-spot analysis (Getis-Ord Gi*), spatial association rules and co-location patterns, classification and prediction, outlier detection, and trajectory/movement mining.

16

Applications: crime and disease clusters, landslide and flood susceptibility, market analysis, transport patterns. • The spatial query language is SQL extended with the spatial types and functions above, standardised by OGC Simple Feature Access — SQL (the 'OGIS' standards) and ISO SQL/MM Spatial, which prescribe the ST_ prefix and the behaviour of each function, so that queries are portable between compliant systems.

17

PostgreSQL/PostGIS and Spatial Storage • PostgreSQL is a free, open-source, standards-compliant object-relational DBMS;

18

PostGIS is its spatial extension, enabled with CREATE EXTENSION postgis;.

19

It provides the geometry type (planar, fast) and geography type (on the ellipsoid, for global data), the spatial_refsys table of coordinate systems, raster support, topology and pgRouting for network analysis. • Typical workflow: create the database and extension → load data with shp2pgsql, ogr2ogr or QGIS → create a GiST index (CREATE INDEX idx ON parcel USING GIST (geom);) → run spatial SQL → publish through GeoServer/QGIS Server as OGC services (Chapter 6.5 and 8.6). • Spatial storage and access: geometries are stored in compact binary form (large objects are compressed and moved out of line), tables may be clustered on the spatial index or partitioned by area or time, rasters are stored as tiles with overviews/pyramids, and caching or materialised views speed up repeated queries.

20

Access is through SQL (psql, ODBC/JDBC, Python), GIS desktop clients and web services, with the usual database facilities for multi-user editing, transactions, versioning, privileges and backup.

1

This section covers the organisational structure, policy, metadata, clearinghouse, client-server architecture and applications of a national spatial data infrastructure.

2

Concept and Organisational Structure • An NSDI is the nationwide implementation of an SDI — the framework of institutions, policies, standards, data and technology through which a country's geospatial data are produced once, shared and used by everybody.

3

It sits within a hierarchy: local → provincial/state → national → regional → global (GSDI). • Organisational structure: a national coordinating body or steering committee with representatives of the main data-producing and data-using ministries (and of academia and the private sector), supported by a lead/executive agency — normally the national mapping organisation, in Nepal the Survey Department; technical working groups for standards, metadata, framework data and the geoportal; and a named custodian for each framework dataset, responsible for collecting, maintaining, documenting and publishing it to the agreed standards.

4

A secretariat maintains the portal and the registry. • (Nepal has been developing its national geographic information infrastructure through the Survey Department, with a national geoportal and standards work — verify the current programme name and status.) Policy • The policy layer answers the questions that technology cannot: who is the custodian of each dataset and what must they maintain; data-sharing policy between agencies (ideally 'share by default'); pricing and licensing (increasingly open data with an open licence, with cost recovery only for special products); privacy, confidentiality and national security restrictions; copyright and liability; quality and standards compliance; funding and sustainability; and the legal mandate that makes the arrangements binding.

5

Weak policy — not weak technology — is the usual reason an NSDI fails.

6

Metadata and the Clearinghouse • Every dataset and service must be described by metadata to the national profile of ISO 19115/19139 (identification, extent, CRS, quality and lineage, distribution, constraints, contact), created by the custodian at the time the data are made and kept current. • The clearinghouse is the distributed network of catalogue servers through which users discover, evaluate and obtain data — today usually presented as a single geoportal with a CSW catalogue behind it, harvesting metadata from the participating agencies.

7

Its functions are search (by keyword, theme, place, time), display of metadata, preview of the data, and links to download or to live services; it may also register the standards, code lists and reference systems used.

8

Architecture and Applications • Client-server architecture: at the back end each custodian holds its data in a spatial database and publishes them through a map/feature server (GeoServer, MapServer, ArcGIS Server) as OGC services — WMS/WMTS for map images and tiles, WFS for features, WCS for coverages and CSW for metadata; at the front end the geoportal or any GIS client, web or mobile application consumes those services.

9

The data therefore stay with, and are maintained by, their custodian while appearing to the user as one seamless resource. • Applications: land administration and cadastre; urban and regional planning; infrastructure design and asset management; disaster risk reduction and emergency response (earthquake, landslide and flood mapping — of particular importance in Nepal); agriculture, forestry and watershed management; environment and climate; public health; census and statistics; utilities; navigation and logistics; and the monitoring of the Sustainable Development Goals. • Benefits: no duplicated collection, faster and better-informed decisions, consistency between agencies, a market for value-added services and transparency.

10

Challenges: sustained funding, institutional coordination and willingness to share, capacity and skills, data quality and currency of legacy datasets, and keeping the portal and standards alive after the project that created them ends.