17.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”.
17.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 2 metros? Ou qualquer outra unidade, que interesse?
17.4.2. Índices espaciais¶
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
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)
17.4.3. Try Yourself 
¶
Modificar a tabela de cidades de modo que sua coluna de geometria é espacialmente indexado.
17.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).
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 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.
17.4.4.1. Selecionar pela localização¶
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';
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)
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';
Adicionar a vista como uma camada e exibi-lo no QGIS:
17.4.4.2. Selecione os vizinhos¶
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';
Resultados:
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';
En 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';
Isso cria um buffer de 100 metros ao redor da região de Hokkaido.
A área sombreada representa o buffer:
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';
Nesta consulta, a visão do buffer original é usada como qualquer outra tabela seria. É dado o apelido a, e seu campo geometria, a.the_geom, é usado para selecionar qualquer polígono na tabela region (apelido 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 adjacentes.
En 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';
Isto atinge o mesmo resultado, sem necessidade de intercalar o passo de buffer:
17.4.4.3. Selecione valores únicos¶
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';
Resultados:
name
---------
Beijing
Berlin
Atlanta
(3 rows)
17.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;
17.4.5. In Conclusion¶
Você já viu como consultar objetos espaciais usando as novas funções de banco de dados do PostGIS.
17.4.6. What’s Next?¶
Em seguida vamos para investigar as estruturas de geometrias mais complexas e como criá-las usando o PostGIS.