0Pricing
SQL Academy · Lekcja

Złączenia przestrzenne i zawieranie

Które punkty znajdują się w poszczególnych obszarach

Złączenia przestrzenne i zawieranie to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Czym jest złączenie przestrzenne?

Złączenie przestrzenne łączy dwie tabele na podstawie relacji geograficznej, a nie pasującego klucza. Zamiast pytać „czy ten identyfikator jest równy tamtemu identyfikatorowi?”, można zadać pytania takie jak „czy ten punkt znajduje się wewnątrz tego wielokąta?” lub „czy te dwa kształty się nakładają?”.

PostGIS rozszerza PostgreSQL o typy geometrii i funkcje przestrzenne, które umożliwiają wykonywanie takich złączeń. Wynik jest taki sam jak w przypadku zwykłego złączenia SQL JOIN — połączone wiersze z obu tabel — ale warunek ma charakter geometryczny.

Konfigurowanie przykładowych tabel

Utwórzmy dwie tabele do pracy: cities przechowującą lokalizacje punktów oraz countries przechowującą granice wielokątów. Obie używają typu GEOMETRY z SRID 4326 (standardowej szerokości i długości geograficznej WGS84).

Wartość SRID (Spatial Reference ID) informuje PostGIS, którego układu współrzędnych należy użyć. SRID 4326 jest najczęściej używany w przypadku danych 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)
);

Wstawianie przykładowych danych

Użyjemy ST_GeomFromText, aby wstawiać wartości geometrii w formacie Well-Known Text (WKT). WKT to standardowa tekstowa reprezentacja geometrii: POINT(lon lat) w przypadku punktów oraz POLYGON((...)) w przypadku wielokątów.

Należy zauważyć, że w formacie WKT długość geograficzna występuje przed szerokością geograficzną — odpowiada to konwencji X, Y stosowanej w matematyce.

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: kluczowa funkcja

ST_Contains(geometry A, geometry B) zwraca TRUE, gdy geometria A w całości zawiera geometrię B. W naszym przypadku ST_Contains(country.border, city.location) zwraca wartość true, gdy punkt miasta znajduje się wewnątrz wielokąta kraju.

To podstawa złączeń opartych na zawieraniu w PostGIS. Funkcja jest częścią standardu OGC i działa dla dowolnej kombinacji typów geometrii.

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

ST_Within: odwrotna perspektywa

ST_Within(geometry A, geometry B) jest dokładnym przeciwieństwem funkcji ST_Contains: zwraca TRUE, gdy geometria A znajduje się w całości wewnątrz geometrii B. ST_Within(city, country) jest logicznie równoważne wyrażeniu ST_Contains(country, city).

Obie funkcje zwracają tutaj ten sam wynik. Wybór między nimi zależy od czytelności — należy wybrać tę, która brzmi bardziej naturalnie w danym zapytaniu.

-- 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 do znajdowania niedopasowanych punktów

Zwykłe JOIN pomija miasta, które nie znajdują się wewnątrz żadnego wielokąta kraju. Należy użyć LEFT JOIN w połączeniu ze sprawdzeniem WHERE ... IS NULL, aby znaleźć punkty, dla których nie istnieje zawierający je wielokąt — jest to przydatne do wykrywania problemów z jakością danych lub punktów znajdujących się poza obszarem pokrycia.

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;

Zliczanie punktów w każdym wielokącie

Złączenia przestrzenne doskonale sprawdzają się w połączeniu z agregacją. Łącząc miasta z krajami, a następnie grupując wyniki według kraju, można zliczyć, ile punktów znajduje się wewnątrz każdego wielokąta. Ten wzorzec jest niezwykle często stosowany w analizie geograficznej — do zliczania sklepów w regionach, wydarzeń w okręgach, czujników w strefach i podobnych danych.

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;

Korzystanie z indeksu przestrzennego w celu zwiększenia wydajności

Złączenia przestrzenne mogą działać wolno w przypadku dużych zbiorów danych, ponieważ każdy punkt jest sprawdzany względem każdego wielokąta. Indeks GIST (Generalized Search Tree) pozwala PostGIS użyć wstępnego filtrowania za pomocą prostokątów ograniczających, aby pominąć większość porównań przed wykonaniem dokładnego sprawdzenia ST_Contains.

Zawsze należy tworzyć indeks GIST dla kolumn z geometrią używanych w złączeniach lub filtrowaniu. Optymalizator zapytań użyje go automatycznie.

-- 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: nakładające się geometrie

ST_Intersects(A, B) zwraca TRUE, jeśli dwie geometrie mają wspólny choćby jeden punkt — również wtedy, gdy tylko stykają się na granicy. Funkcja jest mniej restrykcyjna niż ST_Contains: dwa nakładające się wielokąty przecinają się, nawet jeśli żaden z nich nie zawiera w całości drugiego.

W przypadku testów punktu względem wielokąta ST_Intersects i ST_Contains są równoważne, ale ST_Intersects efektywniej korzysta z indeksu GIST i często jest preferowana w praktyce.

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

Łączenie punktów z najbliższym wielokątem

Gdy punkt leży dokładnie na granicy lub bardzo blisko wielu wielokątów, zamiast wszystkich dopasowań można wybrać najbliższy wielokąt. ST_Distance mierzy odległość między dwiema geometriami, umożliwiając stosowanie wzorców ORDER BY + LIMIT lub wyspecjalizowanego złączenia LATERAL z ORDER BY ... LIMIT 1.

Nazywa się to złączeniem najbliższego sąsiada i często stosuje się je podczas dopasowywania śladów GPS do odcinków dróg lub przypisywania punktu do najbliższego regionu.

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

Przykład praktyczny: restauracje w okręgach

Oto realistyczny przykład od początku do końca: znalezienie okręgu miejskiego, do którego należy każda restauracja, a następnie zliczenie restauracji w każdym okręgu. Ten wzorzec można zastosować w dowolnym scenariuszu punktu względem wielokąta — do bankomatów w dzielnicach, wypadków w rejonach lub zamówień w strefach dostaw.

Zapytanie używa ST_Within w złączeniu przestrzennym i grupuje wyniki na potrzeby raportu podsumowującego.

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

Szybkie sprawdzenie

Sprawdź swoją wiedzę na temat złączeń przestrzennych i zawierania geometrii w PostGIS.

Podsumowanie: złączenia przestrzenne i zawieranie

W tej lekcji poznali Państwo sposób odpowiadania na pytanie „które punkty znajdują się w których obszarach?” za pomocą złączeń przestrzennych PostGIS.

Najważniejsze informacje:

  • ST_Contains(polygon, point) — zwraca wartość true, gdy wielokąt w całości zawiera punkt.
  • ST_Within(point, polygon) — funkcja odwrotna; jest logicznie równoważna funkcji ST_Contains.
  • ST_Intersects — bardziej ogólny test nakładania się geometrii; dobrze współpracuje z indeksem w przypadku punktu względem wielokąta.
  • Indeksy GIST na kolumnach z geometrią są niezbędne dla wydajności przy dużej skali.
  • Złączenia przestrzenne można łączyć z GROUP BY, aby zliczać punkty lub wykonywać ich agregację według regionu.
  • Należy używać LEFT JOIN, aby wykrywać punkty znajdujące się poza wszystkimi wielokątami.

Złączenia przestrzenne umożliwiają przeprowadzanie analiz geograficznych bezpośrednio w SQL — bez konieczności korzystania z zewnętrznego narzędzia GIS.

Często zadawane pytania

Czy lekcja „Złączenia przestrzenne i zawieranie” jest bezpłatna?

Tak — pełny tekst „Złączenia przestrzenne i zawieranie” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Złączenia przestrzenne i zawieranie”?

Które punkty znajdują się w poszczególnych obszarach Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.

Ile czasu zajmuje lekcja „Złączenia przestrzenne i zawieranie”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Typy danych przestrzennych
  2. Odległość i najbliżsi sąsiedzi
  3. Złączenia przestrzenne i zawieranie
  4. Indeksy przestrzenne (GiST)
← Powrót do SQL Academy