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)

Note

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?

Verificați-vă rezultatele

16.4.2. Indecși Spațiali

De asemenea, putem defini indecși spațiali. Un index spațial accelerează mult interogările spațiale. Pentru a crea un index spațial pe coloana de geometrie folosiți:

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 moderate

Modificați tabelul orașelor, astfel încât coloana de geometrie să fie indexată spațial.

Verificați-vă rezultatele

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).

Pentru a începe, creați o nouă bază de date (ieșiți mai întâi din linia de comandă psql):

createdb postgis_demo

Amintiți-vă să instalați extensiile PostGIS:

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

Mai departe, vom importa datele prezente în directorul exercise_data/postgis/. Revedeți lecția anterioară pentru instrucțiuni, dar nu uitați că va trebui să creați o nouă conexiune PostGIS la noua bază de date. Aveți posibilitatea de import de la terminal sau prin SPIT. Importați fișierele în următoarele tabele ale bazei de date:

  • 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.

16.4.4.1. Selectare după locație

Obțineți toate clădirile din regiunea KwaZulu:

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)

Sau, în cazul în care vom crea o vizualizare a ei:

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:

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

16.4.4.2. Selectați vecinii

Arată o listă cu toate numele regiunilor adiacente regiunii Hokkaido:

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)

Sub formă de vedere:

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:

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

Observați lipsa unei regiuni (Queensland). Acest lucru se poate datora unei erori de topologie. Artifactele de acest gen ne poate atenționa asupra unor potențiale probleme în interiorul datelor. Pentru a rezolva această dilemă, fără a fi afectați de anomaliile pe care le-ar putea avea datele, am putea folosi un tampon în locul intersecției:

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:

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

Selectați folosind tamponul:

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:

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

De asemenea, este posibil să se selecteze toate obiectele aflate la o anumită distanță, fără etapa suplimentară de creare a unui tampon:

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:

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

16.4.4.3. Selectați valorile unice

Arată o listă cu numele unice, ale tuturor clădirilor din regiunea Queensland:

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)

16.4.4.4. Trimiteri 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.