Inserir endereços rurais oficais do IBGE-2017 #16
Comments
CargaDepois de ajustar utf8, CREATE TABLE ibge_agro2017 (
COD_UF int,
COD_MUNICIPIO int,
COD_DISTRITO int,
COD_SUBDISTRITO int,
SITUACAO int,
NOM_TIPO_SEGLOGR text,
NOM_TITULO_SEGLOGR text,
NOM_SEGLOGR text,
NUM_ENDERECO text,
DSC_MODIFICADOR text,
NOM_COMP_ELEM1 text,
VAL_COMP_ELEM1 text,
NOM_COMP_ELEM2 text,
VAL_COMP_ELEM2 text,
NOM_COMP_ELEM3 text,
VAL_COMP_ELEM3 text,
NOM_COMP_ELEM4 text,
VAL_COMP_ELEM4 text,
NOM_COMP_ELEM5 text,
VAL_COMP_ELEM5 text,
LATITUDE double precision,
LONGITUDE double precision,
ALTITUDE real,
DSC_LOCALIDADE text,
COD_ESPECIE int,
CEP bigint
);
COPY ibge_agro2017 FROM '/tmp/35_SAO_PAULO_utf8.csv' DELIMITER ';' CSV HEADER;
-- COPY 188660Contagem por municípioscreate view vw_brcodes_city_ibgesubcod as
select substring(ibge_id::text from 3)::int as ibge_subcod, *
from brcodes_city where uf='SP'
;
create view vw_ibge_agro2017_count1 as
select agro.cod_municipio, brcods.name, count(*) n
from ibge_agro2017 agro inner join vw_brcodes_city_ibgesubcod brcods
ON brcods.uf='SP' AND agro.cod_uf=35 AND agro.cod_municipio=brcods.ibge_subcod
group by 1,2 order by 3 desc
;
select percentile_disc(0.5) within group (order by n) as mediana
from vw_ibge_agro2017_count1; -- 210 ~200
select round(avg(n)) from vw_ibge_agro2017_count1 where n<=210; -- 111 ~100
select round(avg(n)) from vw_ibge_agro2017_count1 where n>210; -- 479 ~500Conclusão: metade dos municipios de SP não tiveram mais do que 200 endereços contabilizados, conforme valor da mediana. A metade ruim tem em média 100 endereços rurais, a metade boa tem em média 500 endereços. Amostra dos extremos superior e inferior:
Contagem de duplicados por geohashPrimeiramente avaliando os pontos duplicados, ou seja, com resolução inferior a 1m, sendo igual é o mesmo ponto. create view vw_ibge_agro2017_grby_geohashes AS
SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(longitude,latitude),4326),11) as geohash,
count(*) n
from ibge_agro2017 group by 1 order by 2 desc;
select count(*) n_dups, sum(n) lines from vw_ibge_agro2017_grby_geohashes where n>1;
-- 594 | 1338Conclusão: o IBGE pode ter contabilizado ~1300 famílias morando em "cojunto habitacional" compostos de duas ou mais casas. No total teriam sido da ordem de 600 conjuntos desse tipo. Para confirmar a hipótese basta verificar as imagens de satélite dos conjuntos com maior número de elementos:
Em seguida avaliando os pontos duplicados com resolução inferior a 25m (geohash de 8 dígitos), tendo em vista que se esperam habitações mais esparças no meio rural. -- idem anterior SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(longitude,latitude),4326),8)
-- n_dups= 2650 | rows=5653Análise de amostras, imagem BING de hoje:
Contagem de endereços por macrocélula geohash-- idem anterior , usando 4 dígitos
create view vw_ibge_agro2017_grby_geohashes4 AS
SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(longitude,latitude),4326),4) as geohash,
count(*) n
from ibge_agro2017 group by 1 order by 2 desc;
select count(*) n_dups, sum(n) lines from vw_ibge_agro2017_grby_geohashes4 where n>1;
-- n_dups=410 | lines =188656
select percentile_disc(0.5) within group (order by n) as mediana
from vw_ibge_agro2017_grby_geohashes4 where n>10; -- 410 ~400
select round(avg(n)) from vw_ibge_agro2017_grby_geohashes4 where n<=410; -- 210 ~200
select round(avg(n)) from vw_ibge_agro2017_grby_geohashes4 where n>410; -- 743 ~750Aqui o número de macrocélulas Geohash é da mesma ordem de grandeza que o número de municípios, mas a área das células é uniforme (da ordem de 700 km²), de modo que podemos esperar uma densidade mais uniforme. Também podemos descartar macrocélulas com menos de 10 residências (foram descartadas 14).
A mediana de n dessa listagem é ~400. A média acima é 750, abaixo é 200. O resultado sugere que o trabalho seja realizado inicialmente por quadrantes de maior n, e depois fechamentos por municípios. |
ppKrauss
added
the
melhora
|
Testando com Curitiba. São 123 registros completos (com CEP) no arquivo IBGE correspondente, COPY ibge_agro2017 FROM '/tmp/4106902_CURITIBA_utf8.csv' DELIMITER ';' CSV HEADER;
-- ...
CREATE VIEW vw_ibge_agro2017_curitiba AS
SELECT st_geohash(ST_Point(longitude,latitude),9) geohash,
nom_tipo_seglogr,
nom_seglogr,
regexp_replace(num_endereco,'\.0$','') num_endereco,
cep
FROM ibge_agro2017
WHERE cod_uf=41 and num_endereco>''
ORDER BY 1
; -- 105 linhas |
ppKrauss
added
the
AddressForAll
Dados do Censo Agropecuário de 2017, com download disponível nesta URL ibge.gov.br/estatisticas-novoportal/economicas/agricultura-e-pecuaria (
ftp://ftp.ibge.gov.br/Censo_Agropecuario/Censo_Agropecuario_2017/Cadastro_Nacional_de_Enderecos_Fins_Estatisticos/35_SAO_PAULO.zip).Conferindo dados e redundância na fonte
Aparentemente ambígua e redundante a fonte não descreve se há diferença entre nomes de arquivo que se repetem apesar de estarem sub diretórios diferentes, aparentemente para cumprir finalidades diferentes. Baixando por exemplo duas versões de
35_SAO_PAULO.zip. Teste por SHA1:VERIFICAÇÃO: ambos com mesmo tamanho de arquivo (17M) e mesmo SHA1
sha1sum be6a4804087257ef0c67167106d4b0aeee3a3731 35_SAO_PAULO.zip. Conclusão: idênticos.Dentro do zip conferindo
35_SAO_PAULO/3522307_ITAPETININGA.csvque possui os dados duplicados em mais 2 arquivos:2.1.
35_SAO_PAULO/35_SAO_PAULO.csv, supostamente contendo mesmo cabeçalho e todas as linhas de dados de3522307_ITAPETININGA.csv.TESTE: cabeçalho CSV é o mesmo. Amostras de dados com exatamente as mesmas linhas. Grande chance de ser o mesmo. Verificando número de linhas:
grep "35;22307;" 35_SAO_PAULO/3522307_ITAPETININGA.csv | wc -lresulta 1291. Idemgrep "35;22307;" 35_SAO_PAULO/35_SAO_PAULO.csv | wc -l. Conclusão: pode-se supor idêntico, pois há também suposta intenção do IBGE em apenas oferecer opções de formato.2.2.
35_SAO_PAULO/3522307_ITAPETININGA.kml, supostamente mesmos dados convertidos para formato XML KML.TESTE: campos do cabeçalho CSV coincidem com nomes listados pelo XPath
/kml/Document/Schema/SimpleField/@name. Quanto ao conteúdo, breve amostragem de 3 mostrou que são mesmos valores. Quanto à quantidade de items,grep "<Placemark>" 35_SAO_PAULO/3522307_ITAPETININGA.kml | wc -lresulta em 1290. Apenas um a menos do que o esperado, cabe conferir se havia no CSV alguma linha vazia cuja cópia seria inválida no formato KLM.Conclusão: por se tratarem de dados tabulares simples (a geometria está precisamente determinada plas colunas latitude e longitude), mais simples e rápido carregar para o PostgreSQL via CSV global, com o Estado de SP inteiro,
35_SAO_PAULO.csv.The text was updated successfully, but these errors were encountered: