Disciplina: Bancos de Dados Geogrficos

Atividade Prtica SQL com extenses espaciais

Considere o banco de dados BDG e resolva as seguintes consultas:

1. Listar o nome de todos os municpios que fazem parte da microrregio Blumenau 2. Listar o nome de todos os municpios limtrofes (exteriores e vizinhos) microrregio Blumenau 3. Listar o nome de todos os municpios limtrofes (exteriores e vizinhos) microrregio Blumenau e suas

respectivas microrregies, ordenados pelo nome do municpio 4. Listar o nome de todos os municpios ao longo da rodovia BR-101 5. Listar o nome de todas as localidades contidas no municpio Praia Norte 6. Listar o nome do municpio que contm MAIS localidades 7. Listar o nome dos municpios de Minas Gerais que so cortados por uma rodovia federal 8. Listar o nome das microrregies cujo territrio intercepta ou adjacente ao Rio das Velhas em Minas Gerais 9. Listar as rodovias que cruzam o Rio das Velhas em Minas Gerais 10. Listar os municpios que esto situados a menos de 200 km de Poos de Caldas 11. Listar os nomes das localidades que esto situadas a mais de 50 km de uma sede municipal 12. Informar o nmero de vrtices usados para descrever as fronteiras do Estado do Par 13. Informar os limites geogrficos do Estado do Tocantins (lat. e long. mnima e mxima) 14. Determinar quantos municpios possuem ferrovias atravessando seu territrio 15. Determinar a mxima coordenada Y (Norte) da microrregio Curvelo 16. Calcular a distncia em km entre as sedes dos municpios de Recife e Campina Grande 17. Listar os nomes das 10 sedes de municpios mais prximas da localidade EPITACIOLNDIA 18. Listar os nomes das localidades e sedes de municpios (indicando cada respectivo estado) situadas a menos de

20 km do eixo da rodovia BR-040 19. Calcular a rea, em km2, da poro do territrio formada pelas mesorregies Norte Amazonense, Sudoeste

Amazonense e Centro Amazonense (obs.: no fazer trs consultas e somar os valores...) 20. Calcular o nmero de municpios que fazem parte das mesorregies Mata Pernambucana, Agreste

Pernambucano e Metropolitana de Recife 21. Calcular a populao total da regio hidrogrfica Regio Hidrogrfica do So Francisco 22. Calcular a distncia mdia entre a sede de cada municpio e a sede de seus distritos 23. Determinar a porcentagem da rea de cada estado que est na regio hidrogrfica Regio Hidrogrfica do So

Francisco 24. Criar uma viso mun2010, contendo todos os dados de municpios, inclusive sua geometria, e incorporar os

dados populacionais de 2010 que esto na tabela POPBRASIL. 25. Criar uma viso para apresentar apenas os municpios da regio Norte (AM, AC, AP, PA, RO, RR e TO). 26. Criar uma viso que contenha apenas os estados que esto na bacia do Rio So Francisco (cdigo 74), ou seja,

que contm alguma parte de algum afluente do Rio So Francisco 27. Usar a viso criada acima para determinar o comprimento total do Rio So Francisco que se encontra dentro ou

na fronteira de cada estado. 28. Criar e armazenar temporariamente um polgono (buffer) cujas fronteiras estejam a 50km de cada sede

municipal brasileira 29. Usar o polgono construdo acima para calcular o percentual do territrio nacional que est a mais de 50km de

uma sede municipal 30. Exportar as sedes de municpios com menos de 5.000 habitantes para um arquivo KML e apresentar o contedo

exportado no Google Earth

1. select m.nome_munic from munbrasil m, microbrasil mb where (ST_CONTAINS(mb.geom, m.geom) or ST_COVERS(mb.geom, m.geom)) and mb.nome_micro = 'Blumenau' 2. select m.nome_munic from munbrasil m, microbrasil mb where ST_TOUCHES(mb.geom, m.geom) and mb.nome_micro = 'Blumenau' 3. select m.nome_munic, mb2.nome_micro from munbrasil m, microbrasil mb1, microbrasil mb2 where ST_TOUCHES(mb1.geom, m.geom) and ST_CONTAINS(mb2.geom, m.geom) and mb1.nome_micro = 'Blumenau' order by m.nome_munic 4. select m.nome_munic, m.sigla from munbrasil m, rodovia_br r where r.sigla = 'BR-101' and ST_INTERSECTS(m.geom, r.geom) 5. select nome_localidade from munbrasil m, localidade l where ST_CONTAINS(m.geom, l.geom) and m.nome_munic = 'Praia Norte' 6. select m.nome_munic, count(*) as c from localidade l, munbrasil m where ST_CONTAINS(m.geom, l.geom) GROUP BY m.nome_munic ORDER BY c DESC LIMIT 1 7. select distinct m.nome_munic from munbrasil m, rodovia_br r where ST_INTERSECTS(m.geom, r.geom) and m.sigla = 'MG' order by m.nome_munic 8. select distinct m.nome_micro from microbrasil m, rio r where ST_INTERSECTS(m.geom, r.geom) and r.nome_rio_completo = 'Rio das Velhas' order by m.nome_micro 9. select distinct r.sigla from rodovia_br r, rio, estado e where ST_INTERSECTS(r.geom, rio.geom) and ST_INTERSECTS(rio.geom, e.geom) and rio.cod_curso_dagua = '74'

and e.sigla = 'MG' 10. select b.nome_munic from munbrasil a, munbrasil b where a.nome_munic = 'Caldas' and ST_DISTANCE(ST_TRANSFORM(a.geom, 29193), ST_TRANSFORM(b.geom, 29193)) < 200000 11. select l.nome_localidade, m.nome_munic, ST_DISTANCE_SPHEROID(s.geom, l.geom, 'SPHEROID["WGS 84",6378137,298.257223563]') as dist from sede_munbrasil s, localidade l, munbrasil m where ST_DISTANCE_SPHEROID(s.geom, l.geom, 'SPHEROID["WGS 84",6378137,298.257223563]') > 50000 and ST_CONTAINS(m.geom, s.geom) and ST_CONTAINS(m.geom, l.geom) order by ST_DISTANCE_SPHEROID(s.geom, l.geom, 'SPHEROID["WGS 84",6378137,298.257223563]') desc 12. select ST_NPOINTS(geom) from estado where sigla = 'PA' 13. select ST_BOX2d(geom) from estado where sigla = 'TO' 14. select count(distinct m.nome_munic) from munbrasil m, ferrovia f where st_intersects(m.geom, f.geom) 15. select ST_YMAX(geom) from microbrasil m where m.nome_micro = 'Curvelo' 16. select ST_DISTANCE_SPHEROID(a.geom, b.geom, 'SPHEROID["WGS 84",6378137,298.257223563]') / 1000 from sede_munbrasil a, sede_munbrasil b where a.municipio = 'Recife' and b.municipio = 'Campina Grande' 17. select a.municipio, ST_DISTANCE_SPHEROID(a.geom, b.geom, 'SPHEROID["WGS 84",6378137,298.257223563]') / 1000 from sede_munbrasil a, localidade b where b.nome_localidade = 'EPITACIOLNDIA' order by ST_DISTANCE_SPHEROID(a.geom, b.geom, 'SPHEROID["WGS 84",6378137,298.257223563]') LIMIT 10 Obs.: o problema de busca dos N vizinhos mais prximos resolvido com uma funo no Oracle Spatial, mas no PostGIS essa funo nao existe. A soluo acima no a mais eficiente possvel por causa do clculo de distncia, j que necessrio calcular todos os pares de distncias para depois orden-los. O ndice espacial no utilizado, na verdade.

A consulta abaixo usa o ndice (operador ) APENAS na hora da ordenao dos resultados por distncia ao ponto de referncia. Esse operador retorna a distncia entre os retngulos envolventes mnimos dos objetos, mas no caso os objetos so pontos, e portanto os REM so pontuais tambm. Haveria erro se os objetos envolvidos fossem linhas ou polgonos. O mtodo tambm no retorna o VALOR da distncia, pois no usa a projeo para calcul-lo. Se for necessrio ter esse valor, incluir o comando ST_DISTANCE na linha do SELECT. select a.municipio from sede_munbrasil a, localidade b where b.nome_localidade = 'EPITACIOLNDIA' order by a.geom b.geom LIMIT 10 O mtodo tambm no retorna o VALOR da distncia, pois no usa a projeo para calcul-lo, e sim a distncia entre retngulos. Se for necessrio ter esse valor, incluir o comando ST_DISTANCE na linha do SELECT. select a.municipio, ST_DISTANCE_SPHEROID(a.geom, b.geom, 'SPHEROID["WGS 84",6378137,298.257223563]') / 1000 from sede_munbrasil a, localidade b where b.nome_localidade = 'EPITACIOLNDIA' order by a.geom b.geom LIMIT 10 Existem diversas implementaes de funes de N vizinhos mais prximos, com caractersticas variadas. Por exemplo, pode-se usar a funo ST_DWITHIN para limitar o nmero de candidatos soluo, mas isso implica em estimar uma distncia dentro da qual existam, garantidamente, N vizinhos. Outras solues funcionam interativamente, com um raio de busca que aumenta progressivamente. 18. ( select l.nome_localidade from rodovia_br r, localidade l where ST_DWITHIN ( st_transform(r.geom, 29193), st_transform(l.geom, 29193), 20000) and r.sigla = 'BR-040' ) UNION ( select s.municipio from rodovia_br r, sede_munbrasil s where ST_DWITHIN ( st_transform(r.geom, 29193), st_transform(s.geom, 29193), 20000) and r.sigla = 'BR-040' ) Obs.: esta consulta exige a transformao do SRID para UTM, onde se pode especificar a distncia em metros. 19. select st_area(st_transform(geom, 29190)) from (

select st_union(geom) as geom from mesobrasil where nome_meso in ('Norte Amazonense', 'Sudoeste Amazonense', 'Centro Amazonense') ) as x Obs: o Amazonas est no fuso 20S, MC = 63W, SRID = 29191. Obs2: como so reas muito grandes, que extrapolam um fuso UTM, o ideal seria calcular usando geography. 20. select count(*) from munbrasil m, mesobrasil me where me.nome_meso in ('Mata Pernambucana', 'Agreste Pernambucano', 'Metropolitana de Recife') and (st_contains(me.geom, m.geom) or st_covers(me.geom, m.geom)) 21. select sum(pop2010) from reg_hidrografica r, munbrasilpop m where (st_contains(r.geom, m.geom) or st_covers(r.geom, m.geom)) and regiao_hid = 'Regiao Hidrografica do Sao Francisco' 22. select m.nome_munic, count(l.geom), avg(st_distance_spheroid(l.geom, s.geom, 'SPHEROID["WGS 84",6378137,298.257223563]')) as dist from munbrasil m, localidade l, sede_munbrasil s where st_contains(m.geom, l.geom) and st_contains(m.geom, s.geom) group by m.nome_munic order by dist desc Obs: a consulta acima inclui a contagem do numero de localidades no municpio e ordena o resultado por distncia mdia decrescente 23. select e.sigla, st_area(st_intersection(r.geom, e.geom)) / st_area(e.geom) * 100 as perc from reg_hidrografica r, estado e where regiao_hid = 'Regiao Hidrografica do Sao Francisco' order by perc desc 24. create view mun2010 as ( select m.*, p.homens2010, p.mulheres2010, p.popurb2010, p.poprural2010, p.pop2010 from munbrasil m, popbrasil p where m.mun = p.mun ) 25. create view munnorte as ( select * from munbrasil where regiao = 'Norte' )

26. create view estado_sf as ( select distinct e.* from estado e, rio r where st_intersects(e.geom,r.geom) and r.cod_curso_dagua = '74' ) 27. select e.sigla, sum(st_length(st_transform(r.geom, 29193)))/1000 from rio r, estado e where st_intersects(r.geom, e.geom) and r.cod_curso_dagua = '74' group by e.sigla 28. create table temp as ( select st_union( st_geometryfromtext( st_astext( st_buffer( st_geographyfromtext( astext(geom)), 50000)))) from sede_munbrasil ) 29. select st_area(st_difference(a.geom, b.geom)) / st_area(a.geom) from (select st_union(e.geom) as geom from estado e) as a, (select st_union(st_buffer(s.geom, 0.5)) as geom from sede_munbrasil s ) as b 30. Obs.: A funo ST_AsKML do PostGIS coloca em formato KML a geometria de CADA objeto passado como parmetro. O arquivo KML propriamente dito requer algumas linhas de header e de fechamento para que esteja bem formado. O script abaixo cria uma funo que acrescenta essas linhas saida de ST_AsKML -- Function: public.askml(text, text, geometry) -- DROP FUNCTION public.askml(text, text, geometry); CREATE OR REPLACE FUNCTION public.askml(name text, description text, the_geom geometry) RETURNS text AS $BODY$ DECLARE result text; BEGIN result := '' || E'\n' || '' || E'\n' || '' || E'\n' ||

'' || quote_literal(name) || '' || E'\n' || '' || quote_literal(description) || '' || E'\n\n' || '' || E'\n' || ' ' || E'\n' || ' ff00ff00' || E'\n' || ' 1' || E'\n' || ' ' || E'\n' || ' ' || E'\n' || ' 5f00ff00' || E'\n' || ' ' || E'\n' || '' || E'\n\n' || '' || E'\n' || '#defaultStyle' || E'\n'; result := result || askml(the_geom) || E'\n'; result := result || '' || E'\n\n' || '' || E'\n' || ''; RETURN result; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE; Uso: select askml('cid', 'pequenas cidades', collect(geom)) from sede_munbrasil s, popbrasil p where pop2010