Viktigt
Översättning är en gemenskapsinsats du kan gå med i. Den här sidan är för närvarande översatt till 100.00%.
16.4. Lektion: Spatiala frågor
Spatiala frågor skiljer sig inte från andra databasfrågor. Du kan använda geometrikolumnen som vilken annan databaskolumn som helst. Med installationen av PostGIS i vår databas har vi ytterligare funktioner för att ställa frågor i vår databas.
Målet för den här lektionen: Att se hur spatiala funktioner implementeras på samma sätt som ”normala” icke-spatiala funktioner.
16.4.1. Spatiala operatorer
Om du vill veta vilka punkter som ligger inom ett avstånd på 2 grader från en punkt (X,Y) kan du göra detta med:
select *
from people
where st_distance(geom,'SRID=4326;POINT(33 -34)') < 2;
Resultat:
id | name | house_no | street_id | phone_no | geom
----+--------------+----------+-----------+---------------+---------------
6 | Fault Towers | 34 | 3 | 072 812 31 28 | 01010008040C0
(1 row)
Observera
geom-värdet ovan förkortades av utrymmesskäl på den här sidan. Om du vill se punkten i läsbara koordinater kan du försöka med något liknande det du gjorde i avsnittet ”Visa en punkt som WKT” ovan.
Hur vet vi att frågan ovan returnerar alla punkter inom 2 * grader *? Varför inte 2 * meter *? Eller någon annan enhet, för den delen?
Svar
De enheter som används i exemplet är grader, eftersom det CRS som skiktet använder är WGS 84. Detta är ett geografiskt CRS, vilket innebär att dess enheter är i grader. Ett projicerat CRS, som UTM-projektionerna, är i meter.
Kom ihåg att när du skriver en fråga måste du veta vilka enheter skiktets CRS finns i. Detta gör att du kan skriva en fråga som ger de resultat du förväntar dig.
16.4.2. Rumsliga index
Vi kan också definiera spatiala index. Ett rumsligt index gör dina rumsliga frågor mycket snabbare. För att skapa ett spatialt index på geometrikolumnen använder du:
CREATE INDEX people_geo_idx
ON people
USING gist
(geom);
\d people
Resultat:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+----------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
geom | geometry |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"people_geo_idx" gist (geom) <-- new spatial key added
"people_name_idx" btree (name)
Check constraints:
"people_geom_point_chk" CHECK (st_geometrytype(geom) = 'ST_Point'::text
OR geom IS NULL)
Foreign-key constraints:
"people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)
16.4.3. Prova själv: ★★☆
Modifiera tabellen cities så att dess geometrikolumn är rumsligt indexerad.
Svar
CREATE INDEX cities_geo_idx ON cities USING gist (geom);
16.4.4. Demo av PostGIS rumsliga funktioner
För att demonstrera PostGIS rumsliga funktioner skapar vi en ny databas som innehåller några (fiktiva) data.
Börja med att skapa en ny databas (avsluta psql-skalet först):
createdb postgis_demo
Kom ihåg att installera postgis-tilläggen:
psql -d postgis_demo -c "CREATE EXTENSION postgis;"
Importera sedan de data som finns i katalogen exercise_data/postgis/. Gå tillbaka till föregående lektion för instruktioner, men kom ihåg att du måste skapa en ny PostGIS-anslutning till den nya databasen. Du kan importera från terminalen eller via DB Manager. Importera filerna till följande databastabeller:
points.shp in i building
lines.shp in i road
polygoner.shp into region
Ladda dessa tre databaslager till QGIS via dialogrutan Add PostGIS Layers, som vanligt. När du öppnar deras attributtabeller kommer du att se att de har både ett id-fält och ett gid-fält som skapats av PostGIS-importen.
Nu när tabellerna är importerade kan vi använda PostGIS för att fråga efter data. Gå tillbaka till din terminal (kommandorad) och ange psql-prompten genom att köra:
psql postgis_demo
Vi kommer att demonstrera några av dessa select statements genom att skapa vyer från dem, så att du kan öppna dem i QGIS och se resultatet.
Välj efter plats
Få tag på alla byggnader i KwaZulu-regionen:
SELECT a.id, a.name, st_astext(a.geom) as point
FROM building a, region b
WHERE st_within(a.geom, b.geom)
AND b.name = 'KwaZulu';
Resultat:
id | name | point
----+------+------------------------------------------
30 | York | POINT(1622345.23785063 6940490.65844485)
33 | York | POINT(1622495.65620524 6940403.87862489)
35 | York | POINT(1622403.09106394 6940212.96302097)
36 | York | POINT(1622287.38463732 6940357.59605424)
40 | York | POINT(1621888.19746548 6940508.01440885)
(5 rows)
Eller om vi skapar en vy från den:
CREATE VIEW vw_select_location AS
SELECT a.gid, a.name, a.geom
FROM building a, region b
WHERE st_within(a.geom, b.geom)
AND b.name = 'KwaZulu';
Lägg till vyn som ett lager och visa den i QGIS:

Välj grannar
Visa en lista över alla namn på regioner som gränsar till Hokkaido-regionen:
SELECT b.name
FROM region a, region b
WHERE st_touches(a.geom, b.geom)
AND a.name = 'Hokkaido';
Resultat:
name
--------------
Missouri
Saskatchewan
Wales
(3 rows)
Som en vy:
CREATE VIEW vw_regions_adjoining_hokkaido AS
SELECT b.gid, b.name, b.geom
FROM region a, region b
WHERE st_touches(a.geom, b.geom)
AND a.name = 'Hokkaido';
I QGIS:

Observera att det saknas en region (Queensland). Detta kan bero på ett topologifel. Artefakter som denna kan varna oss för potentiella problem i data. För att lösa denna gåta utan att fastna i de anomalier som data kan ha kan vi använda en buffertintersect istället:
CREATE VIEW vw_hokkaido_buffer AS
SELECT gid, ST_BUFFER(geom, 100) as geom
FROM region
WHERE name = 'Hokkaido';
Detta skapar en buffert på 100 meter runt regionen Hokkaido.
Det mörkare området är bufferten:

Välj med hjälp av bufferten:
CREATE VIEW vw_hokkaido_buffer_select AS
SELECT b.gid, b.name, b.geom
FROM
(
SELECT * FROM
vw_hokkaido_buffer
) a,
region b
WHERE ST_INTERSECTS(a.geom, b.geom)
AND b.name != 'Hokkaido';
I den här frågan används den ursprungliga buffertvyn som vilken annan tabell som helst. Den får aliaset a, och dess geometrifält, a.geom, används för att välja alla polygoner i tabellen region (alias b) som skär den. Själva Hokkaido är dock utesluten från denna urvalssats, eftersom vi inte vill ha den utan bara de regioner som gränsar till den.
I QGIS:

Det är också möjligt att välja alla objekt inom ett visst avstånd utan att behöva skapa en buffert:
CREATE VIEW vw_hokkaido_distance_select AS
SELECT b.gid, b.name, b.geom
FROM region a, region b
WHERE ST_DISTANCE (a.geom, b.geom) < 100
AND a.name = 'Hokkaido'
AND b.name != 'Hokkaido';
På så sätt uppnås samma resultat, utan att det mellanliggande buffertsteget behöver användas:

Välj unika värden
Visa en lista med unika stadsnamn för alla byggnader i Queensland-regionen:
SELECT DISTINCT a.name
FROM building a, region b
WHERE st_within(a.geom, b.geom)
AND b.name = 'Queensland';
Resultat:
name
---------
Beijing
Berlin
Atlanta
(3 rows)
Ytterligare exempel …
CREATE VIEW vw_shortestline AS
SELECT b.gid AS gid,
ST_ASTEXT(ST_SHORTESTLINE(a.geom, b.geom)) as text,
ST_SHORTESTLINE(a.geom, b.geom) AS geom
FROM road a, building b
WHERE a.id=5 AND b.id=22;
CREATE VIEW vw_longestline AS
SELECT b.gid AS gid,
ST_ASTEXT(ST_LONGESTLINE(a.geom, b.geom)) as text,
ST_LONGESTLINE(a.geom, b.geom) AS geom
FROM road a, building b
WHERE a.id=5 AND b.id=22;
CREATE VIEW vw_road_centroid AS
SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
FROM road a
WHERE a.id = 1;
CREATE VIEW vw_region_centroid AS
SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
FROM region a
WHERE a.name = 'Saskatchewan';
SELECT ST_PERIMETER(a.geom)
FROM region a
WHERE a.name='Queensland';
SELECT ST_AREA(a.geom)
FROM region a
WHERE a.name='Queensland';
CREATE VIEW vw_simplify AS
SELECT gid, ST_Simplify(geom, 20) AS geom
FROM road;
CREATE VIEW vw_simplify_more AS
SELECT gid, ST_Simplify(geom, 50) AS geom
FROM road;
CREATE VIEW vw_convex_hull AS
SELECT
ROW_NUMBER() over (order by a.name) as id,
a.name as town,
ST_CONVEXHULL(ST_COLLECT(a.geom)) AS geom
FROM building a
GROUP BY a.name;
16.4.5. Sammanfattningsvis
Du har sett hur man ställer frågor till rumsliga objekt med hjälp av de nya databasfunktionerna från PostGIS.
16.4.6. Vad händer härnäst?
Nu ska vi undersöka strukturerna i mer komplexa geometrier och hur man skapar dem med hjälp av PostGIS.