Rumlige joins og indeholdelse
Hvilke punkter ligger i hvilke områder
Rumlige joins og indeholdelse er en gratis SQL Academy-lektion på CoddyKit. Dette er lektion 3 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i SQL Academy, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. SQL Academy-kurset indeholder 4 lektioner i alt.
Hvad er et spatialt JOIN
Et spatialt JOIN kombinerer to tabeller på baggrund af en geografisk relation i stedet for en matchende nøgle. I stedet for at spørge "er dette id lig med hint id?" spørger du for eksempel "falder dette punkt inden i denne polygon?" eller "overlapper disse to former?"
PostGIS udvider PostgreSQL med geometrityper og spatiale funktioner, der gør disse JOIN-operationer mulige. Resultatet er det samme som ved et almindeligt SQL-JOIN — rækker fra begge tabeller kombineres — men betingelsen er geometrisk.
Opsætning af eksempeltabeller
Lad os oprette to tabeller at arbejde med: cities, som indeholder punktplaceringer, og countries, som indeholder polygonafgrænsninger. Begge bruger typen GEOMETRY fra PostGIS med SRID 4326 (standarden WGS84 for breddegrad/længdegrad).
SRID (ID for rumlig reference) fortæller PostGIS, hvilket koordinatsystem der skal bruges. SRID 4326 er det mest almindelige til 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)
);Indsættelse af eksempeldata
Vi bruger ST_GeomFromText til at indsætte geometriværdier i formatet Well-Known Text (WKT). WKT er en standardiseret tekstrepræsentation af geometrier: POINT(lon lat) for punkter og POLYGON((...)) for polygoner.
Bemærk, at længdegrad kommer før breddegrad i WKT — det stemmer overens med X-, Y-konventionen i matematik.
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: Den centrale funktion
ST_Contains(geometry A, geometry B) returnerer TRUE, når geometri A indeholder geometri B fuldstændigt. I vores tilfælde returnerer ST_Contains(country.border, city.location) sandt, når et bypunkt ligger inden i en landepolygon.
Dette er kernen i sammenføjninger baseret på indeholdelse i PostGIS. Funktionen er en del af OGC-standarden og fungerer med alle kombinationer af 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 perspektiv
ST_Within(geometry A, geometry B) er den nøjagtige modsætning til ST_Contains: den returnerer TRUE, når geometri A er helt inden i geometri B. ST_Within(city, country) er logisk identisk med ST_Contains(country, city).
Begge funktioner giver samme resultat her. Valget mellem dem handler om læsbarhed — vælg den, der lyder mest naturlig i din forespørgsel.
-- 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 til at finde punkter uden match
Et almindeligt JOIN udelader byer, der ikke ligger inden i nogen landepolygon. Brug en LEFT JOIN kombineret med en kontrol af WHERE ... IS NULL for at finde punkter, der ikke har nogen polygon, som indeholder dem — nyttigt til at opdage problemer med datakvaliteten eller punkter uden for dit dækningsområde.
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;Optælling af punkter pr. polygon
Rumlige sammenføjninger er særligt nyttige sammen med aggregering. Ved at sammenføje byer med lande og derefter gruppere efter land kan du tælle, hvor mange punkter der ligger inden i hver polygon. Dette mønster er meget almindeligt i geografisk analyse — optælling af butikker pr. region, begivenheder pr. distrikt, sensorer pr. zone 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;Brug af et rumligt indeks for bedre ydeevne
Rumlige sammenføjninger kan være langsomme på store datasæt, fordi hvert punkt testes mod hver polygon. Et GIST-indeks (Generaliseret søgetræ) lader PostGIS bruge et forfilter baseret på afgrænsningsbokse, så de fleste sammenligninger springes over, før den præcise ST_Contains-kontrol udføres.
Opret altid et GIST-indeks på geometrikolonner, som du sammenføjer eller filtrerer efter. Forespørgselsplanlæggeren bruger det 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: Geometrier med overlap
ST_Intersects(A, B) returnerer TRUE, hvis to geometrier har mindst ét fælles punkt — også hvis de kun berører hinanden ved en grænse. Den er mindre restriktiv end ST_Contains: to overlappende polygoner skærer hinanden, selv om ingen af dem indeholder den anden fuldstændigt.
Ved punkt-i-polygon-kontroller er ST_Intersects og ST_Contains ækvivalente, men ST_Intersects bruger GIST-indekset mere effektivt og foretrækkes 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);Sammenføjning af punkter med den nærmeste polygon
Når et punkt ligger præcis på en grænse eller meget tæt på flere polygoner, vil du måske have den nærmeste polygon i stedet for alle resultater. ST_Distance måler afstanden mellem to geometrier, så du kan bruge mønstre med ORDER BY + LIMIT eller den specialiserede LATERAL-sammenføjning med ORDER BY ... LIMIT 1.
Dette kaldes en nærmeste-nabo-sammenføjning og bruges ofte til at tilpasse GPS-spor til vejsegmenter eller knytte et punkt til den nærmeste region.
-- 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 bydele
Her er et realistisk eksempel fra start til slut: Find ud af, hvilken bydel hver restaurant hører til, og tæl derefter restauranterne pr. bydel. Dette mønster gælder for alle situationer med punkter i polygoner — hæveautomater i kvarterer, ulykker i politidistrikter og ordrer i leveringszoner.
Forespørgslen bruger ST_Within i en rumlig sammenføjning og grupperer resultaterne til en opsummerende rapport.
-- 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;Hurtig kontrol
Kontrollér din forståelse af rumlige sammenføjninger og indeholdelse i PostGIS.
Opsummering: Rumlige sammenføjninger og indeholdelse
I denne lektion har du lært, hvordan du kan besvare spørgsmålet "hvilke punkter ligger i hvilke områder?" ved hjælp af rumlige sammenføjninger i PostGIS.
Vigtigste pointer:
- ST_Contains(polygon, point) — returnerer sandt, når polygonen indeholder punktet fuldstændigt.
- ST_Within(point, polygon) — det omvendte; logisk ækvivalent med ST_Contains.
- ST_Intersects — en mere generel overlapningskontrol, der er indeksvenlig ved punkt-i-polygon.
- GIST-indekser på geometrikolonner er afgørende for ydeevnen i stor skala.
- Kombinér rumlige sammenføjninger med GROUP BY for at tælle eller aggregere punkter pr. region.
- Brug LEFT JOIN til at finde punkter, der ligger uden for alle polygoner.
Rumlige sammenføjninger gør geografisk analyse direkte i SQL mulig — uden behov for et eksternt GIS-værktøj.
Lær SQL med en AI-underviser — gratis
Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.
- Kurser
- 46
- Lektioner
- 183
Ofte stillede spørgsmål
Er lektionen “Rumlige joins og indeholdelse” gratis?
Ja — hele teksten til “Rumlige joins og indeholdelse” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af SQL Academy-kurset, skal du opgradere til CoddyKit PRO. SQL Academy-kurset indeholder 4 lektioner i alt.
Hvad lærer jeg i “Rumlige joins og indeholdelse”?
Hvilke punkter ligger i hvilke områder Du øver dig i SQL Academy med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.
Skal jeg have erfaring for at begynde på SQL Academy?
Der kræves ingen tidligere erfaring. SQL Academy på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 3 af 4.
Hvor lang tid tager lektionen “Rumlige joins og indeholdelse”?
De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.
Kan jeg skrive og køre kode i denne SQL Academy-lektion?
Ja. Alle SQL Academy-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.
Alle lektioner i dette kursus
- Rumlige datatyper
- Afstand og nærmeste naboer
- Rumlige joins og indeholdelse
- Rumlige indeks (GiST)