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