Diferenças

Aqui você vê as diferenças entre duas revisões dessa página.

--- queries_uteis_em_spatialite [2018/06/09 20:17] – [Juntar três camadas que se interseccionam] rtsaboya
+++ 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 "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');
+</code>
 ====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.
@@ Linha 75: / Linha 112: @@
   * 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.
-====Atribuir código do setor a pontos internos====
+====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>
@@ Linha 95: / Linha 133: @@
 where [coluna]="Alguma coisa"
 </code>
+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]='algum valor' then [coluna a ser somada] else 0 end ) as [nome_da_nova_coluna]
+from [tabela criada no passo anterior]
+group by codsetor
+</code>
+====Agregar SQL====
+Quando precisar calcular a média ponderada: https://stackoverflow.com/questions/42586178/sql-server-weighted-average-group-by