Queries úteis em Spatialite

Juntar várias camadas escolhendo quais campos

Bom para consolidar em uma camada só várias informações provenientes de diversas camadas ou tabelas.

-- Apaga a tabela - só é necessário depois da primeira vez
DROP TABLE "censo_2010_dados_consolidados";

-- Cria uma nova tabela
CREATE TABLE "censo_2010_dados_consolidados" AS 
SELECT 
a.*,b."c-end 1000" as "CrimesPor1000Enderecos",
c."c-pop-1000" as "CrimesPor1000Habitantes",
e."porcresid" as "PorcentagemResidencial",
e."porccomerc" as "PorcentagemComercial",
e."porcnresid" as "PorcentagemNaoResidencial",
e."ginisimpso" as "GiniSimpson",
e."ginitd" as "GiniSimpsonTD",
f."shannon" as "Shannon",
f."shannonNormalized" as "ShannonNormalizada",
f."usos_diferentes1" as "RiquezaDeUsosGerais"

-- Define quais tabelas serão usadas e cria apelidos para elas (a, b, c, ...)
FROM
"setores_acf_2010_medidas_basicas" as a,
"ACF_Base CNEFE_Crimes por endereço" as b,
"Crimes por setor censitário_ACF" as c,
"ACF_setores_censitarios_2010+tamanho_quadras" as d,
"ACF_Base CNEFE_Diversidade" as e,
"census_tracts_cnefe_ACF" as f

-- 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("censo_2010_dados_consolidados", "geom", 31982, 'MULTIPOLYGON','XY');

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.

Na janela SQL do Gerenciador DB executar a seguinte query, fazendo as devidas substituções dos elementos que estão entre colchetes (maiores explicações são dadas adiante):

-- determina os campos que se quer manter na nova camada
SELECT 
[nome do campo 1],
[nome do campo 2] ,
[nome do campo 3] , 
[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
[nome da camada / tabela 1]

-- JOIN com a camada 2 - define também o tipo de JOIN
LEFT 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([nome da camada / tabela 1].[campo da geometria], [nome da camada / tabela 2].[campo da geometria]);

A query faz, em linhas gerais, o seguinte:

1. Indica quais campos devem ser trazidos para a nova camada / tabela;
2. Indica que devem ser consideradas duas tabelas, sendo que as mesmas devem ser unidas linha a linha (JOIN – ver abaixo);
3. Determina que a união (JOIN) deve ser feita com base na intersecção entre as feições das duas camadas;

O JOIN compara linha a linha as tabelas e une as linhas que atendem a algum critério. Quando o JOIN não é espacial, o mais frequente é usar um campo em comum para orientar a união (linhas com o mesmo valor nesse campo em comum são consideradas como se fossem uma única linha na tabela de saída).

O INNER JOIN faz a união e mantém apenas as linhas que atendem ao critério indicado.

O LEFT JOIN faz a união e mantém todas as linhas da primeira tabela. No caso, as que não atendem ao critério terão valores nulos nas colunas que vêm da outra tabela.

O ST_Intersects indica qual é o critério para saber se duas linhas serão unidas em uma só ou não. Ele precisa de dois argumentos: o campo da geometria da camada 1 e o campo da geometria da camada 2. Veja algumas outras opções de análises espaciais aqui. (falta acrescentar)

Sobre o campo da geometria, ver a explicação contida aqui para saber qual nome subtituir na query acima: Spatialite – Campos da geometria

Depois que a query estiver pronta com os nomes dos campos corretos, é só clicar em “Executar”. Depois disso, clicar em “Criar uma vista”, selecionar a coluna de geometria, e depois clicar em “Garregar agora!”.

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.

-- 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 para tirar a média nesse caso. Assim, ele precisa saber quais valores considerar na média. Se não colocarmos nada, ele soma todos os elementos da tabela 1 e divide pela quantidade. Determinando que é pelo id, ele soma para cada elemento da tabela 1 e divide pela quantidade.

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:

-- 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]="Alguma coisa"

Em seguida, usamos a camada de pontos para agregar por setores, usando o código inserido no passo anterior:

-- 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]='algum valor' then [coluna a ser somada] else 0 end ) as [nome_da_nova_coluna]

from [tabela criada no passo anterior]

group by codsetor

Agregar SQL

Quando precisar calcular a média ponderada: https://stackoverflow.com/questions/42586178/sql-server-weighted-average-group-by