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 lagret 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 lagrets 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. Spatiala index

Vi kan också definiera spatiala index. Ett spatialt index gör dina spatiala 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 spatialt indexerad.

Svar

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (geom);

16.4.4. Demo av PostGIS spatiala funktioner

För att demonstrera PostGIS spatiala 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 spatiala 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.