16.4. Lesson: Interogări Spaţiale
Interogările spațiale nu sunt diferite de alte interogări de baze de date. Puteți utiliza coloana de geometrie la fel ca pe orice altă coloană de baze de date. O dată cu instalarea PostGIS în baza noastră de date, avem la dispoziție funcții suplimentare pentru a interoga baza de date.
Scopul acestei lecții: De a afla cum sunt implementate funcțiile spațiale similare cu funcțiile non-spațiale „normale”.
16.4.1. Operatori Spaţiali
Când doriți să știți care puncte se află la o distanță de 2 grade față de un punct (X,Y), puteți proceda astfel cu:
select *
from people
where st_distance(the_geom,'SRID=4326;POINT(33 -34)') < 2;
Rezultat:
id | name | house_no | street_id | phone_no | the_geom
----+--------------+----------+-----------+---------------+---------------
6 | Fault Towers | 34 | 3 | 072 812 31 28 | 01010008040C0
(1 row)
Notă
Valoarea the_geom de mai sus a fost trunchiată datorită spațierii de pe această pagină. În cazul în care doriți să vedeți punctul în coordonate clare, încercați ceva similar cu ceea ce ați efectuat în secțiunea „Vizualizează un punct sub forma WKT”, de mai sus.
De unde știm că interogarea de mai sus returnează toate punctele incluse în cadrul a 2 grade? De ce nu 2 metri? Sau oricare altă unitate?
16.4.2. Indecși Spațiali
We also can define spatial indexes. A spatial index makes your spatial queries much faster. To create a spatial index on the geometry column use:
CREATE INDEX people_geo_idx
ON people
USING gist
(the_geom);
\d people
Rezultat:
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 
Modificați tabelul orașelor, astfel încât coloana de geometrie să fie indexată spațial.
16.4.4. Demo Funcții Spațiale PostGIS
În scopul demonstrării funcțiilor spațiale PostGIS , vom crea o nouă bază de date care conține câteva date (fictive).
To start, create a new database (exit the psql shell first):
createdb postgis_demo
Remember to install the postgis extensions:
psql -d postgis_demo -c "CREATE EXTENSION postgis;"
Next, import the data provided in the exercise_data/postgis/ directory. Refer back to the previous lesson for instructions, but remember that you’ll need to create a new PostGIS connection to the new database. You can import from the terminal or via DB Manager. Import the files into the following database tables:
points.shp în building
lines.shp în road
polygons.shp în region
Încărcați aceste trei straturi ale bazei de date în QGIS ca de obicei, prin intermediul Adăugării Straturilor PostGIS. Atunci când deschideți tabelele lor cu atribute, veți observa că ambele dețin atât un câmp id cât și unul gid, create în urma importului PostGIS.
Acum, că tabelele sunt importate, putem folosi PostGIS pentru a interoga datele. Mergeți înapoi în ferestra terminalului (linia de comandă) și introduceți promptul psql astfel:
psql postgis_demo
Vom demonstra unele dintre aceste expresii de selectare prin crearea unor vederi, pentru a le deschide apoi în QGIS și pentru a le observa rezultatele.
Selectare după locație
Get all the buildings in the KwaZulu region:
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';
Rezultat:
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)
Or, if we create a view from it:
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';
Adăugați vederea sub formă de strat, apoi vizualizați-o în QGIS:
Selectați vecinii
Show a list of all the names of regions adjoining the Hokkaido region:
SELECT b.name
FROM region a, region b
WHERE st_touches(a.the_geom, b.the_geom)
AND a.name = 'Hokkaido';
Rezultat:
name
--------------
Missouri
Saskatchewan
Wales
(3 rows)
As a 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';
În QGIS:
Note the missing region (Queensland). This may be due to a topology error. Artifacts such as this can alert us to potential problems in the data. To solve this enigma without getting caught up in the anomalies the data may have, we could use a buffer intersect instead:
CREATE VIEW vw_hokkaido_buffer AS
SELECT gid, ST_BUFFER(the_geom, 100) as the_geom
FROM region
WHERE name = 'Hokkaido';
Aceasta va crea o zonă tampon de 100 de metri în jurul regiunii Hokkaido.
Zona mai închisă este tamponul:
Select using the 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';
În această interogare, vizualizarea originală a tamponului se face similar oricărui alt tabel. Acesta primește aliasul a iar câmpul de geometrie a.the_geom`este folosit la selectarea oricărui poligon din tabela :kbd:`region (alias b) cu care se intersectează. Totuși, Hokkaido este exclusă din această expresie de selectare, nefiind dorită; vrem să obținem doar regiunile din vecinătate.
În QGIS:
It is also possible to select all objects within a given distance, without the extra step of creating a buffer:
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';
Prin aceasta se obține același rezultat, fără a fi necesar pasul tamponului intermediar:
Selectați valorile unice
Show a list of unique town names for all buildings in the Queensland region:
SELECT DISTINCT a.name
FROM building a, region b
WHERE st_within(a.the_geom, b.the_geom)
AND b.name = 'Queensland';
Rezultat:
name
---------
Beijing
Berlin
Atlanta
(3 rows)
Exemple suplimentare …
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
Ați văzut cum se pot interoga obiectele spațiale, cu ajutorul noilor funcții de bază de date din PostGIS.
16.4.6. What’s Next?
Mai departe vom investiga structurile geometriilor complexe și cum să le creați cu ajutorul PostGIS.