SQL Academy · Lezione

Distanze e vicini più prossimi

Trovi ciò che si trova nelle vicinanze.

Distanze e vicini più prossimi è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è la distanza spaziale?

Nei database geospaziali, la distanza è la separazione misurata tra due posizioni geografiche. PostGIS fornisce potenti funzioni per calcolare le distanze tra punti, linee, poligoni e altri tipi di geometria.

Comprendere le query sulla distanza consente di rispondere a domande come: Qual è il ristorante più vicino a me? oppure Quali clienti si trovano entro 5 km dal nostro magazzino?

La funzione ST_Distance

ST_Distance(geom_a, geom_b) restituisce la distanza minima tra due oggetti geometry. Per impostazione predefinita, quando si utilizzano tipi geometry standard, il risultato è espresso nelle stesse unità del sistema di riferimento delle coordinate, solitamente in gradi per EPSG:4326.

Per ottenere risultati significativi in metri, utilizzi tipi geography oppure riproietti i dati.

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

Creazione di una tabella di esempio

Crei una semplice tabella di luoghi con coordinate geografiche. Utilizzeremo il tipo geography, in modo che tutti i calcoli delle distanze restituiscano automaticamente i metri, senza bisogno di alcuna proiezione manuale.

Questa tabella conterrà caffetterie di una città, ciascuna con un nome e una posizione memorizzata come punto.

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

Calcolo della distanza da un punto fisso

Una volta disponibili i dati sulle posizioni, può calcolare la distanza di ogni riga da un punto di riferimento. Qui calcolerà la distanza di ogni caffetteria da un utente che si trova alle coordinate (28.9800, 41.0100).

Il risultato è espresso in metri perché abbiamo utilizzato il tipo geography.

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

Ricerca del vicino più prossimo

Per trovare la singola posizione più vicina a un determinato punto, ordini per distanza e restituisca una sola riga con LIMIT 1. Questa è la classica query per la ricerca del vicino più prossimo.

Questo schema è molto comune nelle applicazioni che utilizzano la posizione: per trovare il negozio, il medico o la fermata del trasporto pubblico più vicini.

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;

L'operatore <-> per il vicino più prossimo

PostGIS fornisce l'operatore <-> (KNN — K Nearest Neighbors), che tiene conto dell'indice. A differenza di ST_Distance in una clausola ORDER BY, <-> può utilizzare un indice spaziale GIST per evitare di esaminare ogni riga.

Quando ha bisogno dei vicini più prossimi in modo efficiente, preferisca sempre <-> in ORDER BY. Crei un indice come il seguente per supportarlo:

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;

Filtro in base alla distanza (ST_DWithin)

ST_DWithin(geom_a, geom_b, distance) restituisce TRUE quando due geometrie si trovano entro una distanza specificata l'una dall'altra. Per i tipi geography, la distanza è espressa in metri.

È più efficiente di ST_Distance(...) < radius in una clausola WHERE, perché ST_DWithin tiene conto dell'indice e interrompe la ricerca non appena la condizione è soddisfatta.

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;

I primi N vicini con limite di raggio

Un approccio comune negli ambienti di produzione combina ST_DWithin come pre-filtro con l'ordinamento tramite <->. Il controllo ST_DWithin utilizza l'indice spaziale per scartare rapidamente le righe lontane, dopodiché i candidati rimanenti vengono ordinati in base alla distanza esatta.

In questo modo può ottenere in modo molto efficiente le N posizioni più vicine entro un raggio massimo.

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;

Distanza tra due tabelle (distanza incrociata)

Può calcolare le distanze tra righe di due tabelle diverse utilizzando una JOIN combinata con ST_Distance. È utile, ad esempio, per associare ogni cliente al magazzino più vicino o ogni incidente all'ospedale più vicino.

Di seguito troviamo la caffetteria più vicina per ogni utente in una tabella users, utilizzando un lateral join, un potente approccio di PostgreSQL per le sottoquery per riga.

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;

Formattazione dell'output della distanza

Le distanze grezze espresse in metri possono essere difficili da leggere. Può formattarle in chilometri oppure aggiungere etichette intuitive utilizzando espressioni CASE e la formattazione delle stringhe. Ecco una query che presenta la distanza in metri per le distanze brevi e in chilometri per quelle più lunghe.

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;

Assegnazione di un rango in base alla distanza

Le funzioni finestra come RANK() e ROW_NUMBER() si combinano perfettamente con le query sulla distanza. Può assegnare un rango di prossimità a ogni posizione rispetto a un utente, una funzione utile per creare elenchi di consigli classificati o risultati di ricerca ordinati in base alla vicinanza.

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;

Verifica delle conoscenze

Verifichi la Sua comprensione delle query sulla distanza e delle ricerche dei vicini più prossimi in PostGIS.

Riepilogo della lezione

In questa lezione ha appreso come utilizzare le distanze spaziali e le query per la ricerca dei vicini più prossimi in PostGIS:

ST_Distance calcola la distanza esatta tra due geometrie; utilizzi il tipo geography per ottenere automaticamente i metri.
ST_DWithin filtra in modo efficiente le righe che rientrano in un raggio utilizzando l'indice spaziale; lo preferisca a ST_Distance < radius nelle clausole WHERE.
L'operatore <-> (KNN) in ORDER BY tiene conto dell'indice ed è il modo più rapido per recuperare i primi N vicini.
Combini ST_DWithin come pre-filtro con l'ordinamento tramite <-> per ottenere query top-N entro un raggio nel modo più efficiente.
CROSS JOIN LATERAL consente di trovare la corrispondenza più vicina in un'altra tabella riga per riga.
Le funzioni finestra come ROW_NUMBER() possono assegnare ranghi di prossimità ai risultati ordinati per distanza.

Questi schemi costituiscono la base delle funzionalità basate sulla posizione nelle applicazioni del mondo reale.

Domande Frequenti

La lezione «Distanze e vicini più prossimi» è gratuita?

Sì — il testo completo di «Distanze e vicini più prossimi» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Distanze e vicini più prossimi»?

Trovi ciò che si trova nelle vicinanze. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.

Quanto tempo richiede la lezione «Distanze e vicini più prossimi»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy