0Pricing
SQL Academy · Leçon

Jointures spatiales et inclusion

Quels points se trouvent dans quelles zones.

Jointures spatiales et inclusion est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce qu'une jointure spatiale ?

Une jointure spatiale combine deux tables en fonction d'une relation géographique plutôt que d'une clé correspondante. Au lieu de demander « cet ID est-il égal à cet ID ? », vous posez des questions comme « ce point se trouve-t-il dans ce polygone ? » ou « ces deux formes se chevauchent-elles ? »

PostGIS étend PostgreSQL avec des types de géométrie et des fonctions spatiales qui rendent ces jointures possibles. Le résultat est identique à celui d'une jointure classique — les lignes des deux tables sont combinées — mais la condition est géométrique.

Configuration des tables d’exemple

Créons deux tables avec lesquelles travailler : cities, qui contient les emplacements ponctuels, et countries, qui contient les limites polygonales. Toutes deux utilisent le type GEOMETRY de PostGIS avec le SRID 4326, le système standard WGS84 de latitude/longitude.

Le SRID (identifiant de référence spatiale) indique à PostGIS quel système de coordonnées utiliser. Le SRID 4326 est le plus courant pour les données GPS.

CREATE TABLE countries (
  id     SERIAL PRIMARY KEY,
  name   TEXT NOT NULL,
  border GEOMETRY(POLYGON, 4326)
);

CREATE TABLE cities (
  id       SERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  location GEOMETRY(POINT, 4326)
);

Insertion de données d’exemple

Vous allez utiliser ST_GeomFromText pour insérer des valeurs géométriques au format de texte bien connu (WKT). WKT est une représentation textuelle standard des géométries : POINT(lon lat) pour les points et POLYGON((...)) pour les polygones.

Notez que la longitude précède la latitude dans WKT : cela correspond à la convention X, Y utilisée en mathématiques.

INSERT INTO countries (name, border) VALUES
  ('France', ST_GeomFromText('POLYGON((-5 42, 8 42, 8 51, -5 51, -5 42))', 4326)),
  ('Spain',  ST_GeomFromText('POLYGON((-9 36, 3 36, 3 44, -9 44, -9 36))', 4326));

INSERT INTO cities (name, location) VALUES
  ('Paris',    ST_GeomFromText('POINT(2.35 48.85)',   4326)),
  ('Madrid',   ST_GeomFromText('POINT(-3.70 40.42)',  4326)),
  ('Bordeaux', ST_GeomFromText('POINT(-0.58 44.84)',  4326)),
  ('Lisbon',   ST_GeomFromText('POINT(-9.14 38.72)',  4326));

ST_Contains : la fonction fondamentale

ST_Contains(geometry A, geometry B) renvoie TRUE lorsque la géométrie A contient entièrement la géométrie B. Dans notre cas, ST_Contains(country.border, city.location) renvoie vrai lorsqu’un point représentant une ville se trouve à l’intérieur d’un polygone représentant un pays.

C’est le principe central des jointures par inclusion dans PostGIS. Cette fonction fait partie de la norme OGC et fonctionne avec toute combinaison de types de géométries.

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Within : la perspective inverse

ST_Within(geometry A, geometry B) est l’exact opposé de ST_Contains : la fonction renvoie TRUE lorsque la géométrie A se trouve entièrement à l’intérieur de la géométrie B. ST_Within(city, country) est logiquement identique à ST_Contains(country, city).

Dans ce cas, les deux fonctions produisent le même résultat. Le choix entre elles dépend de la lisibilité : choisissez celle dont la formulation convient le mieux à votre requête.

-- These two queries return identical results:

-- Using ST_Contains (country contains city)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Contains(co.border, ci.location);

-- Using ST_Within (city is within country)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Within(ci.location, co.border);

LEFT JOIN pour trouver les points sans correspondance

Une jointure JOIN classique exclut les villes qui ne se trouvent à l’intérieur d’aucun polygone représentant un pays. Utilisez un LEFT JOIN associé à une vérification WHERE ... IS NULL pour trouver les points qui ne possèdent aucun polygone englobant ; cette technique est utile pour détecter les problèmes de qualité des données ou les points situés en dehors de votre zone couverte.

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
LEFT JOIN countries co
  ON   ST_Contains(co.border, ci.location)
ORDER BY co.name NULLS LAST;

Comptage des points par polygone

Les jointures spatiales sont particulièrement utiles lorsqu’elles sont combinées à une agrégation. En associant les villes aux pays, puis en regroupant les résultats par pays, vous pouvez compter le nombre de points situés à l’intérieur de chaque polygone. Ce schéma est très courant dans l’analyse géographique : compter les magasins par région, les événements par district, les capteurs par zone, et ainsi de suite.

SELECT
  co.name          AS country,
  COUNT(ci.id)     AS city_count
FROM   countries co
LEFT JOIN cities  ci
  ON   ST_Contains(co.border, ci.location)
GROUP BY co.name
ORDER BY city_count DESC;

Utilisation d’un index spatial pour améliorer les performances

Les jointures spatiales peuvent être lentes sur de grands ensembles de données, car chaque point est comparé à chaque polygone. Un index GIST (arbre de recherche généralisé) permet à PostGIS d’utiliser un préfiltre par boîte englobante afin d’éviter la plupart des comparaisons avant d’exécuter la vérification précise ST_Contains.

Créez toujours un index GIST sur les colonnes géométriques utilisées dans les jointures ou les filtres. Le planificateur de requêtes l’utilisera automatiquement.

-- Create GIST indexes on both geometry columns
CREATE INDEX idx_countries_border
  ON countries USING GIST (border);

CREATE INDEX idx_cities_location
  ON cities USING GIST (location);

-- The same join now benefits from index acceleration
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Intersects : géométries qui se chevauchent

ST_Intersects(A, B) renvoie TRUE si deux géométries ont au moins un point en commun, y compris si elles se touchent uniquement sur une limite. Cette fonction est plus permissive que ST_Contains : deux polygones qui se chevauchent s’intersectent même si aucun ne contient entièrement l’autre.

Pour les vérifications point-dans-polygone, ST_Intersects et ST_Contains sont équivalentes, mais ST_Intersects utilise l’index GIST plus efficacement et est souvent privilégiée en pratique.

-- ST_Intersects is index-friendly and equivalent
-- to ST_Contains for point-in-polygon tests
SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Intersects(co.border, ci.location);

Associer des points au polygone le plus proche

Lorsqu’un point se trouve exactement sur une limite ou très près de plusieurs polygones, vous pouvez vouloir obtenir le polygone le plus proche plutôt que toutes les correspondances. ST_Distance mesure la distance entre deux géométries, ce qui permet d’utiliser des schémas avec ORDER BY + LIMIT ou la jointure spécialisée LATERAL avec ORDER BY ... LIMIT 1.

On parle alors de jointure par recherche du plus proche voisin. Cette technique est courante pour rapprocher des traces GPS de segments routiers ou affecter un point à la région la plus proche.

-- For each city, find the single closest country centroid
SELECT DISTINCT ON (ci.name)
  ci.name                              AS city,
  co.name                              AS nearest_country,
  ST_Distance(ci.location, ST_Centroid(co.border)) AS dist
FROM   cities   ci
CROSS JOIN countries co
ORDER BY ci.name, dist;

Exemple pratique : restaurants dans les districts

Voici un exemple réaliste de bout en bout : déterminer à quel district chaque restaurant appartient, puis compter les restaurants par district. Ce schéma s’applique à tous les cas point-dans-polygone : distributeurs automatiques dans les quartiers, accidents dans les circonscriptions, commandes dans les zones de livraison.

La requête utilise ST_Within dans une jointure spatiale et regroupe les résultats pour produire un rapport récapitulatif.

-- Assume tables: districts(id, name, boundary GEOMETRY)
--                restaurants(id, name, location GEOMETRY)

SELECT
  d.name                 AS district,
  COUNT(r.id)            AS restaurant_count,
  STRING_AGG(r.name, ', ' ORDER BY r.name) AS names
FROM   districts    d
LEFT JOIN restaurants r
  ON   ST_Within(r.location, d.boundary)
GROUP BY d.name
ORDER BY restaurant_count DESC;

Vérification rapide

Vérifiez votre compréhension des jointures spatiales et de l’inclusion dans PostGIS.

Récapitulatif : jointures spatiales et inclusion

Dans cette leçon, vous avez appris à répondre à la question « quels points se trouvent dans quelles zones ? » à l’aide des jointures spatiales de PostGIS.

Points essentiels à retenir :

  • ST_Contains(polygone, point) — renvoie vrai lorsque le polygone contient entièrement le point.
  • ST_Within(point, polygone) — l’opposé ; cette fonction est logiquement équivalente à ST_Contains.
  • ST_Intersects — vérification de chevauchement plus générale, optimisée pour l’utilisation d’un index dans les recherches point-dans-polygone.
  • Les index GIST sur les colonnes géométriques sont essentiels pour conserver de bonnes performances à grande échelle.
  • Combinez les jointures spatiales avec GROUP BY pour compter ou agréger les points par région.
  • Utilisez LEFT JOIN pour détecter les points situés en dehors de tous les polygones.

Les jointures spatiales permettent d’effectuer directement des analyses géographiques en SQL, sans outil GIS externe.

Questions Fréquemment Posées

La leçon « Jointures spatiales et inclusion » est-elle gratuite ?

Oui — le texte complet de « Jointures spatiales et inclusion » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Jointures spatiales et inclusion » ?

Quels points se trouvent dans quelles zones. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « Jointures spatiales et inclusion » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Types de données spatiales
  2. Distance et plus proches voisins
  3. Jointures spatiales et inclusion
  4. Index spatiaux (GiST)
← Retour à SQL Academy