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