PostGIS (Spatial Postgres Extension)

PostGIS est l'extension PostgreSQL ajoutant le support des données géospatiales (Geographic Information Systems — GIS) — types geometry/geography, fonctions spatiales, indexing R-Tree (via GiST), et conformance avec standards OGC. Le SGBD spatial open source le plus mature et complet, utilisé par OpenStreetMap, gouvernements, telecoms, retail.

Types principaux :
(1) **GEOMETRY** — planar geometries (Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection). Calculs Euclidean.
(2) **GEOGRAPHY** — geometries sur sphère terrestre. Calculs basés sur grand cercle, plus précis pour distances mondiales mais plus lourd.

Activation : `CREATE EXTENSION postgis;`

Fonctions courantes (1000+ disponibles) :
- **Création** : ST_MakePoint(lon, lat), ST_GeomFromText('POINT(2.349 48.864)', 4326), ST_GeomFromGeoJSON(...).
- **Distance** : ST_Distance(a, b), ST_DWithin(a, b, 1000) — efficient "within distance" check using index.
- **Topology** : ST_Contains, ST_Intersects, ST_Overlaps, ST_Within, ST_Touches, ST_Crosses.
- **Transformations** : ST_Transform(geom, srid) — change projection (4326 WGS84 lat/lon vers 3857 Web Mercator).
- **Buffering** : ST_Buffer(geom, distance).
- **Aggregation** : ST_Union, ST_Collect, ST_Centroid.
- **Format conversion** : ST_AsText, ST_AsGeoJSON, ST_AsKML.
- **Validation** : ST_IsValid, ST_MakeValid.

Indexing spatial : `CREATE INDEX idx_geom ON locations USING GIST (geom);` — R-tree-like structure, accelerates spatial queries dramatically.

Use cases :
(1) **Find restaurants within 500m of user** — `WHERE ST_DWithin(location, ST_MakePoint(2.349, 48.864)::geography, 500)`.
(2) **Polygon containment** — does this point fall in this delivery zone ? Voting precinct ? Country ?
(3) **Routing** — shortest path (with pgRouting extension).
(4) **Heatmaps** — aggregate points to grid.
(5) **Geofencing** — alert when device enters/exits zone.
(6) **Map rendering** — backend for tile servers (MapServer, GeoServer, pg_tileserv).

Intégrations : QGIS (desktop GIS), Mapbox, Leaflet, OpenLayers, ArcGIS, Tableau spatial. Standard de facto pour GIS open source. Compétences DP-300, DEA-C01.