PricingLoginStart free
SQL Academy · leksjon

Romlige koblinger og innebygdhet

Hvilke punkter som ligger i hvilke områder.

Leksjon 3 av 413 trinn

Romlige koblinger og innebygdhet er en gratis leksjon i SQL Academy på CoddyKit. Dette er leksjon 3 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i SQL Academy, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i SQL Academy inneholder totalt 4 leksjoner.

Hva er en romlig join?

En romlig join kombinerer to tabeller basert på en geografisk relasjon i stedet for en samsvarende nøkkel. I stedet for å spørre «er denne ID-en lik den ID-en?», stiller De spørsmål som «faller dette punktet innenfor denne polygonen?» eller «overlapper disse to formene?»

PostGIS utvider PostgreSQL med geometrityper og romlige funksjoner som gjør disse joinene mulige. Resultatet er det samme som ved en vanlig SQL JOIN – rader fra begge tabellene kombineres – men betingelsen er geometrisk.

Sette opp eksempeltabeller

Vi oppretter to tabeller vi kan jobbe med: cities som inneholder punktlokasjoner, og countries som inneholder polygoner for landegrenser. Begge bruker typen GEOMETRY fra PostGIS med SRID 4326 (standard WGS84 for breddegrad og lengdegrad).

SRID (Spatial Reference ID) forteller PostGIS hvilket koordinatsystem som skal brukes. SRID 4326 er det vanligste for GPS-data.

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

Sette inn eksempeldata

Vi bruker ST_GeomFromText til å sette inn geometriverdier i Well-Known Text-formatet (WKT). WKT er en standardisert tekstrepresentasjon for geometrier: POINT(lon lat) for punkter og POLYGON((...)) for polygoner.

Legg merke til at lengdegrad kommer før breddegrad i WKT — dette følger X-, Y-konvensjonen i matematikken.

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: Kjernefunksjonen

ST_Contains(geometry A, geometry B) returnerer TRUE når geometri A inneholder geometri B fullstendig. I vårt tilfelle returnerer ST_Contains(country.border, city.location) true når et bypunkt ligger innenfor et landpolygon.

Dette er selve kjernen i joiner basert på inneslutning i PostGIS. Funksjonen er en del av OGC-standarden og fungerer for alle kombinasjoner av geometrityper.

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

ST_Within: Det omvendte perspektivet

ST_Within(geometry A, geometry B) er det nøyaktige motsatte av ST_Contains: den returnerer TRUE når geometri A ligger fullstendig innenfor geometri B. ST_Within(city, country) er logisk identisk med ST_Contains(country, city).

Begge funksjonene gir samme resultat her. Valget mellom dem handler om lesbarhet — velg den som gir mest naturlig mening i spørringen.

-- 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 for å finne punkter uten treff

En vanlig JOIN utelater byer som ikke ligger innenfor noe landpolygon. Bruk en LEFT JOIN kombinert med en WHERE ... IS NULL-kontroll for å finne punkter som ikke har noe polygon som inneholder dem — nyttig for å oppdage problemer med datakvaliteten eller punkter utenfor dekningsområdet.

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;

Telle punkter per polygon

Romlige joiner er særlig nyttige når de kombineres med aggregering. Ved å koble byer til land og deretter gruppere etter land kan De telle hvor mange punkter som ligger innenfor hvert polygon. Dette mønsteret er svært vanlig i geografiske analyser — for eksempel når man teller butikker per region, hendelser per distrikt, sensorer per sone og så videre.

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;

Bruke en romlig indeks for bedre ytelse

Romlige joiner kan være trege på store datasett fordi hvert punkt testes mot hvert polygon. En GIST-indeks (Generalized Search Tree) lar PostGIS bruke et forhåndsfilter basert på bounding boxes, slik at de fleste sammenligningene kan hoppes over før den nøyaktige ST_Contains-testen kjøres.

Opprett alltid en GIST-indeks på geometrikolonner som brukes i joiner eller filtrering. Spørringsplanleggeren bruker den automatisk.

-- 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: Overlappende geometrier

ST_Intersects(A, B) returnerer TRUE hvis to geometrier har minst ett felles punkt — også når de bare berører hverandre langs en grense. Den er mer generell enn ST_Contains: to overlappende polygoner intersecter selv om ingen av dem inneholder det andre fullstendig.

For tester av punkter i polygoner er ST_Intersects og ST_Contains ekvivalente, men ST_Intersects utnytter GIST-indeksen mer effektivt og foretrekkes ofte i praksis.

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

Koble punkter til det nærmeste polygonet

Når et punkt ligger nøyaktig på en grense eller svært nær flere polygoner, kan De ønske det nærmeste polygonet i stedet for alle treffene. ST_Distance måler avstanden mellom to geometrier, slik at De kan bruke mønstre med ORDER BY + LIMIT eller den spesialiserte LATERAL-joinen med ORDER BY ... LIMIT 1.

Dette kalles en join med nærmeste nabo og er vanlig når GPS-spor festes til veistrekninger eller et punkt tilordnes den nærmeste regionen.

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

Praktisk eksempel: Restauranter i bydeler

Her er et realistisk eksempel fra start til slutt: finn hvilken bydel hver restaurant tilhører, og tell deretter antallet restauranter per bydel. Dette mønsteret gjelder for alle scenarioer der punkter ligger i polygoner — minibanker i nabolag, ulykker i valgdistrikter, bestillinger i leveringssoner.

Spørringen bruker ST_Within i en romlig join og grupperer resultatene for en sammendragsrapport.

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

Hurtigsjekk

Test forståelsen av romlige joiner og inneslutning i PostGIS.

Besvar den i den interaktive leksjonen

Oppsummering: Romlige joiner og inneslutning

I denne leksjonen lærte De hvordan De kan besvare spørsmålet "hvilke punkter ligger i hvilke områder?" ved hjelp av romlige joiner i PostGIS.

Viktigste punkter:

  • ST_Contains(polygon, point) — returnerer true når polygonet inneholder punktet fullstendig.
  • ST_Within(point, polygon) — det omvendte; logisk ekvivalent med ST_Contains.
  • ST_Intersects — en mer generell test av overlapp; indeksvennlig for punkter i polygoner.
  • GIST-indekser på geometrikolonner er avgjørende for god ytelse i stor skala.
  • Kombiner romlige joiner med GROUP BY for å telle eller aggregere punkter per region.
  • Bruk LEFT JOIN til å oppdage punkter som ligger utenfor alle polygoner.

Romlige joiner gjør geografiske analyser direkte i SQL mulig — uten behov for et eksternt GIS-verktøy.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs
46
Leksjoner
183
Start læringen gratisSe hele kurset

Ofte stilte spørsmål

Er leksjonen «Romlige koblinger og innebygdhet» gratis?

Ja – hele teksten i «Romlige koblinger og innebygdhet» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av SQL Academy-kurset, kan du oppgradere til CoddyKit PRO. Kurset i SQL Academy inneholder totalt 4 leksjoner.

Hva lærer jeg i «Romlige koblinger og innebygdhet»?

Hvilke punkter som ligger i hvilke områder. Du øver på SQL Academy med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med SQL Academy?

Ingen tidligere erfaring er nødvendig. SQL Academy på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 3 av 4.

Hvor lang tid tar leksjonen «Romlige koblinger og innebygdhet»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne SQL Academy-leksjonen?

Ja. Alle SQL Academy-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

  1. Romlige datatyper
  2. Avstand og nærmeste naboer
  3. Romlige koblinger og innebygdhet
  4. Romlige indekser (GiST)
← Tilbake til SQL Academy