--Exerc�cios feitos em sala de aula, usando alguns dados importados do IBGE
--Alguns dados foram digitalizados utilizando a ferramenta QGIS como o layer de ferrovias
--Sistema de coordenadas ou SRID utilizado 4326 WGS84

--Mudando o sistema de coordenadas
UPDATE municipios SET geom = ST_SetSRID(geom,4326);
UPDATE malha_viaria SET geom = ST_SetSRID(geom,4326);
UPDATE principais_cidades SET geom = ST_SetSRID(geom,4326);
UPDATE ferrovias SET geom = ST_SetSRID(geom,4326);


--1) Quais as rodovias que tocam SP-461
drop table temp_toca_sp461
create table temp_toca_sp461 as
select b.* from malha_viaria a, malha_viaria b
where a.nm_sigla = 'SP-461' AND
ST_TOUCHES(a.geom,b.geom);

--2) Quais sao os trechos de rodovias ligadas pelas ferrovias cadastras
create table temp_exercicio2 as
SELECT b.* FROM ferrovias f, malha_viaria b
WHERE ST_intersects(f.geom, b.geom);

--3) Quais municipios fazem divisa com o municipio de ADAMANTINA ?

create table temp_exercicio3 as
SELECT b.* FROM municipios a, municipios b
WHERE a.nm_municip = 'ADAMANTINA'
AND b.nm_municip <> 'ADAMANTINA'
AND ST_intersects(a.geom,b.geom);

--4) Criando tabela para cadastramento e georeferenciamento de ferrovias
CREATE TABLE ferrovias (
 gid serial primary key,
 nome varchar(20),
 geom geometry(LINESTRING)
);


--############################################################################################################
--Exerc�cio para classe - Baseando-se nas consultas abaixo com os dados do IBGE importados na Aula1 para o PostgreSQL + PostGIS
--Criar 3 consulta usando as camadas que importamos em sala de aula
--Colocar no email o arquivo SQL com as 3 consultas e o nome do aluno
--E-mail jairodealmeida@gmail.com
--############################################################################################################



-- 5) Adicionando coluna espacial
SELECT AddGeometryColumn
('public','municipios','centroid',4326,'POINT',2);
select * from municipios  limit 5
UPDATE municipios SET centroid = ST_CENTROID(geom);


--6) Qual a quantidade de vias(malhas_viarias)
--totalmente contidas no municipio de ADAMANTINA?
SELECT a.nm_municip,
count(b.*) as quantidade
FROM municipios a,malha_viaria b
WHERE ST_Contains(a.geom,b.geom)
AND a.nm_municip in ('ADAMANTINA')
GROUP BY a.nm_municip
ORDER BY quantidade;

-- 7) Qual o total de vias pavimentadas no municipio de ROSANA
create table temp_exercicio7 as
select a.* quantidade
FROM malha_viaria a,municipios b
WHERE b.nm_municip='ROSANA'
AND a.cd_tipo_pa='pavimentada'
AND st_intersects(a.geom, b.geom);

-- 8) Quais sao as malhas_viarias em pavimentacao
--do estado de sao paulo
create table temp_exercicio8 as
SELECT b.*
FROM unidade_federativa a, malha_viaria b
WHERE a.nm_estado = 'SÃO PAULO'
AND cd_tipo_pa='em pavimentacao'
AND a.geom && b.geom;

-- 9) Vendo a projeção em SAD69
select * from spatial_ref_sys where srid = 29101

-- 11) Qual a distancia entre as cidades de Assis e Campinas
SELECT
ST_Distance(
    ST_Transform(a.geom,31983),
    ST_Transform(b.geom,31983)
    )/1000 distancia_km
FROM principais_cidades a, principais_cidades b
WHERE a.nm_nome = 'Assis' AND b.nm_nome = 'Campinas'
limit 1;

-- 12) Mudando o sistema de coordenadas
UPDATE manchas_urbanas SET geom = ST_SetSRID(geom,4674);

SELECT UpdateGeometrySRID('manchas_urbanas','geom',4326);

select ST_SRID(geom) from manchas_urbanas

-- 12) Resultado as manchas urbanas em hectares
SELECT m.nome ,
ST_AREA(m.geom,true) / 10000 hectares
FROM manchas_urbanas m;

-- 13) Quais são as cidades mais proximas a cidade de Campinas?
SELECT  a.nm_nome,
    ST_Distance(
        ST_Transform(a.geom,31983),
        ST_Transform(b.geom,31983)
    )/1000 AS distancia
FROM principais_cidades a, principais_cidades b
WHERE b.nm_nome = 'Campinas'
AND a.nm_nome <> 'Campinas'
ORDER BY distancia ASC
LIMIT 6;

-- 14) Quais são as cidades localizadas num raio de até 50
--quilometros do Município de Campinas?
SELECT  b.nm_nome
FROM municipios a,principais_cidades b
WHERE a.nm_municip = 'CAMPINAS'
AND ST_Distance(
    ST_Transform(b.geom, 31983),
    ST_Transform(a.geom,31983))/1000 <= 50;

create table temp_exercicio14 as
SELECT b.nm_nome, b.geom
FROM municipios a,principais_cidades b
WHERE a.nm_municip = 'CAMPINAS'
AND ST_DWITHIN(
    ST_Transform(b.geom, 31983),
    ST_Transform(a.geom,31983),
    50*1000);

create table temp_exercicio14_buffer as
SELECT ST_Buffer(
    ST_Transform(a.geom,31983),
    50*1000)
FROM municipios a
WHERE a.nm_municip = 'CAMPINAS';

-- 15) Criar um buffer de 200 metros a partir da rodovia SP-461
drop table temp_exercicio15;
create table temp_exercicio15 as
SELECT
ST_UNION(ST_Buffer(
    ST_Transform(a.geom,31983),
    200)
)
FROM malha_viaria a
WHERE a.nm_sigla = 'SP-461';



-- 16) Criando indice espacial
CREATE INDEX principais_cidades_idx
  ON principais_cidades
  USING GIST (geom);

--  Criando indice espacial
CREATE INDEX malha_viaria_idx
  ON malha_viaria
  USING GIST (geom);

--Criando indice espacial
CREATE INDEX municipios_idx
  ON municipios
  USING GIST (geom);

VACUUM ANALYZE principais_cidades;
VACUUM ANALYZE malha_viaria;
VACUUM ANALYZE municipios;


-- 17) Identificar qual o cidade por meio da coordenada fornecida: Exemplo 'POINT(-63.88186513101888 -8.74772579141916)'
SELECT nm_nome,
ST_DISTANCE(
    ST_GeometryFromText('POINT(-63.88186513101888 -8.74772579141916)', 4326),
    geom,
    true) as distancia
FROM principais_cidades
ORDER BY distancia ASC
limit 1;

-- 18) Quais os municípios cruzados pela SP-461 e qual a extensão
--total dos trechos contidos em cada um destes municípios?
SELECT b.nm_municip,
sum(st_length(st_transform(a.geom,31983)))/1000 km
FROM malha_viaria a, municipios b
WHERE a.nm_sigla = 'SP-461' AND
a.geom && b.geom
GROUP BY b.nm_municip
ORDER BY b.nm_municip asc

create table temp_exercicio18 as
SELECT b.nm_municip,
sum(st_length(st_transform(a.geom,31983)))/1000 km,
ST_UNION(b.geom) as geouniao
FROM malha_viaria a, municipios b
WHERE a.nm_sigla = 'SP-461' AND
ST_CROSSES(a.geom , b.geom)
GROUP BY b.nm_municip
ORDER BY b.nm_municip asc

-- 19)Selecionar o ponto inicial, final e a entensão das ferrovias
--que formam a Ferrovia1, nos limites do municipio de CAMPINAS?
SELECT
ST_AsText(ST_StartPoint(ST_LineMerge(a.geom))) inicial,
ST_AsText(ST_EndPoint(ST_LineMerge(a.geom))) final,
ST_Length(a.geom,true) metros
FROM ferrovias  a, municipios b
WHERE nm_municip = 'CAMPINAS' AND
ST_Intersects(b.geom, a.geom)

--Especializando
create table tep_exercicio19 as
SELECT
ST_StartPoint(ST_LineMerge(a.geom)) inicial,
ST_EndPoint(ST_LineMerge(a.geom)) final,
ST_Length(a.geom,true) metros
FROM malha_viaria a, municipios b
WHERE nm_municip = 'CAMPINAS' AND
ST_Intersects(b.geom, a.geom)


SELECT AddGeometryColumn
('public','municipios','box',4326,'GEOMETRY',2);
UPDATE municipios SET box = ST_ENVELOPE(geom);

SELECT
ST_MAKELINE(
    a.geom,
    b.geom
    ) ligacao
FROM principais_cidades a, principais_cidades b
WHERE a.nm_nome = 'Assis' AND b.nm_nome = 'Campinas';

select st_geometrytype(geom) from principais_cidades limit 5