Chapter 5. Spatiala frågor

Table of Contents

Syftet med spatiala databaser är att kunna utföra sökningar i databasen som normalt kräver GIS-funktioner på skrivbordet. För att PostGIS ska kunna användas effektivt måste man veta vilka spatiala funktioner som finns tillgängliga, hur de ska användas i frågor och se till att lämpliga index finns på plats för att ge bra prestanda.

5.1. Fastställande av spatiala relationer

Spatiala relationer anger hur två geometrier interagerar med varandra. De är en grundläggande egenskap för att ställa frågor om geometri.

5.1.1. Dimensionsutvidgad 9-intersektionell modell

Enligt OpenGIS Simple Features Implementation Specification for SQL är "det grundläggande tillvägagångssättet för att jämföra två geometrier att göra parvisa tester av skärningarna mellan de två geometriernas inre, yttre och yttre delar och att klassificera förhållandet mellan de två geometrierna baserat på posterna i den resulterande 'skärningsmatrisen'"

I teorin om punktuppsättningstopologi kategoriseras punkterna i en geometri som är inbäddad i ett 2-dimensionellt rum i tre uppsättningar:

Begränsning

Gränsen för en geometri är uppsättningen av geometrier med nästa lägre dimension. För POINTs, som har dimensionen 0, är gränsen den tomma mängden. Gränsen för en LINESTRING är de två ändpunkterna. För POLYGONERär gränsen linjeverket för de yttre och inre ringarna.

Inredning

Det inre av en geometri är de punkter i en geometri som inte ligger i begränsningen. För POINTsär det inre själva punkten. Det inre av en LINESTRING är den uppsättning punkter som finns mellan ändpunkterna. För POLYGONERär det inre den areella ytan inuti polygonen.

Exteriör

En geometris utsida är resten av det rum i vilket geometrin är inbäddad; med andra ord alla punkter som inte ligger i geometrins inre eller på dess gräns. Det är en 2-dimensionell icke sluten yta.

DE-9IM ( Dimensionally Extended 9-Intersection Model ) beskriver det spatiala förhållandet mellan två geometrier genom att ange dimensionerna för de 9 skärningspunkterna mellan ovanstående uppsättningar för varje geometri. Dimensionerna på skärningspunkterna kan formellt representeras i en 3x3 skärningsmatris.

För en geometri g betecknas det inre, det yttre och det yttre med beteckningarna I(g), B(g) och E(g). Dim(s) betecknar också dimensionen hos en mängd s med domänen {0,1,2,F}:

  • 0 => punkt

  • 1 => linje

  • 2 => område

  • F => tom uppsättning

Med hjälp av denna notation är intersektionsmatrisen för två geometrier a och b: är:

  Inredning Begränsning Exteriör
Inredning dim( I(a) ∩ I(b) ) dim( I(a) ∩ B(b) ) dim( I(a) ∩ E(b) )
Begränsning dim( B(a) ∩ I(b) ) dim( B(a) ∩ B(b) ) dim( B(a) ∩ E(b) )
Exteriör dim( E(a) ∩ I(b) ) dim( E(a) ∩ B(b) ) dim( E(a) ∩ E(b) )

Visuellt, för två överlappande polygonala geometrier, ser detta ut så här:

 
  Inredning Begränsning Exteriör
Inredning

dim( I(a) ∩ I(b) ) = 2

dim( I(a) ∩ B(b) = 1

dim( I(a) ∩ E(b) ) = 2

Begränsning

dim( B(a) ∩ I(b) ) = 1

dim( B(a) ∩ B(b) ) = 0

dim( B(a) ∩ E(b) ) = 1

Exteriör

dim( E(a) ∩ I(b) ) = 2

dim( E(a) ∩ B(b) ) = 1

dim( E(a) ∩ E(b) = 2

När man läser från vänster till höger och uppifrån och ner representeras skärningsmatrisen av textsträngen '"212101212".'.

För mer information, se:

5.1.2. Namngivna spatiala relationer

För att göra det enkelt att fastställa vanliga spatiala relationer definierar OGC SFS en uppsättning namngivna predikat för spatiala relationer. PostGIS tillhandahåller dessa som funktionerna ST_Contains, ST_Crosses, ST_Disjoint, ST_Equals, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within. Det definierar också de icke-standardiserade relationspredikaten ST_Covers, ST_CoveredBy, och ST_ContainsProperly.

Spatiala predikat används vanligtvis som villkor i SQL WHERE- eller JOIN-klausuler. De namngivna spatiala predikaten använder automatiskt ett spatialt index om ett sådant finns tillgängligt, så det finns inget behov av att använda bounding box-operatorn && också. Till exempel:

SELECT city.name, state.name, city.geom
FROM city JOIN state ON ST_Intersects(city.geom, state.geom);

För mer information och illustrationer, se PostGIS Workshop.

5.1.3. Allmänna spatiala relationer

I vissa fall är de angivna spatiala relationerna otillräckliga för att ge ett önskat spatialt filtertillstånd.

Tänk till exempel på en linjär dataset som representerar ett vägnät. Det kan vara nödvändigt att identifiera alla vägsegment som korsar varandra, inte vid en punkt utan i en linje (kanske för att validera någon affärsregel). I det här fallet tillhandahåller ST_Crosses inte det nödvändiga spatiala filtret, eftersom det för linjära funktioner endast returnerar sant där de korsas vid en punkt.

En tvåstegslösning skulle vara att först beräkna den faktiska skärningspunkten (ST_Intersection) för par av väglinjer som spatialt skär varandra (ST_Intersects), och sedan kontrollera om skärningspunktens ST_GeometryType är"LINESTRING" (för att på ett korrekt sätt hantera fall som ger GEOMETRYCOLLECTIONsav [MULTI]POINTs, [ MULTI]LINESTRINGs, etc.).

Det är uppenbart att en enklare och snabbare lösning är önskvärd.

Ett annat exempel är lokalisering av kajer som skär en sjös gräns på en linje och där ena änden av kajen är uppe på stranden. Med andra ord, där en kaj ligger inom men inte helt innesluten av en sjö, skär sjöns gräns på en linje och där exakt en av kajens ändpunkter ligger inom eller på sjöns gräns. Det är möjligt att använda en kombination av spatiala predikat för att hitta de funktioner som krävs:

Dessa krav kan uppfyllas genom att beräkna den fullständiga DE-9IM-intersektionsmatrisen. PostGIS tillhandahåller funktionen ST_Relate för att göra detta:

SELECT ST_Relate( 'LINESTRING (1 1, 5 5)',
                  'POLYGON ((3 3, 3 7, 7 7, 7 3, 3 3))' );
st_relate
-----------
1010F0212

För att testa ett visst spatialt förhållande används ett intersektionsmatrismönster. Detta är matrisrepresentationen utökad med tilläggssymbolerna {T,*}:

  • T => intersection dimension is non-empty; i.e. is in {0,1,2}

  • * => bryr sig inte

Med hjälp av intersektionsmatrismönster kan specifika spatiala relationer utvärderas på ett mer kortfattat sätt. Funktionerna ST_Relate och ST_RelateMatch kan användas för att testa intersektionsmatrismönster. I det första exemplet ovan är det matrismönster som anger att två linjer korsar varandra i en linje"1*1***1**":

-- Find road segments that intersect in a line
SELECT a.id
FROM roads a, roads b
WHERE a.id != b.id
      AND a.geom && b.geom
      AND ST_Relate(a.geom, b.geom, '1*1***1**');

I det andra exemplet är mönstret för skärningsmatrisen som anger en linje som delvis ligger innanför och delvis utanför en polygon"102101FF2":

-- Find wharves partly on a lake's shoreline
SELECT a.lake_id, b.wharf_id
FROM lakes a, wharfs b
WHERE a.geom && b.geom
      AND ST_Relate(a.geom, b.geom, '102101FF2');

5.2. Använda spatiala index

När du konstruerar frågor med spatiala villkor är det viktigt att se till att ett spatialt index används, om ett sådant finns (se Section 4.9, “Spatiala index”), för bästa prestanda. För att göra detta måste en spatial operator eller en indexmedveten funktion användas i en WHERE- eller ON-sats i frågan.

Spatiala operatorer inkluderar bounding box-operatorer (av vilka den vanligaste är &&; se Section 7.10.1, “Operatorer för avgränsande box” för en fullständig lista) och avståndsoperatorer som används i frågor om närmaste granne (den vanligaste är <->; se Section 7.10.2, “Avståndsoperatorer” för en fullständig lista)

Indexmedvetna funktioner lägger automatiskt till en bounding box-operator till det spatiala villkoret. Indexmedvetna funktioner inkluderar de namngivna spatiala relationspredikaten ST_Contains, ST_ContainsProperly, ST_CoveredBy, ST_Covers, ST_Crosses, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within, ST_Within, och ST_3DIntersects, och avståndspredikaten ST_DWithin, ST_DFullyWithin, ST_3DDFullyWithin, och ST_3DDWithin.)

Funktioner som ST_Distance använder inte index för att optimera sin drift. Till exempel skulle följande fråga vara ganska långsam på en stor tabell:

SELECT geom
FROM geom_table
WHERE ST_Distance( geom, 'SRID=312;POINT(100000 200000)' ) < 100

Denna fråga väljer ut alla geometrier i geom_table som ligger inom 100 enheter från punkten (100000, 200000). Den kommer att vara långsam eftersom den beräknar avståndet mellan varje punkt i tabellen och den angivna punkten, dvs. en ST_Distance() -beräkning görs för varje rad i tabellen.

Antalet rader som bearbetas kan minskas avsevärt genom att använda den indexmedvetna funktionen ST_DWithin:

SELECT geom
FROM geom_table
WHERE ST_DWithin( geom, 'SRID=312;POINT(100000 200000)', 100 )

Denna fråga väljer samma geometrier, men den gör det på ett effektivare sätt. Detta möjliggörs genom att ST_DWithin() använder &&-operatorn internt på en utökad begränsningsbox för frågegegeometrin. Om det finns ett spatialt index på geom kommer frågeplaneraren att inse att den kan använda indexet för att minska antalet rader som skannas innan avståndet beräknas. Det spatiala indexet gör det möjligt att endast hämta poster med geometrier vars avgränsande rutor överlappar den expanderade omfattningen och som därför kan ligga inom det avstånd som krävs. Det faktiska avståndet beräknas sedan för att bekräfta om posten ska inkluderas i resultatuppsättningen.

För mer information och exempel, se PostGIS Workshop.

5.3. Exempel på Spatial SQL

I exemplen i detta avsnitt används en tabell med linjära vägar och en tabell med polygonala kommungränser. Definitionen av tabellen bc_roads är:

Column    | Type              | Description
----------+-------------------+-------------------
gid       | integer           | Unique ID
name      | character varying | Road Name
geom      | geometry          | Location Geometry (Linestring)

Definitionen av tabellen bc_municipality är:

Column   | Type              | Description
---------+-------------------+-------------------
gid      | integer           | Unique ID
code     | integer           | Unique ID
name     | character varying | City / Town Name
geom     | geometry          | Location Geometry (Polygon)

5.3.1.

Vad är den totala längden på alla vägar, uttryckt i kilometer?

Du kan besvara den här frågan med ett mycket enkelt SQL-test:

SELECT sum(ST_Length(geom))/1000 AS km_roads FROM bc_roads;

km_roads
------------------
70842.1243039643

5.3.2.

Hur stor är staden Prince George, räknat i hektar?

Denna fråga kombinerar ett attributvillkor (på kommunens namn) med en spatial beräkning (av polygonområdet):

SELECT
  ST_Area(geom)/10000 AS hectares
FROM bc_municipality
WHERE name = 'PRINCE GEORGE';

hectares
------------------
32657.9103824927

5.3.3.

Vilken är den största kommunen i provinsen, sett till ytan?

Denna fråga använder en spatial mätning som ett ordningsvärde. Det finns flera sätt att närma sig detta problem, men det mest effektiva är nedan:

SELECT
  name,
  ST_Area(geom)/10000 AS hectares
FROM bc_municipality
ORDER BY hectares DESC
LIMIT 1;

name           | hectares
---------------+-----------------
TUMBLER RIDGE  | 155020.02556131

Observera att vi måste beräkna arean för varje polygon för att kunna besvara den här frågan. Om vi gjorde det här mycket skulle det vara vettigt att lägga till en områdeskolumn i tabellen som kan indexeras för prestanda. Genom att beställa resultaten i fallande riktning och använda PostgreSQL-kommandot "LIMIT" kan vi enkelt välja bara det största värdet utan att använda en aggregerad funktion som MAX ().

5.3.4.

Hur långa är de vägar som helt och hållet ingår i varje kommun?

Detta är ett exempel på en "spatial join", som sammanför data från två tabeller (med en join) med hjälp av en spatial interaktion ("contained") som join-villkor (i stället för den vanliga relationella metoden med join på en gemensam nyckel):

SELECT
  m.name,
  sum(ST_Length(r.geom))/1000 as roads_km
FROM bc_roads AS r
JOIN bc_municipality AS m
  ON ST_Contains(m.geom, r.geom)
GROUP BY m.name
ORDER BY roads_km;

name                        | roads_km
----------------------------+------------------
SURREY                      | 1539.47553551242
VANCOUVER                   | 1450.33093486576
LANGLEY DISTRICT            | 833.793392535662
BURNABY                     | 773.769091404338
PRINCE GEORGE               | 694.37554369147
...

Den här frågan tar ett tag, eftersom varje väg i tabellen sammanfattas i slutresultatet (ca 250 000 vägar för exempeltabellen). För mindre dataset (flera tusen poster på flera hundra) kan svaret vara mycket snabbt.

5.3.5.

Skapa en ny tabell med alla vägar inom staden Prince George.

Detta är ett exempel på en "overlay", som tar in två tabeller och matar ut en ny tabell som består av spatialt klippta eller skurna resultat. Till skillnad från den "spatial join" som demonstreras ovan skapar den här frågan nya geometrier. En overlay är som en turboladdad spatial join och är användbar för mer exakt analysarbete:

CREATE TABLE pg_roads as
SELECT
  ST_Intersection(r.geom, m.geom) AS intersection_geom,
  ST_Length(r.geom) AS rd_orig_length,
  r.*
FROM bc_roads AS r
JOIN bc_municipality AS m
  ON ST_Intersects(r.geom, m.geom)
WHERE
  m.name = 'PRINCE GEORGE';

5.3.6.

Hur lång är "Douglas St" i Victoria i kilometer?

SELECT
  sum(ST_Length(r.geom))/1000 AS kilometers
FROM bc_roads r
JOIN bc_municipality m
  ON ST_Intersects(m.geom, r.geom
WHERE
  r.name = 'Douglas St'
  AND m.name = 'VICTORIA';

kilometers
------------------
4.89151904172838

5.3.7.

Vilken är den största kommunpolygonen som har ett hål?

SELECT gid, name, ST_Area(geom) AS area
FROM bc_municipality
WHERE ST_NRings(geom) 
> 1
ORDER BY area DESC LIMIT 1;

gid  | name         | area
-----+--------------+------------------
12   | SPALLUMCHEEN | 257374619.430216