0Pricing
SQL Academy · Lezione

Join spaziali e contenimento

Scopra quali punti ricadono in quali aree.

Join spaziali e contenimento è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 3 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'è una join spaziale?

Una join spaziale combina due tabelle in base a una relazione geografica anziché a una chiave corrispondente. Invece di chiedersi "questo ID è uguale a quell'ID?", può chiedersi "questo punto si trova all'interno di quel poligono?" oppure "queste due forme si sovrappongono?"

PostGIS estende PostgreSQL con tipi geometry e funzioni spaziali che rendono possibili queste JOIN. Il risultato è uguale a quello di una JOIN SQL standard: le righe delle due tabelle vengono combinate, ma la condizione è geometrica.

Configurazione delle tabelle di esempio

Creiamo due tabelle su cui lavorare: cities, che contiene posizioni puntuali, e countries, che contiene i confini dei poligoni. Entrambe usano il tipo GEOMETRY di PostGIS con SRID 4326 (latitudine/longitudine WGS84 standard).

Lo SRID (Spatial Reference ID, identificatore del riferimento spaziale) indica a PostGIS quale sistema di coordinate utilizzare. SRID 4326 è il più comune per i dati 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)
);

Inserimento di dati di esempio

Utilizzeremo ST_GeomFromText per inserire valori geometrici dal formato Well-Known Text (WKT). WKT è una rappresentazione testuale standard delle geometrie: POINT(lon lat) per i punti e POLYGON((...)) per i poligoni.

Noti che in WKT la longitudine precede la latitudine: ciò corrisponde alla convenzione X, Y della matematica.

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 funzione principale

ST_Contains(geometry A, geometry B) restituisce TRUE quando la geometria A contiene completamente la geometria B. Nel nostro caso, ST_Contains(country.border, city.location) restituisce true quando un punto di città si trova all'interno di un poligono che rappresenta un Paese.

È il fulcro dei join di contenimento in PostGIS. La funzione fa parte dello standard OGC e funziona con qualsiasi combinazione di tipi di geometria.

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 prospettiva inversa

ST_Within(geometry A, geometry B) è l'esatto opposto di ST_Contains: restituisce TRUE quando la geometria A si trova interamente all'interno della geometria B. ST_Within(city, country) è logicamente equivalente a ST_Contains(country, city).

In questo caso entrambe le funzioni producono lo stesso risultato. La scelta tra le due dipende dalla leggibilità: scelga quella che rende la query più naturale.

-- 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 per trovare i punti senza corrispondenza

Un JOIN normale esclude le città che non ricadono in alcun poligono di Paese. Usi un LEFT JOIN combinato con un controllo WHERE ... IS NULL per trovare i punti che non hanno alcun poligono contenitore: è utile per rilevare problemi di qualità dei dati o punti al di fuori dell'area coperta.

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;

Conteggio dei punti per poligono

I join spaziali danno il meglio se combinati con l'aggregazione. Unendo cities a countries e raggruppando poi per Paese, è possibile contare quanti punti ricadono all'interno di ciascun poligono. Questo schema è molto comune nell'analisi geografica: contare i negozi per regione, gli eventi per distretto, i sensori per zona e così via.

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;

Uso di un indice spaziale per le prestazioni

I join spaziali possono essere lenti su dataset di grandi dimensioni, perché ogni punto viene confrontato con ogni poligono. Un indice GIST (Generalized Search Tree) consente a PostGIS di usare un filtro preliminare basato sui riquadri delimitatori, saltando la maggior parte dei confronti prima di eseguire il test preciso ST_Contains.

Crei sempre un indice GIST sulle colonne geometriche utilizzate nei join o nei filtri. Il pianificatore delle query lo utilizzerà automaticamente.

-- 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: geometrie sovrapposte

ST_Intersects(A, B) restituisce TRUE se due geometrie hanno almeno un punto in comune, anche se si toccano soltanto lungo un confine. È più permissivo di ST_Contains: due poligoni sovrapposti si intersecano anche se nessuno contiene completamente l'altro.

Per i test punto-nel-poligono, ST_Intersects e ST_Contains sono equivalenti, ma ST_Intersects sfrutta l'indice GIST in modo più efficiente ed è spesso preferito nella pratica.

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

Join dei punti al poligono più vicino

Quando un punto si trova esattamente su un confine o molto vicino a più poligoni, potrebbe essere necessario il poligono più vicino invece di tutte le corrispondenze. ST_Distance misura la distanza tra due geometrie, consentendo di usare schemi ORDER BY + LIMIT o il join specializzato LATERAL con ORDER BY ... LIMIT 1.

Si parla di join del vicino più prossimo, una tecnica comune per associare tracce GPS ai segmenti stradali o assegnare un punto alla regione più vicina.

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

Esempio pratico: ristoranti nei distretti

Ecco un esempio realistico completo: trovare a quale distretto cittadino appartiene ogni ristorante e quindi contare i ristoranti per distretto. Questo schema si applica a qualsiasi scenario punto-nel-poligono: bancomat nei quartieri, incidenti nei distretti, ordini nelle zone di consegna.

La query usa ST_Within all'interno di un join spaziale e raggruppa i risultati per un report riepilogativo.

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

Verifica rapida

Verifichi la sua comprensione dei join spaziali e del contenimento in PostGIS.

Riepilogo: join spaziali e contenimento

In questa lezione ha imparato a rispondere alla domanda "quali punti ricadono in quali aree?" usando i join spaziali di PostGIS.

Elementi chiave:

  • ST_Contains(polygon, point) — restituisce true quando il poligono contiene completamente il punto.
  • ST_Within(point, polygon) — è il contrario; logicamente equivalente a ST_Contains.
  • ST_Intersects — test di sovrapposizione più generale, che sfrutta bene l'indice per i test punto-nel-poligono.
  • Indici GIST sulle colonne geometriche sono essenziali per ottenere buone prestazioni su larga scala.
  • Combini i join spaziali con GROUP BY per contare o aggregare i punti per regione.
  • Usi LEFT JOIN per rilevare i punti che si trovano al di fuori di tutti i poligoni.

I join spaziali permettono di eseguire analisi geografiche direttamente in SQL, senza bisogno di strumenti GIS esterni.

Domande Frequenti

La lezione «Join spaziali e contenimento» è gratuita?

Sì — il testo completo di «Join spaziali e contenimento» è 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 «Join spaziali e contenimento»?

Scopra quali punti ricadono in quali aree. 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 3 di 4.

Quanto tempo richiede la lezione «Join spaziali e contenimento»?

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

  1. Tipi di dati spaziali
  2. Distanze e vicini più prossimi
  3. Join spaziali e contenimento
  4. Indici spaziali (GiST)
← Torna a SQL Academy