16.4. Consultas Espaciales ILSI

Las consultas espaciales no son diferentes de otras consultas de bases de datos. Puede utilizar la columna de geometría como cualquier otra columna de la base de datos. Con la instalación de PostGIS en nuestra base de datos, tenemos funciones adicionales para consultar nuestra base de datos.

El objetivo de esta lección: Ver cómo las funciones espaciales se implementan de manera similar a las funciones «no-espaciales» normales.

16.4.1. Operadores espaciales

Cuando desee saber qué puntos están a una distancia de 2 grados de un punto (X, Y), puede hacerlo con:

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

Resultados:

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

Nota

el valor de the_geom anterior se truncó por espacio en esta página. Si desea ver el punto en coordenadas legibles por humanos, intente algo similar a lo que hizo en la sección «Ver un punto como WKT», arriba.

¿Cómo sabemos que la consulta anterior devuelve todos los puntos dentro de 2 * grados *? ¿Por qué no 2 * metros *? ¿O cualquier otra unidad, para el caso?

Answer

Las unidades que usa la consulta de ejemplo son grados, porque el SRC que usa la capa es WGS 84. Este es un SRC geográfico, lo que significa que sus unidades están en grados. Un SRC Proyectado, como las proyecciones UTM, es en metros.

Recuerde que cuando escribe una consulta, necesita saber en qué unidades se encuentra el SRC de la capa. Esto le permitirá escribir una consulta que devolverá los resultados que espera.

16.4.2. Índices espaciales

También podemos definir índices espaciales. Un índice espacial hace que sus consultas espaciales sean mucho más rápidas. Para crear un índice espacial en la columna de geometría, use:

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (the_geom);

\d people

Resultados:

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

Modifique la tabla de ciudades para que su columna de geometría esté indexada espacialmente.

Answer

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (the_geom);

16.4.4. Demostración de funciones espaciales de PostGIS

Para hacer una demostración de las funciones espaciales de PostGIS, crearemos una nueva base de datos que contiene algunos datos (ficticios).

Para comenzar, cree una nueva base de datos (primero salga del shell psql):

createdb postgis_demo

Recuerda instalar las extensiones de postgis:

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

A continuación, importe los datos proporcionados en el directorio exercise_data/postgis/. Consulte la lección anterior para obtener instrucciones, pero recuerde que deberá crear una nueva conexión PostGIS a la nueva base de datos. Puede importar desde el terminal o mediante DB Manager. Importe los archivos a las siguientes tablas de la base de datos:

  • points.shp en building

  • lines.shp en road

  • polygons.shp en region

Cargue estas tres capas de base de datos en QGIS a través del cuadro de diálogo Add PostGIS Layers, como de costumbre. Cuando abra sus tablas de atributos, notará que tienen un campo: kbd: ʻid` y un campo gid creado por la importación de PostGIS.

Ahora que se importaron las tablas, podemos usar PostGIS para consultar los datos. Regrese a su terminal (línea de comando) e ingrese el símbolo del sistema psql ejecutando:

psql postgis_demo

Demostraremos algunas de estas declaraciones selectas creando vistas a partir de ellas, para que pueda abrirlas en QGIS y ver los resultados.

Seleccionar por ubicacion

Obtén todos los edificios de la región de 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';

Resultados:

 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)

O, si creamos una vista a partir de ella:

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';

Agregue la vista como una capa y véala en QGIS:

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

Seleccionar vecinos

Muestra una lista de todos los nombres de las regiones adyacentes a la región de Hokkaido:

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

Resultados:

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

Como una vista:

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

En QGIS:

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

Tenga en cuenta la región que falta (Queensland). Esto puede deberse a un error de topología. Artefactos como este pueden alertarnos sobre posibles problemas en los datos. Para resolver este enigma sin quedar atrapado en las anomalías que pueden tener los datos, podríamos usar una intersección de búfer en su lugar:

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

Esto crea una zona de influencia de 100 metros alrededor de la región de Hokkaido.

El área más oscura es el búfer:

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

Seleccionar usando el búfer:

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';

En esta consulta, la vista de búfer original se usa como cualquier otra tabla. Se le da el alias a, y su campo de geometría, a.the_geom, se usa para seleccionar cualquier polígono en la tabla region (alias b) que lo cruza. Sin embargo, el propio Hokkaido está excluido de esta declaración de selección, porque no lo queremos; solo queremos las regiones contiguas.

En QGIS:

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

También es posible seleccionar todos los objetos dentro de una distancia determinada, sin el paso adicional de crear un búfer:

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';

Esto logra el mismo resultado, sin necesidad del paso intermedio del búfer:

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

Seleccionar valores únicos

Muestre una lista de nombres de ciudades únicos para todos los edificios en la región de Queensland:

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

Resultados:

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

Otros ejemplos …

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

Ha visto cómo consultar objetos espaciales utilizando las nuevas funciones de base de datos de PostGIS.

16.4.6. What’s Next?

A continuación, vamos a investigar las estructuras de geometrías más complejas y cómo crearlas usando PostGIS.