Inserir endereços rurais oficais do IBGE-2017

ppKrauss commented 5 years ago

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.csv que 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 de 3522307_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 -l resulta 1291. Idem grep "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 -l resulta 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.

ppKrauss commented 5 years ago

Carga

Depois de ajustar utf8, iconv -f "ISO8859-1" -t "UTF-8" 35_SAO_PAULO.csv > /tmp/35_SAO_PAULO_utf8.csv, se a tabela ainda não foi criada, criar, e copiar o estado.

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 188660

Contagem por municípios

create 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 ~500

Conclusã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:

cod_municipio	name	n
13603	Cunha	2287
30201	Mirante do Paranapanema	2097
50209	São Miguel Arcanjo	1916
52106	Socorro	1749
37800	Piedade	1723
30607	Mogi das Cruzes	1379
22307	Itapetininga	1291
8702	Caconde	1262
19709	Ibiúna	1240
22406	Itapeva	1231
11003	Castilho	1199
53401	Tanabi	1062
41604	Promissão	1058
2804	Araçatuba	1021
30102	Mirandópolis	1013
24808	Jales	1012
54300	Teodoro Sampaio	976
...	...	...
2754	Araçariguama	10
18701	Guarujá	9
56503	Várzea Paulista	8
1152	Alumínio	8
51009	São Vicente	7
39806	Poá	7
20426	Ilha Comprida	6
44103	Rio Grande da Serra	5
48807	São Caetano do Sul	5
22505	Itapevi	5
13504	Cubatão	3
52809	Taboão da Serra	2
6359	Bertioga	1
48500	Santos	1

Contagem de duplicados por geohash

Primeiramente 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 |  1338

Conclusã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:

geohash	n
6un2yvus3h3	16 *
6gyrszr166v	9
6gur68vfwbc	7
6gykfxmx0cv	6
6gyyy2c3jpk	5
6gur62n9g6g	5
6ujq3gx014j	5 *
6u5t64vhu3t	4
6gur68vfwh2	4
6ujg8fj11ju	4 *

itens conferidos.

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=5653

Análise de amostras, imagem BING de hoje:

localizações como 6gyzefvw (6 linhas duplicadas) caem em vias de acesso de pequenos conjuntos de casas (2 a 10) ...
localizações como 6ujq3gx0 (6 linhas duplicadas) caem em via de acesso a uma casa só...
localizações como 6un2yvus ou 6un2yz2z com mais de 15 linhas cada, estão "caindo no vazio" e são vizinhos (~20 metros) de conjuntos urbanos.
Pode ter acontecido de ser acentamentos rurais em 2017 que agora não existem mais.

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 ~750

Aqui 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).

geohash	n
6gyz	1872
6u7u	1665
6gyy	1629
6gvb	1537
6gz1	1537
6u7v	1474
6u7g	1421
6gtq	1354
...	...
7h00	30
6gz8	28
6gsg	26
6gzc	20
6gzp	19
6gsv	17
6ukp	15
6gz0	10
6gtt	10
6upb	10

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 commented 4 years ago

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

OSMBrasil / stable