Outdated version of the documentation. Find the latest one here.

16.4. Lesson: Consultas Espaciais

As consultas espaciais não são diferentes de outras consultas do banco de dados. Você pode usar a coluna de geometria como qualquer outra coluna de banco de dados. Com a instalação do PostGIS em nosso banco de dados tem funções adicionais para consultar nosso banco de dados.

A meta para esta lição: Para ver como as funções espaciais são implementados de forma semelhante a funções não-espaciais “normais”.

16.4.1. Operadores Espaciais

Quando você quiser saber quais os pontos estão a uma distância de 2 graus até um ponto (X, Y), você pode fazer isso com:

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

valor the_geom acima foi truncado para o espaço nesta página. Se você quiser ver o ponto em coordenadas legíveis, tentar algo parecido com o que você fez na seção “Ver um ponto como WKT”, acima.

Como sabemos que a consulta acima retorna todos os pontos dentro de 2 * graus *? Por que não dois metros * *? Ou qualquer outra unidade, para que o assunto?

Cheque seus resultados

16.4.2. Índices espaciais

Também podemos definir índices espaciais. Um índice espacial faz com que suas consultas espaciais muito mais rápido. Para criar um índice espacial na utilização coluna de geometria

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (the_geom);

\d people

Resultado:

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

Modificar a tabela de cidades de modo que sua coluna de geometria é espacialmente indexado.

Cheque seus resultados

16.4.4. PostGIS Espacial Funções Demonstração

A fim de demonstração de PostGIS funções espaciais, vamos criar um novo banco de dados que contém alguns dados (fictícios).

Para iniciar, criaremos uma nova base de dados (feche primeiro a shell psql):

createdb postgis_demo

Lembre-se de instalar as extensões PostGIS:

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

Em seguida, importe os dados fornecidos no diretório :kbd: exercise_data/postgis/. Volte à lição anterior para obter instruções, mas lembre-se que você vai precisar criar uma nova conexão PostGIS para o novo banco de dados. Você pode importar a partir do terminal ou via SPIT. Importe os arquivos para as seguintes tabelas do banco de dados:

  • points.shp em edifícios

  • lines.shp Em estradas

  • polygons.shp em regiões

Coloque essas três camadas de banco de dados para QGIS através do: guilabel: caixa de diálogo Adicionar PostGIS Layers, como de costume. Quando você abre as suas tabelas de atributos, você vai notar que eles têm tanto um: kbd: campo id e uma: kbd:` campo gid` criado pela importação PostGIS.

Agora que as tabelas foram importadas, podemos usar PostGIS para consultar os dados. Volte para o seu terminal (linha de comando) e digite o prompt do psql executando

psql postgis_demo

Iremos demonstrar algumas dessas instruções selecionadas, criando visualizações a partir delas, para que você possa abri-las no QGIS e ver os resultados.

16.4.4.1. Selecionar pela localização

Obter todos os edifícios na região 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';

Resultado:

 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)

Ou, se criarmos uma visão dele:

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

Adicionar a vista como uma camada e exibi-lo no QGIS:

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

16.4.4.2. Selecione os vizinhos

Mostrar uma lista de todos os nomes das regiões adjacentes a região de Hokkaido:

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

Resultado:

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

Como vista:

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

En QGIS:

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

Observe a região em falta (Queensland). Isto pode ser devido a um erro de topologia. Artefatos como isso pode nos alertar para possíveis problemas nos dados. Para resolver este enigma sem se envolver nas anomalias dos dados pode ter, nós poderíamos usar um tampão se cruzam em vez

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

Isso cria um buffer de 100 metros ao redor da região de Hokkaido.

A área sombreada representa o buffer:

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

Selecione usando o 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';

Nesta consulta, a visão tampão original é usado como qualquer outra tabela seria. É dado o apelido: kbd: a, e seu campo geometria,: kbd:` a.the_geom`, é usado para selecionar qualquer polígono no: kbd: mesa region (apelido: kbd:` b`) que ele intersecta. No entanto, a própria Hokkaido é excluído desta instrução SELECT, porque nós não queremos isso; queremos apenas as regiões adjacente.

En QGIS:

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

É também possível selecionar todos os objetos dentro de uma determinada distância, sem a etapa adicional de criar um 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';

Isto atinge o mesmo resultado, sem necessidade de intercalar o passo de buffer:

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

16.4.4.3. Selecione valores únicos

Mostrar uma lista de nomes de cidades únicas para todos os edifícios na região de Queensland:

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

Resultado:

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

16.4.4.4. Outros exemplos ...

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

Você já viu como consultar objetos espaciais usando as novas funções de banco de dados do PostGIS.

16.4.6. What’s Next?

Em seguida vamos para investigar as estruturas de geometrias mais complexas e como criá-las usando o PostGIS.