From GeoBox

Spatial queries

Some links:

Geometries PostGIS ver. 1.5 Quick Guide - Cheatsheet

Exercícios

Quantos MacDonalds têm McDrive?

SELECT COUNT(*)
FROM mcdonalds
WHERE extra LIKE 'McDrive%'

Quantos McDonalds estão (pelo nome) em shoppings?

SELECT nome, morada
FROM mcdonalds
WHERE nome ilike '%shop%'

Selecionar os McDonalds com Braga no nome

SELECT * FROM mcdonalds WHERE nome ilike '%braga%';

Coordenadas dos restaurantes anteriores:

SELECT wkb_geometry FROM mcdonalds WHERE nome ilike '%braga%';

Como as coordenadas estão num formato binário, para se poderem ler é necessário usar uma função st_astext:

SELECT st_astext(wkb_geometry) FROM mcdonalds WHERE nome ilike '%braga%';

Descubra, por exemplo, com o Google Maps, as coordenadas aproximadas de sua casa. Que sejam: -8.751404 40.612153

Para converter as mesmas para o sistema de coordenadas oficial de Portugal, use uma query:

SELECT astext(ST_transform(st_geometryfromtext('POINT(-8.751404 40.612153)',4326),3763));

Em alternativa, pode fazer a mesma conversão na linha de comandos:

echo -8.751404 40.612153 | cs2cs +init=epsg:4326 +to +init=epsg:3763

Sabendo que a minha casa é em: 'POINT(-24034 209179)'.

Qual a distância ao McDonals 'Braga I'

SELECT nome, st_distance(wkb_geometry, ST_GeomFromText('POINT(-22518 208748)',3763)) 
FROM mcdonalds
WHERE nome = 'Braga I'

Quais os mcdonalds mais próximos da minha casa?

SELECT nome, st_distance(wkb_geometry, ST_GeomFromText('POINT(-22518 208748)',3763)) AS distancia
FROM mcdonalds
ORDER BY distancia

A menos de 5km?

SELECT nome, st_distance(wkb_geometry, ST_GeomFromText('POINT(-22518 208748)',3763)) 
FROM mcdonalds
WHERE st_distance(wkb_geometry, ST_GeomFromText('POINT(-22518 208748)',3763)) < 5000;

Buffer à volta dos Macs

CREATE TABLE cheeseburger AS
SELECT ogc_fid, nome, st_buffer(wkb_geometry, 750)
FROM mcdonalds

Exercícios com joins

Qual o código portal de minha casa?

SELECT cp4
FROM cttshapefile
WHERE 
st_contains(cttshapefile.the_geom, ST_GeomFromText('POINT(-22518 208748)',3763))

ou, usando as coordenadas em WGS84

SELECT cp4
FROM cttshapefile
WHERE st_contains(the_geom, ST_transform(st_geometryfromtext('POINT(-8.751297 40.612332)',4326),3763) )

Quantos McDonalds existem em lugares cujo código postal começa por 4.

SELECT nome, cp4
FROM mcdonalds, cttshapefile
WHERE 
st_contains(cttshapefile.the_geom, mcdonalds.wkb_geometry)
AND cp4 LIKE '4%'

Creating a new table

CREATE TABLE hamburguer 
AS SELECT ogc_fid, nome, st_buffer(wkb_geometry, 1000) AS wkb_geometry FROM mcdonalds

Encontrar diferenças entre os códigos postais dos restaurantes em relação à tabela dos CTT.

SELECT nome, SUBSTRING(codpostal,1,4), cp4
FROM mcdonalds, cttshapefile
WHERE 
st_contains(the_geom, wkb_geometry)
AND SUBSTRING(codpostal,1,4) != cp4

Metadados

SELECT * FROM spatial_ref_sys WHERE srid = '20790' OR srid = '3763';
SELECT COUNT(*) FROM spatial_ref_sys;

Visualizar a tabela mcdonalds no Google earth

ogr2ogr -s_srs EPSG:3763 -t_srs EPSG:4326 -f "KML" mcdonalds.kml PG:"host=localhost user=geobox dbname=geotuga password=geobox" "mcdonalds"

Importação de uma shapefile

Obter a shapefile a partir de http://sniamb.apambiente.pt/webatlas. Existe uma cópia disponível em: c_albufeiras.zip

Utilizar o Q-GIS, extensão PostGIS Manager. No PostGIS Manager, já deverá estar ligado à base de dados geotuga. Para importar uma shapefile, use o menu Data -> Load data from shapefile.

File:Load shapefile to PostGIS.png

Confirmar que a tabela geometry_columns foi devidamente actualizada.

Importação de uma shapefile (com erros!)

Entrar em http://sniamb.apambiente.pt/webatlas/ e escolher o tema IV. Ambiente Humano e depois Turismo (Recursos).

Nos metadados, procurar o Sistema de Referência.

Se tentar importar esta tabela, dá-lhe o seguinte erro:

DB Error
An error occured when executing a query
new row for relation "rec_tur" violates check constraint "enforce_geotype_the_geom"

Este erro surge porque ao ser criada a tabela para onde vai ser carregada a shapefile, são também criadas 4 contrições e uma desses constrições não permite que os dados sejam inseridos. Portanto, a resolução deste problema passa pela importação da shapefile de uma forma ligeiramente diferente.

Faz-se em duas partes:

Primeira Parte

1. No diálogo "Load data to PostGIS", preencher tudo como anteriormente, mas nas "Action", em vez de usar a opção pré-definida "Create new table", escolher antes "Create table only, don't add data"
2. No pgAdmin, pode verificar que a tabela foi criada, mas sem dados nenhuns.
3. No pgAdmin, eliminar a contrição "enforce_geotype_the_geom"

Segunda Parte

1. No diálogo "Load data to PostGIS", preencher tudo como anteriormente, mas nas "Action" escolha agora "Append data into table"
2. No pgAdmin, pode verificar que a tabela já tem dados.

Queries sobre a tabela rec_tur

Contabilizar os recursos importados

SELECT COUNT(*) FROM rec_tur

Contabilizar apenas museus

SELECT COUNT(*) FROM rec_tur
WHERE rec_turist = 'Museu'

Ver que tipo de recursos existem

SELECT DISTINCT rec_turist
FROM rec_tur

Contabilizar o número de recursos por cada tipo

SELECT COUNT(*), rec_turist
FROM rec_tur
GROUP BY rec_turist
ORDER BY COUNT(*) DESC

Calcular os recursos a menos de 5km da minha casa

SELECT rec_turist, 
st_distance(the_geom, ST_transform(st_geometryfromtext('POINT(-8.751404 40.612153)',4326),20790) ) 
FROM rec_tur
WHERE st_distance(the_geom, ST_transform(st_geometryfromtext('POINT(-8.751404 40.612153)',4326),20790) ) < 5000

Inserir um novo registo na tabela rec_tur

INSERT INTO rec_tur (simbolo, rec_turist, the_geom) 
VALUES (0, 'Casa do Jorge',  ST_transform(st_geometryfromtext('POINT(-8.751404 40.612153)',4326),20790))

Relacionar a tabela rec_tur com os códigos postais

SELECT rec_turist, cp4
FROM rec_tur, cttshapefile
WHERE 
st_contains(cttshapefile.the_geom, st_transform(rec_tur.the_geom, 3763))
AND cp4 LIKE '38%'

Queries no Q-GIS sobre a tabela rec_tur

Abrir no Q-GIS

Fazer query: "rec_turist" = 'Museu'