0Pricing
SQL Academy · Lektion

Räumliche Joins und Enthaltensein

Welche Punkte in welchen Gebieten liegen

Räumliche Joins und Enthaltensein ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was ist ein räumlicher Join?

Ein räumlicher Join kombiniert zwei Tabellen anhand einer geografischen Beziehung statt anhand eines übereinstimmenden Schlüssels. Statt zu fragen, ob diese ID jener ID entspricht, stellen Sie Fragen wie: Fällt dieser Punkt in jenes Polygon oder überschneiden sich diese beiden Formen?

PostGIS erweitert PostgreSQL um Geometrietypen und räumliche Funktionen, die solche Joins ermöglichen. Das Ergebnis entspricht dem eines regulären SQL-JOINs – Zeilen aus beiden Tabellen werden kombiniert –, aber die Bedingung ist geometrischer Natur.

Beispieltabellen einrichten

Wir erstellen zwei Tabellen, mit denen wir arbeiten: cities enthält Punktpositionen und countries Polygongrenzen. Beide verwenden den Typ GEOMETRY von PostGIS mit SRID 4326 (der standardmäßigen WGS84-Kodierung für Breiten- und Längengrade).

Der SRID (Spatial Reference ID) gibt an, welches Koordinatensystem PostGIS verwenden soll. SRID 4326 ist das gebräuchlichste für GPS-Daten.

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)
);

Beispieldaten einfügen

Wir verwenden ST_GeomFromText, um Geometriewerte im Well-Known-Text-Format (WKT) einzufügen. WKT ist eine standardisierte Textdarstellung für Geometrien: POINT(lon lat) für Punkte und POLYGON((...)) für Polygone.

Beachten Sie, dass im WKT der Längengrad vor dem Breitengrad steht – dies entspricht der X-Y-Konvention in der Mathematik.

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: Die Kernfunktion

ST_Contains(geometry A, geometry B) gibt TRUE zurück, wenn Geometrie A Geometrie B vollständig enthält. In unserem Anwendungsfall gibt ST_Contains(country.border, city.location) true zurück, wenn ein Stadtpunkt innerhalb eines Länderpolygons liegt.

Das ist der Kern von Containment-Joins in PostGIS. Die Funktion ist Teil des OGC-Standards und funktioniert mit jeder Kombination von Geometrietypen.

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

ST_Within: Die umgekehrte Perspektive

ST_Within(geometry A, geometry B) ist das genaue Gegenteil von ST_Contains: Die Funktion gibt TRUE zurück, wenn Geometrie A vollständig innerhalb von Geometrie B liegt. ST_Within(city, country) ist logisch identisch mit ST_Contains(country, city).

Beide Funktionen liefern hier dasselbe Ergebnis. Die Wahl zwischen ihnen ist eine Frage der Lesbarkeit – verwenden Sie die Variante, die sich in Ihrer Abfrage natürlicher liest.

-- 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);

Nicht zugeordnete Punkte mit LEFT JOIN finden

Ein regulärer JOIN schließt Städte aus, die in keinem Länderpolygon liegen. Verwenden Sie einen LEFT JOIN in Kombination mit einer WHERE ... IS NULL-Prüfung, um Punkte ohne enthaltendes Polygon zu finden – beispielsweise zum Erkennen von Datenqualitätsproblemen oder von Punkten außerhalb Ihres Abdeckungsbereichs.

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;

Punkte pro Polygon zählen

Räumliche Joins spielen ihre Stärke besonders in Kombination mit Aggregationen aus. Indem Sie Städte mit Ländern verknüpfen und anschließend nach Land gruppieren, können Sie zählen, wie viele Punkte innerhalb jedes Polygons liegen. Dieses Muster ist in der geografischen Analyse äußerst verbreitet – etwa zum Zählen von Geschäften pro Region, Ereignissen pro Bezirk, Sensoren pro Zone und vielem mehr.

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;

Für bessere Leistung einen räumlichen Index verwenden

Räumliche Joins können bei großen Datenmengen langsam sein, da jeder Punkt mit jedem Polygon verglichen wird. Ein GIST-Index (Generalized Search Tree) ermöglicht PostGIS eine Vorfilterung anhand von Begrenzungsrahmen und überspringt die meisten Vergleiche, bevor die präzise ST_Contains-Prüfung ausgeführt wird.

Erstellen Sie für Geometriespalten, nach denen Sie Joins oder Filter ausführen, immer einen GIST-Index. Der Abfrageplaner verwendet ihn automatisch.

-- 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: Überlappende Geometrien

ST_Intersects(A, B) gibt TRUE zurück, wenn zwei Geometrien mindestens einen gemeinsamen Punkt haben – auch dann, wenn sie sich nur an einer Grenze berühren. Die Funktion ist weniger streng als ST_Contains: Zwei überlappende Polygone schneiden sich, selbst wenn keines das andere vollständig enthält.

Für Punkt-in-Polygon-Prüfungen sind ST_Intersects und ST_Contains gleichwertig. ST_Intersects nutzt den GIST-Index jedoch effizienter und wird in der Praxis häufig bevorzugt.

-- 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);

Punkte dem nächstgelegenen Polygon zuordnen

Wenn ein Punkt genau auf einer Grenze oder sehr nahe an mehreren Polygonen liegt, möchten Sie möglicherweise statt aller Treffer das nächstgelegene Polygon auswählen. ST_Distance misst den Abstand zwischen zwei Geometrien und ermöglicht Muster mit ORDER BY + LIMIT oder den spezialisierten LATERAL-Join mit ORDER BY ... LIMIT 1.

Dies wird als Nearest-Neighbor-Join bezeichnet und ist beispielsweise beim Zuordnen von GPS-Verläufen zu Straßenabschnitten oder eines Punkts zur nächstgelegenen Region üblich.

-- 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;

Praxisbeispiel: Restaurants in Bezirken

Hier ist ein realistisches Beispiel von Anfang bis Ende: Ermitteln Sie, zu welchem Stadtbezirk jedes Restaurant gehört, und zählen Sie anschließend die Restaurants pro Bezirk. Dieses Muster lässt sich auf jedes Punkt-in-Polygon-Szenario anwenden – etwa auf Geldautomaten in Stadtvierteln, Unfälle in Polizeirevieren oder Bestellungen in Lieferzonen.

Die Abfrage verwendet ST_Within innerhalb eines räumlichen Joins und gruppiert die Ergebnisse für einen zusammenfassenden Bericht.

-- 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;

Kurzer Wissenstest

Testen Sie Ihr Verständnis von räumlichen Joins und dem Enthaltensein in PostGIS.

Zusammenfassung: Räumliche Joins und Enthaltensein

In dieser Lektion haben Sie gelernt, wie Sie mit räumlichen Joins in PostGIS die Frage „Welche Punkte liegen in welchen Gebieten?“ beantworten.

Die wichtigsten Erkenntnisse:

  • ST_Contains(polygon, point) – gibt true zurück, wenn das Polygon den Punkt vollständig enthält.
  • ST_Within(point, polygon) – die umgekehrte Funktion, logisch gleichbedeutend mit ST_Contains.
  • ST_Intersects – eine allgemeinere Prüfung auf Überlappung, die sich für Punkt-in-Polygon-Abfragen gut für Indizes eignet.
  • GIST-Indizes auf Geometriespalten sind für eine gute Leistung bei großen Datenmengen unerlässlich.
  • Kombinieren Sie räumliche Joins mit GROUP BY, um Punkte pro Region zu zählen oder zu aggregieren.
  • Verwenden Sie LEFT JOIN, um Punkte zu erkennen, die außerhalb aller Polygone liegen.

Räumliche Joins ermöglichen geografische Analysen direkt in SQL – ganz ohne externes GIS-Tool.

Häufig gestellte Fragen

Ist die Lektion „Räumliche Joins und Enthaltensein“ kostenlos?

Ja — der vollständige Text von „Räumliche Joins und Enthaltensein“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Räumliche Joins und Enthaltensein“?

Welche Punkte in welchen Gebieten liegen Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Academy zu starten?

Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.

Wie lange dauert die Lektion „Räumliche Joins und Enthaltensein“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?

Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. Räumliche Datentypen
  2. Entfernung und nächste Nachbarn
  3. Räumliche Joins und Enthaltensein
  4. Räumliche Indizes (GiST)
← Zurück zu SQL Academy