Spatial Databases - Geomatician

01 — Conceptual Data Schema (CDM)

The UML conceptual schema represents geographic entities and their relationships. Each entity has a geometry: Geometry attribute that stores spatial data (point, line, polygon) in PostGIS.

UML conceptual schema of spatial database

Fig. 1 — UML Conceptual Schema: River, Commune, Parcel, Building entities with inheritance and spatial associations

🔗 Key Relationships

River is_crossed_by Commune

Commune consistsOf Parcel (1..*)

Parcel ← ConstructibleParcel

Parcel ← AgriculturalParcel

Building isLocatedOn ConstructibleParcel

📐 Geometry Types

GEOM River MultiLineString

GEOM Commune Polygon

GEOM Parcel Polygon

GEOM Building Polygon

02 — Logical Data Model (LDM)

The relational model translates the conceptual schema into SQL tables. Primary keys (PK) uniquely identify each record, foreign keys (FK) ensure referential integrity between tables.

Fig. 2 — Logical data model: tables, primary keys, foreign keys and geometries

PK Primary Key

FK Foreign Key

GEO Spatial Geometry

Departement

PK NomD string

GEO the_geom Polygon

FK id_region → Region

Commune

PK NUMINSEE int

nomCom string

GEO the_geom Polygon

FK NomD → Departement

Parcel

PK numCad int

typeculture string

COS float

GEO the_geom Polygon

FK NUMINSEE → Commune

River

PK NumR int

Nom string

GEO the_geom MultiLineString

Is_crossed_by

PK FK NUMINSEE → Commune

PK FK NumR → River

03 — Spatial SQL Queries

Examples of SQL queries using PostGIS spatial functions to query and analyze geographic data.

Communes crossed by a specific river (spatial join with ST_Intersects)

SELECT DISTINCT(c.nomCom)
FROM Commune c, River r
WHERE ST_INTERSECTS(c.the_geom, r.the_geom)
  AND r.Nom = 'Le Lez';

Communes of a given department (attribute join)

SELECT c.NomCom
FROM Departement d, Commune c
WHERE d.NomD = 'Languedoc-R';

Vineyard parcels of a commune (attribute filter)

SELECT numCad
FROM Parcel
WHERE NomCom = 'PradesLeLez'
  AND typeculture = 'viticole';

Herault communes neighboring Saint-Mathieu (ST_Touches = share a boundary)

SELECT c2.NUMINSEE
FROM Commune c1, Commune c2, Departement d
WHERE d.nomD = 'Herault'
  AND c1.NomCom = 'SaintMathieu'
  AND ST_TOUCHES(c1.the_geom, c2.the_geom); -- share a boundary