17.4. Lesson: Ruimtelijke query’s

Ruimtelijke query’s zijn niet anders dan andere query’s voor databases. U kunt de kolom voor geometrie net zo gebruiken als elke andere kolom in een database. Met de installatie van PostGIS in onze database, hebben we aanvullende functies om onze database te bevragen.

het doel voor deze les: Zien hoe ruimtelijke functies worden geïmplementeerd, soortgelijk aan “normale” niet ruimtelijke functies.

17.4.1. Ruimtelijke operatoren

Wanneer u wilt weten welke punten binnen een afstand van 2 graden vanaf een punt(X,Y) liggen, kunt dat doen met:

select *
from people
where st_distance(the_geom,'SRID=4326;POINT(33 -34)') < 2;

Resultaat:

 id |     name     | house_no | street_id |   phone_no    |   the_geom
----+--------------+----------+-----------+---------------+---------------
  6 | Fault Towers |       34 |         3 | 072 812 31 28 | 01010008040C0
(1 row)

Notitie

waarde the_geom hierboven werd afgebroken voor ruimte op deze pagina. Als u het punt in voor mensen leesbare coördinaten wilt zien, probeer dan iets soortgelijks als wat u eerder deed in het gedeelte “Bekijk een punt als WKT”.

Hoe weten we dat de query hierboven alle punten binnen 2 graden teruggeeft? Waarom geen 2 meter? Of elke andere maateenheid, nu we het er toch over hebben?

Controleer uw resultaten

17.4.2. Ruimtelijke indexen

We kunnen ook ruimtelijke indexen definiëren. Een ruimtelijke index maakt uw ruimtelijke query’s veel sneller. Gebruik, om een ruimtelijke index op de kolom voor geometrie te maken:

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (the_geom);

\d people

Resultaat:

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     |
  the_geom  | geometry              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (the_geom)  <-- new spatial key added
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(the_geom) = 'ST_Point'::text
  OR the_geom IS NULL)
Foreign-key constraints:
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

17.4.3. Try Yourself moderate

Pas de tabel cities zo aan dat zijn kolom voor geometrie ruimtelijk wordt geïndexeerd.

Controleer uw resultaten

17.4.4. Demonstratie ruimtelijke functies van PostGIS

We zullen een nieuwe database maken die enkele (fictionele) gegevens bevat om de ruimtelijke functies van PostGIS te demonstreren.

Maak, om te beginnen, eerst een nieuwe database (verlaat eerst de shell van psql):

createdb postgis_demo

Vergeet niet de extensies voor PostGIS te installeren:

psql -d postgis_demo -c "CREATE EXTENSION postgis;"

Importeer vervolgens de gegevens die worden verschaft in de map exercise_data/postgis/. Bekijk de vorige les voor de instructies, maar onthoud dat u een nieuwe PostGIS verbinding moet maken naar de nieuwe database. U kunt de gegevens importeren vanuit de terminal of via SPIT. Importeer de bestanden in de volgende tabellen van de database:

  • points.shp in building

  • lines.shp in road

  • polygons.shp in region

Laad deze drie lagen van de database in QGIS via het dialoogvenster PostGIS-laag toevoegen, zoals gewoonlijk. Wanneer u hun attributentabellen opent, zult u merken dat zij alle een veld id en een veld gid hebben, gemaakt door het importeren met PostGIS.

Nu de tabellen zijn geïmporteerd, kunnen we PostGIS gebruiken om de gegevens te bevragen. Ga terug naar uw terminal (opdrachtregel) en voer bij de prompt van psql in:

psql postgis_demo

We zullen enkele van deze argumenten SELECT demonstreren door er weergaven van te maken, zodat u ze in QGIS kunt openen en hun resultaten kunt zien.

17.4.4.1. Op locatie selecteren

Alle gebouwen in de regio KwaZulu verkrijgen:

SELECT a.id, a.name, st_astext(a.the_geom) as point
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_geom)
    AND b.name = 'KwaZulu';

Resultaat:

 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)

Of, als we er een view van maken:

CREATE VIEW vw_select_location AS
  SELECT a.gid, a.name, a.the_geom
    FROM building a, region b
      WHERE st_within(a.the_geom, b.the_geom)
      AND b.name = 'KwaZulu';

Voeg de view als een laag toe en bekijk deze in QGIS:

../../../_images/kwazulu_view_result.png

17.4.4.2. Buren selecteren

Geef een lijst weer van de namen van alle regio’s die naast de regio Hokkaido liggen:

SELECT b.name
  FROM region a, region b
    WHERE st_touches(a.the_geom, b.the_geom)
    AND a.name = 'Hokkaido';

Resultaat:

    name
--------------
 Missouri
 Saskatchewan
 Wales
(3 rows)

Als een view:

CREATE VIEW vw_regions_adjoining_hokkaido AS
  SELECT b.gid, b.name, b.the_geom
    FROM region a, region b
      WHERE TOUCHES(a.the_geom, b.the_geom)
      AND a.name = 'Hokkaido';

In QGIS:

../../../_images/adjoining_result.png

Merk de ontbrekende regio op (Queensland). Dit zou kunnen komen door een fout in de topologie. Artefacten zoals dit kunnen ons waarschuwen voor potentiële problemen in de gegevens. Om dit enigma op te lossen zonder verstrikt te raken in de afwijkingen in de gegevens die de gegevens zouden kunnen hebben, zouden we in plaats daarvan een ‘buffer intersect’ kunnen gebruiken:

CREATE VIEW vw_hokkaido_buffer AS
  SELECT gid, ST_BUFFER(the_geom, 100) as the_geom
    FROM region
      WHERE name = 'Hokkaido';

Dit maakt een buffer van 100 meter rondom de regio Hokkaido.

Het donkere gebied is de buffer:

../../../_images/hokkaido_buffer.png

Selecteer met behulp van de buffer:

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.the_geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.the_geom, b.the_geom)
    AND b.name != 'Hokkaido';

In deze query wordt de originele view van de buffer gebruikt zoals elke andere tabel gebruikt zou worden. Het is het alias a gegeven en het veld voor de geometrie ervan, a.the_geom, wordt gebruik om elke polygoon in de tabel region (alias b) te selecteren waarmee deze kruist. Echter, Hokkaido zelf wordt uitgesloten van dit argument SELECT, omdat we die niet willen; we willen alleen de regio’s die er naast liggen.

In QGIS:

../../../_images/hokkaido_buffer_select.png

Het is ook mogelijk alle objecten binnen een bepaalde afstand te selecteren, zonder de extra stap van te maken door eerst een buffer te creëren:

CREATE VIEW vw_hokkaido_distance_select AS
  SELECT b.gid, b.name, b.the_geom
    FROM region a, region b
      WHERE ST_DISTANCE (a.the_geom, b.the_geom) < 100
      AND a.name = 'Hokkaido'
      AND b.name != 'Hokkaido';

Dit levert hetzelfde resultaat op, zonder de noodzaak van een tussenliggende stap voor de buffer:

../../../_images/hokkaido_distance_select.png

17.4.4.3. Unieke waarden selecteren

Toon een lijst van alle unieke namen van steden voor alle gebouwen in de regio Queensland:

SELECT DISTINCT a.name
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_geom)
    AND b.name = 'Queensland';

Resultaat:

  name
---------
 Beijing
 Berlin
 Atlanta
(3 rows)

17.4.4.4. Meer voorbeelden …

CREATE VIEW vw_shortestline AS
  SELECT b.gid AS gid,
        ST_ASTEXT(ST_SHORTESTLINE(a.the_geom, b.the_geom)) as text,
        ST_SHORTESTLINE(a.the_geom, b.the_geom) AS the_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.the_geom, b.the_geom)) as text,
         ST_LONGESTLINE(a.the_geom, b.the_geom) AS the_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.the_geom) as the_geom
    FROM road a
      WHERE a.id = 1;

CREATE VIEW vw_region_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.the_geom) as the_geom
    FROM region a
      WHERE a.name = 'Saskatchewan';

SELECT ST_PERIMETER(a.the_geom)
  FROM region a
    WHERE a.name='Queensland';

SELECT ST_AREA(a.the_geom)
  FROM region a
    WHERE a.name='Queensland';

CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(the_geom, 20) AS the_geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(the_geom, 50) AS the_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.the_geom)) AS the_geom
    FROM building a
    GROUP BY a.name;

17.4.5. In Conclusion

U heeft gezien hoe u ruimtelijke objecten kunt bevragen met behulp van de nieuwe databasefuncties van PostGIS.

17.4.6. What’s Next?

Vervolgens gaan we de structuren van meer complexe geometrieën onderzoeken en hoe ze te maken met behulp van PostGIS.