0Pricing
SQL Academy · Lekcja

Odległość i najbliżsi sąsiedzi

Znajdź to, co znajduje się najbliżej

Odległość i najbliżsi sąsiedzi to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 2 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 odległość przestrzenna?

W geoprzestrzennych bazach danych odległość to zmierzona separacja między dwiema lokalizacjami geograficznymi. PostGIS udostępnia zaawansowane funkcje obliczania odległości między punktami, liniami, wielokątami i innymi typami geometrii.

Rozumienie zapytań dotyczących odległości pozwala odpowiadać na pytania takie jak: Jaka restauracja znajduje się najbliżej? lub Którzy klienci znajdują się w promieniu 5 km od naszego magazynu?

Funkcja ST_Distance

ST_Distance(geom_a, geom_b) zwraca minimalną odległość między dwoma obiektami geometrycznymi. Domyślnie, w przypadku zwykłych typów geometry, wynik jest wyrażony w jednostkach układu odniesienia współrzędnych (zwykle w stopniach dla EPSG:4326).

Aby uzyskać miarodajne wyniki w metrach, należy użyć typów geography lub przekształcić odwzorowanie danych.

SELECT ST_Distance(
  ST_MakePoint(28.9784, 41.0082)::geography,
  ST_MakePoint(29.0100, 41.0200)::geography
) AS distance_meters;

Konfigurowanie przykładowej tabeli

Utwórzmy prostą tabelę miejsc ze współrzędnymi geograficznymi. Użyjemy typu geography, aby wszystkie obliczenia odległości automatycznie zwracały wyniki w metrach — bez konieczności ręcznego przekształcania odwzorowania.

Tabela będzie przechowywać kawiarnie w mieście, a dla każdej z nich jej nazwę i lokalizację zapisaną jako punkt.

CREATE TABLE cafes (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  location GEOGRAPHY(Point, 4326)
);

INSERT INTO cafes (name, location) VALUES
  ('Cafe Alpha',   ST_MakePoint(28.9784, 41.0082)::geography),
  ('Cafe Beta',    ST_MakePoint(28.9900, 41.0150)::geography),
  ('Cafe Gamma',   ST_MakePoint(29.0100, 41.0200)::geography),
  ('Cafe Delta',   ST_MakePoint(28.9650, 40.9980)::geography),
  ('Cafe Epsilon', ST_MakePoint(29.0250, 41.0350)::geography);

Obliczanie odległości od stałego punktu

Po zgromadzeniu danych lokalizacyjnych można obliczyć odległość każdego wiersza od punktu odniesienia. W tym przykładzie obliczamy odległość każdej kawiarni od użytkownika znajdującego się we współrzędnych (28.9800, 41.0100).

Wynik jest wyrażony w metrach, ponieważ użyliśmy typu geography.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
ORDER BY distance_m;

Wyszukiwanie najbliższego sąsiada

Aby znaleźć pojedynczą najbliższą lokalizację względem danego punktu, należy posortować wyniki według odległości i pobrać tylko jeden wiersz za pomocą LIMIT 1. Jest to klasyczne zapytanie do wyszukiwania najbliższego sąsiada.

Ten schemat jest niezwykle często używany w aplikacjach uwzględniających lokalizację: do znajdowania najbliższego sklepu, lekarza lub przystanku komunikacji publicznej.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
LIMIT 1;

Operator <-> do wyszukiwania najbliższego sąsiada

PostGIS udostępnia operator <-> (KNN — K Nearest Neighbors), który uwzględnia indeks. W przeciwieństwie do funkcji ST_Distance użytej w klauzuli ORDER BY operator <-> może korzystać z przestrzennego indeksu GiST, aby uniknąć skanowania każdego wiersza.

Gdy potrzebne jest wydajne wyszukiwanie najbliższych sąsiadów, w klauzuli ORDER BY należy zawsze preferować operator <->. Aby go obsłużyć, należy utworzyć indeks w następujący sposób:

CREATE INDEX ON cafes USING GIST (location);

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
LIMIT 5;

Filtrowanie według odległości (ST_DWithin)

ST_DWithin(geom_a, geom_b, distance) zwraca TRUE, gdy dwie geometrie znajdują się w określonej odległości od siebie lub bliżej. W przypadku typów geography odległość jest wyrażona w metrach.

Jest to wydajniejsze niż ST_Distance(...) < radius w klauzuli WHERE, ponieważ ST_DWithin uwzględnia indeks i kończy wyszukiwanie, gdy tylko warunek zostanie spełniony.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
WHERE ST_DWithin(
  location,
  ST_MakePoint(28.9800, 41.0100)::geography,
  2000
)
ORDER BY distance_m;

N najbliższych z ograniczeniem promienia

Typowy schemat stosowany w środowisku produkcyjnym łączy ST_DWithin jako filtr wstępny z sortowaniem za pomocą <->. Sprawdzenie ST_DWithin korzysta z indeksu przestrzennego, aby szybko odrzucić odległe wiersze, a następnie pozostałe kandydatury są sortowane według dokładnej odległości.

Pozwala to bardzo wydajnie pobrać N najbliższych lokalizacji w określonym maksymalnym promieniu.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
WHERE ST_DWithin(
  location,
  ST_MakePoint(28.9800, 41.0100)::geography,
  5000
)
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
LIMIT 3;

Odległość między dwiema tabelami (odległość krzyżowa)

Odległości między wierszami w dwóch różnych tabelach można obliczać za pomocą złączenia połączonego z funkcją ST_Distance. Jest to przydatne na przykład podczas dopasowywania każdego klienta do najbliższego magazynu lub każdego zgłoszenia do najbliższego szpitala.

Poniżej znajdziemy najbliższą kawiarnię dla każdego użytkownika z tabeli users, korzystając ze złączenia lateralnego — zaawansowanego wzorca PostgreSQL służącego do wykonywania podzapytań dla poszczególnych wierszy.

SELECT
  u.username,
  c.name AS nearest_cafe,
  ROUND(ST_Distance(u.location, c.location)::NUMERIC) AS distance_m
FROM (
  VALUES
    ('alice', ST_MakePoint(28.9810, 41.0095)::geography),
    ('bob',   ST_MakePoint(29.0200, 41.0300)::geography)
) AS u(username, location)
CROSS JOIN LATERAL (
  SELECT name, location
  FROM cafes
  ORDER BY location <-> u.location
  LIMIT 1
) c;

Formatowanie wyników odległości

Surowe odległości wyrażone w metrach mogą być trudne do odczytania. Można sformatować je jako kilometry lub dodać przyjazne dla użytkownika etykiety za pomocą wyrażeń CASE i formatowania ciągów znaków. Oto zapytanie, które przedstawia krótkie odległości w metrach, a dłuższe w kilometrach.

SELECT
  name,
  CASE
    WHEN ST_Distance(location, ST_MakePoint(28.9800, 41.0100)::geography) < 1000
    THEN ROUND(ST_Distance(location, ST_MakePoint(28.9800, 41.0100)::geography)::NUMERIC)
         || ' m'
    ELSE ROUND((ST_Distance(location, ST_MakePoint(28.9800, 41.0100)::geography) / 1000.0)::NUMERIC, 2)
         || ' km'
  END AS formatted_distance
FROM cafes
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography;

Przypisywanie rangi według odległości

Funkcje okna, takie jak RANK() i ROW_NUMBER(), doskonale współpracują z zapytaniami dotyczącymi odległości. Można przypisać każdej lokalizacji rangę bliskości względem użytkownika, co jest przydatne podczas tworzenia uporządkowanych list rekomendacji lub wyników wyszukiwania posortowanych według odległości.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m,
  ROW_NUMBER() OVER (
    ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
  ) AS proximity_rank
FROM cafes;

Sprawdzenie wiedzy

Proszę sprawdzić swoją znajomość zapytań dotyczących odległości i wyszukiwania najbliższych sąsiadów w PostGIS.

Podsumowanie lekcji

W tej lekcji nauczyli się Państwo pracować z odległościami przestrzennymi i zapytaniami do wyszukiwania najbliższych sąsiadów w PostGIS:

  • ST_Distance oblicza dokładną odległość między dwiema geometriami; należy użyć typu geography, aby automatycznie uzyskać wynik w metrach.
  • ST_DWithin wydajnie filtruje wiersze znajdujące się w określonym promieniu, korzystając z indeksu przestrzennego — w klauzulach WHERE należy preferować tę funkcję zamiast ST_Distance < radius.
  • <-> operator (KNN) w klauzuli ORDER BY uwzględnia indeks i jest najszybszym sposobem pobrania N najbliższych sąsiadów.
  • Połączenie ST_DWithin jako filtra wstępnego z sortowaniem za pomocą <-> zapewnia najbardziej wydajne zapytania typu top-N w promieniu.
  • CROSS JOIN LATERAL pozwala znajdować najbliższe dopasowanie z innej tabeli osobno dla każdego wiersza.
  • Funkcje okna, takie jak ROW_NUMBER(), mogą przypisywać rangi bliskości wynikom uporządkowanym według odległości.

Te schematy stanowią podstawę funkcji uwzględniających lokalizację w rzeczywistych aplikacjach.

Często zadawane pytania

Czy lekcja „Odległość i najbliżsi sąsiedzi” jest bezpłatna?

Tak — pełny tekst „Odległość i najbliżsi sąsiedzi” 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 „Odległość i najbliżsi sąsiedzi”?

Znajdź to, co znajduje się najbliżej Ć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 2 z 4.

Ile czasu zajmuje lekcja „Odległość i najbliżsi sąsiedzi”?

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