16.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.
16.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?
16.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)
16.4.3. Try Yourself 
¶
Pas de tabel cities zo aan dat zijn kolom voor geometrie ruimtelijk wordt geïndexeerd.
16.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.
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:
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:
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:
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:
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:
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)
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;
16.4.5. In Conclusion¶
U heeft gezien hoe u ruimtelijke objecten kunt bevragen met behulp van de nieuwe databasefuncties van PostGIS.
16.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.