data / sql

Extension géospatiale PostGIS

Leçon 211 exercice

Explication

Ce que vous allez apprendre

Activer et utiliser l'extension PostGIS dans PostgreSQL
Stocker une position géographique avec le type GEOGRAPHY(POINT, 4326)
Choisir entre GEOMETRY (plan) et GEOGRAPHY (sphère) selon le besoin
Calculer une distance réelle en mètres et rechercher les lieux dans un rayon donné
Indexer une colonne géographique avec un index GiST pour rendre la recherche de proximité utilisable à l'échelle

Dans quel contexte ?

Une application de livraison doit répondre en temps réel à la question "quels restaurants sont à moins de 2 km de cet utilisateur ?" à partir d'une table lieux contenant des dizaines de milliers de points. Calculer une distance à la main pour chaque ligne serait à la fois complexe (la Terre est une sphère, pas un plan) et lent sans index adapté : PostGIS, présenté dans cette leçon, résout les deux problèmes.

D'abord, le problème que deux simples nombres ne résolvent pas

Un lieu, ce sont une longitude et une latitude : on pourrait se dire que deux colonnes FLOAT suffisent. Mais essaie de calculer la vraie distance en mètres entre deux points sur une planète ronde avec juste ces deux nombres — les formules deviennent vite complexes, et personne ne veut les réécrire à chaque projet.

La solution : une extension spécialisée

PostGIS ajoute à PostgreSQL des types et des fonctions géométriques toutes faites. Au lieu de recalculer soi-même la trigonométrie sphérique, on appelle ST_Distance, ST_DWithin ou ST_Covers, et PostGIS s'occupe du calcul exact.

Un premier choix à faire : GEOMETRY ou GEOGRAPHY ?

PostGIS propose deux familles de types. GEOMETRY travaille sur un plan cartésien classique : rapide, mais suppose une carte plate, adaptée à une petite zone avec une projection choisie. GEOGRAPHY calcule directement sur une sphère (la Terre), donnant des distances réalistes en mètres sans se soucier de projection — un calcul un peu plus coûteux, mais bien plus simple pour une application grand public comme "trouve un restaurant proche".

Type	Modèle	Vitesse	Cas d'usage
`GEOMETRY`	Plan cartésien, projection fixe	Rapide	SIG, petite zone avec projection connue
`GEOGRAPHY`	Sphère terrestre	Un peu plus coûteux	Application grand public, distances GPS réelles

Piège fréquent

ST_MakePoint(latitude, longitude) au lieu de ST_MakePoint(longitude, latitude) place silencieusement chaque point à des milliers de kilomètres de sa position réelle, sans provoquer d'erreur - PostGIS attend toujours l'ordre (longitude, latitude).

Un problème classique resurgit : la lenteur sans index

Sans un index adapté, chercher tous les lieux à moins de 2 km d'un point obligerait PostgreSQL à calculer la distance entre ce point et CHAQUE ligne de la table, une par une. Sur des milliers de lieux, ce n'est plus praticable.

La solution : l'index GiST

Un index GiST sur la colonne géographique résout ce problème comme n'importe quel index résout un scan complet : il permet de ne consulter que les lieux réellement proches, sans balayer toute la table. Ici, ce n'est pas une option facultative : sans lui, la fonctionnalité de proximité est tout simplement inutilisable à l'échelle réelle.

Bonne pratique

Crée systématiquement un index GiST (CREATE INDEX ... USING GIST (position)) dès la création d'une colonne GEOGRAPHY/GEOMETRY destinée à des recherches de proximité, avant même d'avoir un problème de performance visible.

Pièges à connaître

L'ordre des coordonnées piège souvent les débutants : ST_MakePoint attend (longitude, latitude), pas l'inverse. Et confondre GEOMETRY et GEOGRAPHY sur un projet donne des distances fausses sans erreur visible.

Vers la suite

PostGIS montre comment une extension transforme PostgreSQL en moteur spécialisé, en s'appuyant sur les mêmes bases vues depuis le début du cours (types, index, filtres). Les prochaines leçons appliquent la même logique à la recherche textuelle avancée et au JSONB.

Commandes & code

Extension géospatiale PostGIS

PostGIS ajoute des types et fonctions géométriques à PostgreSQL pour stocker et interroger des données spatiales.

-- Activer l'extension (une fois par base)
CREATE EXTENSION IF NOT EXISTS postgis;

-- Colonne géographique : POINT en WGS84 (SRID 4326, coordonnées GPS lat/lon)
CREATE TABLE lieux (
    id       SERIAL PRIMARY KEY,
    nom      VARCHAR(200),
    position GEOGRAPHY(POINT, 4326)  -- GEOGRAPHY calcule des distances réelles en mètres sur une sphère
);

-- Insérer un point à partir de longitude/latitude (ordre : lon puis lat !)
INSERT INTO lieux (nom, position) VALUES
    ('Tour Eiffel', ST_SetSRID(ST_MakePoint(2.2945, 48.8584), 4326)::geography),
    ('Notre-Dame',  ST_SetSRID(ST_MakePoint(2.3499, 48.8530), 4326)::geography);

-- Distance entre deux points, en mètres
SELECT ST_Distance(
    (SELECT position FROM lieux WHERE nom = 'Tour Eiffel'),
    (SELECT position FROM lieux WHERE nom = 'Notre-Dame')
) AS distance_metres;

-- Recherche de proximité : tous les lieux à moins de 2 km d'un point donné
SELECT nom, ST_Distance(position, ST_SetSRID(ST_MakePoint(2.30, 48.86), 4326)::geography) AS distance
FROM lieux
WHERE ST_DWithin(position, ST_SetSRID(ST_MakePoint(2.30, 48.86), 4326)::geography, 2000)
ORDER BY distance;

-- Index spatial GiST : indispensable, sans lui ST_DWithin/ST_Distance scannent toute la table
CREATE INDEX idx_lieux_position ON lieux USING GIST (position);

-- Polygones : zones de livraison, quartiers, etc.
CREATE TABLE zones_livraison (
    id      SERIAL PRIMARY KEY,
    nom     VARCHAR(100),
    contour GEOGRAPHY(POLYGON, 4326)
);

-- Un point est-il dans une zone ? (ex: éligibilité livraison)
SELECT z.nom
FROM zones_livraison z
WHERE ST_Covers(z.contour, ST_SetSRID(ST_MakePoint(2.30, 48.86), 4326)::geography);

-- Plus proches voisins (KNN) : l'opérateur <-> exploite directement l'index GiST
SELECT nom, position <-> ST_SetSRID(ST_MakePoint(2.30, 48.86), 4326)::geography AS distance
FROM lieux
ORDER BY position <-> ST_SetSRID(ST_MakePoint(2.30, 48.86), 4326)::geography
LIMIT 5;

-- Aire d'un polygone en m²
SELECT nom, ST_Area(contour) AS surface_m2 FROM zones_livraison;

-- Export au format GeoJSON, interopérable avec les cartes web (Leaflet, Mapbox)
SELECT nom, ST_AsGeoJSON(position) FROM lieux;

Résumé

GEOGRAPHY calcule des distances réelles en mètres sur une sphère ; GEOMETRY travaille en coordonnées planes (plus rapide, nécessite une projection adaptée).
Un index GIST sur la colonne géographique est obligatoire pour toute requête de proximité à l'échelle.
ST_DWithin (rayon), ST_Covers/ST_Contains (appartenance à un polygone) et l'opérateur <-> (K plus proches voisins) couvrent l'essentiel des cas d'usage.
ST_AsGeoJSON facilite l'intégration avec des cartes front-end.

Exercices pratiques

1 disponible

Mission : corriger une carte de livraison qui place les restaurants dans l'océan

Objectif : Diagnostiquer une inversion de coordonnées et une absence d'index spatial dans une fonctionnalité de recherche de proximité.

Contexte

Une application de livraison affiche les restaurants proches d'un utilisateur parisien (latitude 48.8566, longitude 2.3522), mais la carte les place en plein océan Atlantique, au large de l'Afrique. La table lieux contient déjà des dizaines de milliers de points, et la requête de proximité met plusieurs secondes à répondre même une fois la position corrigée.

Résoudre l’exercice →