Spatiala joiner och inneslutning
Vilka punkter ligger i vilka områden.
Spatiala joiner och inneslutning är en gratis lektion i SQL Academy på CoddyKit. Detta är lektion 3 av 4. Ni kan läsa hela lektionen gratis nedan och sedan öva praktiskt i webbläsaren med en inbyggd kodredigerare och en AI-handledare som är tillgänglig dygnet runt. Den ingår i lärvägen för SQL Academy, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i SQL Academy innehåller totalt 4 lektioner.
Vad är en spatial join
En spatial join kombinerar två tabeller utifrån ett geografiskt samband i stället för en matchande nyckel. I stället för att fråga "är detta ID lika med det där ID:t?" ställer ni frågor som "faller den här punkten inuti den där polygonen?" eller "överlappar de här två formerna?"
PostGIS utökar PostgreSQL med geometrityper och spatiala funktioner som gör dessa join-operationer möjliga. Resultatet är detsamma som vid en vanlig SQL JOIN — rader från båda tabellerna kombineras — men villkoret är geometriskt.
Konfigurera exempeltabeller
Vi skapar två tabeller att arbeta med: cities som innehåller punktpositioner och countries som innehåller polygoners gränser. Båda använder typen GEOMETRY från PostGIS med SRID 4326 (standardiserade WGS84-koordinater för latitud och longitud).
SRID (Spatial Reference ID) anger vilket koordinatsystem PostGIS ska använda. SRID 4326 är det vanligaste för 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)
);Infoga exempeldata
Vi använder ST_GeomFromText för att infoga geometrivärden i formatet Well-Known Text (WKT). WKT är en standardiserad textrepresentation av geometrier: POINT(lon lat) för punkter och POLYGON((...)) för polygoner.
Observera att longitud kommer före latitud i WKT — detta följer konventionen X, Y inom matematiken.
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: Kärnfunktionen
ST_Contains(geometry A, geometry B) returnerar TRUE när geometri A helt innehåller geometri B. I vårt fall returnerar ST_Contains(country.border, city.location) sant när en stadspunkt ligger inuti en landpolygon.
Detta är kärnan i joinningar baserade på inneslutning i PostGIS. Funktionen ingår i OGC-standarden och fungerar med alla kombinationer 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 omvända perspektivet
ST_Within(geometry A, geometry B) är den exakta motsatsen till ST_Contains: den returnerar TRUE när geometri A ligger helt inuti geometri B. ST_Within(city, country) är logiskt identisk med ST_Contains(country, city).
Båda funktionerna ger samma resultat här. Valet mellan dem handlar om läsbarhet — välj den som blir mest naturlig i er fråga.
-- 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 för att hitta omatchade punkter
En vanlig JOIN tar bort städer som inte ligger inuti någon landpolygon. Använd en LEFT JOIN tillsammans med kontrollen WHERE ... IS NULL för att hitta punkter som inte har någon omslutande polygon — användbart för att upptäcka problem med datakvaliteten eller punkter utanför ert täckningsområ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;Räkna punkter per polygon
Spatiala joinningar blir särskilt användbara tillsammans med aggregering. Genom att joina städer med länder och sedan gruppera efter land kan ni räkna hur många punkter som ligger inuti varje polygon. Detta mönster är mycket vanligt inom geografisk analys — för att räkna butiker per region, händelser per distrikt, sensorer per zon och så vidare.
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;Använda ett spatialt index för bättre prestanda
Spatiala joinningar kan vara långsamma på stora datamängder eftersom varje punkt testas mot varje polygon. Ett GIST-index (Generalized Search Tree) låter PostGIS använda ett förfilter baserat på bounding boxes för att hoppa över de flesta jämförelser innan det exakta testet med ST_Contains körs.
Skapa alltid ett GIST-index på geometrikolumner som ni joinar eller filtrerar efter. Frågeplaneraren använder det automatiskt.
-- 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: Överlappande geometrier
ST_Intersects(A, B) returnerar TRUE om två geometrier har någon gemensam punkt — även om de bara vidrör varandra vid en gräns. Den är mindre strikt än ST_Contains: två överlappande polygoner skär varandra även om ingen av dem helt innehåller den andra.
För tester av punkter i polygoner är ST_Intersects och ST_Contains likvärdiga, men ST_Intersects använder GIST-indexet effektivare och föredras ofta i praktiken.
-- 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);Joina punkter med den närmaste polygonen
När en punkt ligger exakt på en gräns eller mycket nära flera polygoner kan ni vilja ha den närmaste polygonen i stället för alla träffar. ST_Distance mäter avståndet mellan två geometrier, vilket möjliggör mönster med ORDER BY + LIMIT eller den specialiserade LATERAL-joinningen med ORDER BY ... LIMIT 1.
Detta kallas en nearest-neighbor-join och är vanligt när GPS-spår kopplas till vägsegment eller när en punkt tilldelas den närmaste 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;Praktiskt exempel: Restauranger i distrikt
Här är ett realistiskt exempel från början till slut: hitta vilket stadsdistrikt varje restaurang tillhör och räkna sedan restauranger per distrikt. Detta mönster gäller för alla scenarier där punkter ska kopplas till polygoner — uttagsautomater i stadsdelar, olyckor i valdistrikt och beställningar i leveranszoner.
Frågan använder ST_Within i en spatial join och grupperar resultaten för en sammanfattande 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;Snabbkontroll
Testa er förståelse av spatiala joinningar och inneslutning i PostGIS.
Sammanfattning: Spatiala joinningar och inneslutning
I den här lektionen har ni lärt er att besvara frågan "vilka punkter ligger i vilka områden?" med hjälp av spatiala joinningar i PostGIS.
Viktiga slutsatser:
- ST_Contains(polygon, point) — returnerar sant när polygonen helt innehåller punkten.
- ST_Within(point, polygon) — det omvända; logiskt likvärdig med ST_Contains.
- ST_Intersects — ett mer generellt test av överlappning; indexvänligt för punkter i polygoner.
- GIST-index på geometrikolumner är avgörande för prestandan i stor skala.
- Kombinera spatiala joinningar med GROUP BY för att räkna eller aggregera punkter per region.
- Använd LEFT JOIN för att upptäcka punkter som ligger utanför alla polygoner.
Spatiala joinningar möjliggör geografisk analys direkt i SQL — utan behov av något externt GIS-verktyg.
Lär dig SQL med en AI-lärare – gratis
Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.
- Kurser
- 46
- Lektioner
- 183
Vanliga frågor
Är lektionen ”Spatiala joiner och inneslutning” gratis?
Ja – hela texten till ”Spatiala joiner och inneslutning” kan läsas gratis här på webben. Om Ni vill öva interaktivt med en inbyggd kodredigerare och en AI-handledare som är tillgänglig dygnet runt och låsa upp resten av kursen i SQL Academy, kan Ni uppgradera till CoddyKit PRO. Kursen i SQL Academy innehåller totalt 4 lektioner.
Vad lär jag mig i ”Spatiala joiner och inneslutning”?
Vilka punkter ligger i vilka områden. Ni övar på SQL Academy med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.
Behöver jag någon erfarenhet för att börja lära mig SQL Academy?
Du behöver inga förkunskaper. Utbildningen i SQL Academy på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 3 av 4.
Hur lång tid tar lektionen ”Spatiala joiner och inneslutning”?
De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.
Kan jag skriva och köra kod i den här SQL Academy-lektionen?
Ja. Varje SQL Academy-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.
Alla lektioner i den här kursen
- Spatiala datatyper
- Avstånd och närmaste grannar
- Spatiala joiner och inneslutning
- Spatiala index (GiST)