queries_uteis_em_spatialite
Diferenças
Aqui você vê as diferenças entre duas revisões dessa página.
Ambos lados da revisão anteriorRevisão anteriorPróxima revisão | Revisão anterior | ||
queries_uteis_em_spatialite [2018/05/07 22:46] – 189.4.76.1 | queries_uteis_em_spatialite [2019/08/30 18:13] (atual) – [Juntar várias camadas escolhendo quais campos] 150.162.179.77 | ||
---|---|---|---|
Linha 1: | Linha 1: | ||
+ | ====Juntar várias camadas escolhendo quais campos==== | ||
+ | |||
+ | Bom para consolidar em uma camada só várias informações provenientes de diversas camadas ou tabelas. | ||
+ | |||
+ | <code sql> | ||
+ | -- Apaga a tabela - só é necessário depois da primeira vez | ||
+ | DROP TABLE " | ||
+ | |||
+ | -- Cria uma nova tabela | ||
+ | CREATE TABLE " | ||
+ | SELECT | ||
+ | a.*, | ||
+ | c." | ||
+ | e." | ||
+ | e." | ||
+ | e." | ||
+ | e." | ||
+ | e." | ||
+ | f." | ||
+ | f." | ||
+ | f." | ||
+ | |||
+ | -- Define quais tabelas serão usadas e cria apelidos para elas (a, b, c, ...) | ||
+ | FROM | ||
+ | " | ||
+ | " | ||
+ | " | ||
+ | " | ||
+ | " | ||
+ | " | ||
+ | |||
+ | -- estabelece a condicao para o join | ||
+ | WHERE a.codsetor = b.codsetor and a.codsetor = c.codsetor and a.codsetor = d.codsetor and a.codsetor = e.codsetor and a.codsetor = f.idSetor; | ||
+ | |||
+ | -- A nova camada criada não reconhecerá a geometria. Esse comando resolve o problema | ||
+ | SELECT RecoverGeometryColumn(" | ||
+ | </ | ||
====Juntar duas camadas que se interseccionam==== | ====Juntar duas camadas que se interseccionam==== | ||
Essa query permite passar os atributos (colunas) de uma camada para outra com base na intersecção entre os elementos, isto é, os atributos de um elemento serão passadas para os elementos da outra camada (tabela) que eles interceptam. | Essa query permite passar os atributos (colunas) de uma camada para outra com base na intersecção entre os elementos, isto é, os atributos de um elemento serão passadas para os elementos da outra camada (tabela) que eles interceptam. | ||
Linha 39: | Linha 76: | ||
Depois que a query estiver pronta com os nomes dos campos corretos, é só clicar em " | Depois que a query estiver pronta com os nomes dos campos corretos, é só clicar em " | ||
+ | |||
+ | ====Juntar três camadas que se interseccionam==== | ||
+ | |||
+ | Em caso de dúvida, leia primeiro a seção anterior, que explica como juntar duas camadas. O método a seguir faz apenas uma extensão. | ||
+ | |||
+ | <code sql> | ||
+ | -- determina os campos que se quer manter na nova camada | ||
+ | SELECT | ||
+ | tabela1.[nome do campo 1], | ||
+ | avg(tabela1.[nome do campo 2]) as [nome do campo na nova tabela], -- tira a média | ||
+ | sum (tabela2.[nome do campo 3]) as [nome do campo na nova tabela], -- faz a soma | ||
+ | tabela2.[nome do campo 3] , | ||
+ | tabela1.[nome do campo com a geometria que se deseja manter na nova camada ou tabela] -- inclui a geometria de uma das camadas | ||
+ | |||
+ | -- primeira tabela usada como referência para o JOIN | ||
+ | FROM | ||
+ | [tabela 1] | ||
+ | |||
+ | -- JOIN com a camada 2 - define também o tipo de JOIN (INNER JOIN mantém apenas os elementos comuns às duas tabelas | ||
+ | INNER JOIN [nome da camada / tabela 2] | ||
+ | |||
+ | -- Estabelece a condição para unir ou não as linhas. Neste caso, o requisito é espacial | ||
+ | ON ST_Intersects([tabela 1].[campo da geometria], [tabela 2].[campo da geometria]); | ||
+ | |||
+ | INNER JOIN [nome da camada / tabela ] | ||
+ | ON ST_Intersects([tabela 2].[campo da geometria], [tabela 3].[campo da geometria]); | ||
+ | |||
+ | -- Agrupa os resultados pelo id da tabela desejada | ||
+ | GROUP BY tabela1.id | ||
+ | |||
+ | </ | ||
+ | |||
+ | As únicas diferenças são: | ||
+ | * Há mais um INNER JOIN depois do primeiro JOIN. O resultado do primeiro JOIN é imediatamente unido com a outra tabela. | ||
+ | * O GROUP BY, ao final, diz ao computador para agrupar os resultados para cada um dos elementos da tabela 1. Isso é necessário porque esses elementos podem cruzar com mais de um elemento da tabela2, por exemplo, e estamos pedindo | ||
+ | |||
+ | ====Agregar informações de pontos por polígonos==== | ||
+ | |||
+ | Uma primeira operação detecta dentro de qual polígono o ponto está e preenche uma coluna na camada de pontos com o código do setor: | ||
+ | |||
+ | <code sql> | ||
+ | -- Atribui o código do setor a cada registro (linha) de lote. Limite aos Lotes particulares apenas. | ||
+ | create table [nome_da_nova_tabela] as | ||
+ | |||
+ | -- Pega todos os campos da tabela (camada) de pontos | ||
+ | Select [tabela dos pontos].*, codsetor | ||
+ | |||
+ | from [tabela dos pontos] | ||
+ | |||
+ | -- Spatial join baseado na posição: centroides dentros dos polígonos dos setores | ||
+ | join [tabela dos setores] | ||
+ | |||
+ | on st_within([tabela dos pontos].[campo da geometria], [tabela dos setores].[campo da geometria]) | ||
+ | |||
+ | -- Caso deseje restringir as linhas que serão mantidas na nova tabela | ||
+ | where [coluna]=" | ||
+ | </ | ||
+ | |||
+ | Em seguida, usamos a camada de pontos para agregar por setores, usando o código inserido no passo anterior: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | -- Com as informações dos setores nas linhas, faz a agregação por setores | ||
+ | create table [nova_tabela] as | ||
+ | |||
+ | Select | ||
+ | codsetor, -- id do polígono | ||
+ | -- Soma | ||
+ | sum([coluna a ser somada]) as [nome_da_nova_coluna], | ||
+ | |||
+ | -- Soma com uma condição em outra coluna | ||
+ | sum(case when [outra_coluna]=' | ||
+ | |||
+ | from [tabela criada no passo anterior] | ||
+ | |||
+ | group by codsetor | ||
+ | |||
+ | </ | ||
+ | |||
+ | ====Agregar SQL==== | ||
+ | |||
+ | Quando precisar calcular a média ponderada: https:// |
queries_uteis_em_spatialite.1525733205.txt.gz · Última modificação: (edição externa)