====== SQL das Notas de Aula CAP349 - 2014 ======
Habilitando a extensao PostGIS 2.0 em um banco de dados:
CREATE EXTENSION postgis
Verificando como o PostGIS esta configurado
SELECT postgis_full_version();
Criando uma tabela com coluna geométrica:
CREATE TABLE estacoes_pluviometricas
(
gid INT4,
location GEOMETRY(POINT, 4618),
nome VARCHAR(25)
);
Inserindo dados em uma tabela geométrica:
INSERT INTO estacoes_pluviometricas
VALUES(1, ST_GeomFromText('POINT(-46.98 -19.57)', 4618), 'DINIZ-ARAXA');
INSERT INTO estacoes_pluviometricas
VALUES(2, ST_GeomFromText('POINT(-43.59 20.37)', 4618), 'QUEIROZ-OURO-PRETO');
Recuperando os dados de uma tabela com coluna espacial:
SELECT gid, ST_AsText(location) FROM estacoes_pluviometricas;
Consultando as views de metadado do PostGIS:
SELECT * FROM spatial_ref_sys WHERE srid = 4618;
SELECT * FROM geometry_columns WHERE f_table_name = 'estacoes_pluviometricas';
Consulta 01: recuperacao de geometrias que interceptam um dado box:
SELECT nommuni, ST_AsText(geom) AS geom
FROM mg_municipios
WHERE geom && ST_MakeEnvelope(-46.557928, -18.330733, -45.382262, -16.691372, 4618);
Consulta 02: recuperacao de geometrias a uma certa distancia:
SELECT nommuni, ST_AsText(geom) AS geom
FROM mg_municipios
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(-45.970095 -17.5110525)', 4618), 1.0);
Consulta 03: quais os poligonos de aptidao agricola que interceptam a geometria do municipio de "João Pinheiro" ?
--SELECT ST_Relate(ap.geom, mn.geom)
SELECT mn.nommuni, ap.classe, ap.geom, mn.geom
FROM mg_aptidao ap JOIN mg_municipios mn ON ST_Intersects(ap.geom, mn.geom)
WHERE nommuni = 'João Pinheiro';
ou
SELECT mn.nommuni, ap.classe, ap.geom, mn.geom
FROM mg_municipios mn, mg_aptidao ap
WHERE ST_Intersects(mn.geom, ap.geom) AND nommuni = 'João Pinheiro';
Consulta 04: Quais as areas de aptidao agricola de cada municipio de Minas Gerais?
SELECT mn.nommuni, ap.classe, ST_Intersection(ap.geom, mn.geom)
FROM mg_aptidao ap JOIN mg_municipios mn ON ST_Intersects(ap.geom, mn.geom)
Consulta 05: Como estao organizadas as areas de aptidao agricola do municipio de 'João Pinheiro'?
SELECT mn.nommuni, ap.classe, ST_Intersection(ap.geom, mn.geom)
FROM mg_aptidao ap JOIN mg_municipios mn ON ST_Intersects(ap.geom, mn.geom)
WHERE nommuni = 'João Pinheiro';
CREATE TABLE aptidao_joao_pinheiro AS
SELECT mn.nommuni AS nome_municipio, ap.classe AS classe, ST_Intersection(ap.geom, mn.geom) AS geom
FROM mg_aptidao ap JOIN mg_municipios mn ON ST_Intersects(ap.geom, mn.geom)
WHERE nommuni = 'João Pinheiro';
SELECT populate_geometry_columns('aptidao_joao_pinheiro'::regclass, false);
ALTER TABLE aptidao_joao_pinheiro ADD COLUMN gid SERIAL;
ALTER TABLE aptidao_joao_pinheiro ADD CONSTRAINT aptidao_joao_pinheiro_pk PRIMARY KEY (gid)
CREATE INDEX spidx_aptidao_joao_pinheiro ON aptidao_joao_pinheiro USING GIST(geom);
Consulta 06: Gerar o contorno do Estado da Bahia a partir do mapa de municípios da Bahia.:
CREATE TABLE contorno_ba AS
SELECT 1 AS gid, ST_Union(geom) AS geom FROM ba_municipios;
SELECT populate_geometry_columns('contorno_ba'::regclass, false);
ALTER TABLE contorno_ba ADD CONSTRAINT contorno_ba_pk PRIMARY KEY (gid)
Consulta 07: Quais os municípios vizinhos a Salvador?
SELECT m1.nome_munic, m2.nome_munic, m2.gid, m2.geom
FROM ba_municipios m1,
ba_municipios m2
WHERE ST_Touches(m1.geom,
m2.geom)
AND m2.nome_munic <> 'Salvador'
AND m1.nome_munic = 'Salvador';