====== SQL das Notas de Aula CAP349 - 2014 ======

Habilitando a extensao PostGIS 2.0 em um banco de dados:
<code sql>
CREATE EXTENSION postgis
</code>

Verificando como o PostGIS esta configurado
<code sql>
SELECT postgis_full_version();
</code>

 Criando uma tabela com coluna geométrica:
<code sql>
CREATE TABLE estacoes_pluviometricas
(
  gid INT4,
  location GEOMETRY(POINT, 4618),
  nome VARCHAR(25)
);
</code>

Inserindo dados em uma tabela geométrica:
<code sql>
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');
</code>

Recuperando os dados de uma tabela com coluna espacial:
<code sql>
SELECT gid, ST_AsText(location) FROM estacoes_pluviometricas;
</code>

Consultando as views de metadado do PostGIS:
<code sql>
SELECT * FROM spatial_ref_sys WHERE srid = 4618;

SELECT * FROM geometry_columns WHERE f_table_name = 'estacoes_pluviometricas';
</code>

Consulta 01: recuperacao de geometrias que interceptam um dado box:
<code sql>
SELECT nommuni, ST_AsText(geom) AS geom 
  FROM mg_municipios
 WHERE geom && ST_MakeEnvelope(-46.557928, -18.330733, -45.382262, -16.691372, 4618);
</code>

Consulta 02: recuperacao de geometrias a uma certa distancia:
<code sql>
SELECT nommuni, ST_AsText(geom) AS geom 
  FROM mg_municipios
 WHERE ST_DWithin(geom, ST_GeomFromText('POINT(-45.970095 -17.5110525)', 4618), 1.0);
</code>

Consulta 03: quais os poligonos de aptidao agricola que interceptam a geometria do municipio de "João Pinheiro" ?
<code sql>
--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';
</code> 
 
ou

<code sql> 
 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';
</code>

Consulta 04: Quais as areas de aptidao agricola de cada municipio de Minas Gerais?
<code sql>
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)
</code>

Consulta 05: Como estao organizadas as areas de aptidao agricola do municipio de 'João Pinheiro'?
<code sql>
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';  
</code>

<code sql>
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);
</code>
 
Consulta 06: Gerar o contorno do Estado da Bahia a partir do mapa de municípios da Bahia.:
<code sql>
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)
</code>

Consulta 07: Quais os municípios vizinhos a Salvador?
<code sql>
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';
</code>