CNEFE/Pontos basicos

Ingestão. Ver Carlos, colocou na base IBGE, na tabela ibge_pontos. Tudo text.

create table ibge_pontos_raw (
  COD_UNICO_ENDERECO text,
  COD_UF text, COD_MUNICIPIO text, COD_DISTRITO text, COD_SUBDISTRITO text,
  COD_SETOR text, NUM_QUADRA text, NUM_FACE text, CEP text, DSC_LOCALIDADE text, 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 text, LONGITUDE text, NV_GEO_COORD text, COD_ESPECIE text, DSC_ESTABELECIMENTO text, COD_INDICADOR_ESTAB_ENDERECO text, COD_INDICADOR_CONST_ENDERECO text, COD_INDICADOR_FINALIDADE_CONST text, COD_TIPO_ESPECI text);

ntot_geral=111102875; select count(*) ntot_geral from ibge_pontos_raw;
- reduzido para 110744837 depois do filtro1.

Alvo: apenas endereços

-- drop view vw01_ibge_pontos_raw cascade;
Create table ibge_pontos_raw2 as
SELECT ST_GeoHash(geom,9) as ghs9, * FROM (
 SELECT DISTINCT cod_unico_endereco::bigint as gid,  -- invalido, tem duplicação.
   COD_MUNICIPIO::int AS jurisdic_id, -- cod_ibge
   substring(cod_setor,1,15)::bigint as num_setor, -- melhor que CEP para nome de via unico
   NUM_FACE::int as num_face,
   CEP, DSC_LOCALIDADE AS nsvia,
   trim(regexp_replace(
        CASE WHEN NOM_TIPO_SEGLOGR = 'EDF' THEN '' 
        ELSE COALESCE(NOM_TIPO_SEGLOGR,'') || ' ' || COALESCE(NOM_TITULO_SEGLOGR,'') || ' ' || COALESCE(NOM_SEGLOGR,'')
        END,
        '\s+', ' ','g')) AS via,
   NUM_ENDERECO as hnum,
   ST_SetSRID(ST_MakePoint(LONGITUDE::float,LATITUDE::float),4326) AS geom 
 FROM ibge_pontos_raw
) t WHERE via>'' -- AND geom is not null;  -- nulls não ocorreram
ORDER BY jurisdic_id, 1;
-- ~10 minutos para criar. Sem distinct 109478707 linhas, com distinct  105202888.

a condição NOM_TIPO_SEGLOGR = 'EDF' ocorre apenas em Brasília, em quase a totalidade dos endereços (96% dos casos). Iremos por hora remover Brasília até estabelecer como tratar.

a coondição dsc_modificador='SN' também ocorre com frequência em Brasília, e tem predominância superior a 50% também no estado 52 (Goiás). Provavelmente cidades-satelite de Brasilia, municípios com mais de 90%: 5201405, 5200050, 5208806, 5220454 ... 5204854.

COD_SETOR e NUM_FACE serão substitutos do CEP quando este falhar (tipicamente em cidades com cep unico). Uma alternativa mais segura seria truncando o Geohash, visto que todos eles são meramente para reduzir o risco de rua duplicada.
ntot_geral2=105202888 (95.0% de 110744837 ou 94,7% de 111102875); select count(*) ntot_geral from ibge_pontos_raw2;

Filtros na origem

Filtro 1: eliminação dos endereços com coordenada de muito baixa confiabilidade. O percentual por estado mostra que o impacto é baixo.. Exceto pelo estado 33 onde a perda é de 1%. No futuro vale estudar casos de estados 29, 32, 33, 35 e 41, onde foi igual ou maior que 0.5%. Ideal fazer estudo por município, e avaliação do caso de áreas rurais.

ntot_bad_point=358038 (0.3% de ntot_geral); select count(*) ntot_bad_point from ibge_pontos where NV_GEO_COORD::int>3;

with tot as (select cod_uf, count(*) ntot from ibge_pontos_raw group by 1 order by 1) select *, round((100.0*n::float/ntot)::numeric,2) as perc from ( select t.cod_uf, tot.ntot, count(t.*) n from ibge_pontos_raw t inner join tot ON tot.cod_uf=t.cod_uf where t.NV_GEO_COORD::int>3 group by 1,2) t2 order by 1;

Perfil:

 cod_uf |   ntot   |   n    | perc 
--------+----------+--------+------
 11     |   965370 |    541 | 0.06
 12     |   410524 |    161 | 0.04
 13     |  1658970 |   1975 | 0.12
 14     |   260515 |    617 | 0.24
 15     |  3911170 |   5928 | 0.15
 16     |   312665 |    888 | 0.28
 17     |   848437 |    225 | 0.03
 21     |  3257843 |   5781 | 0.18
 22     |  1891421 |    340 | 0.02
 23     |  4750642 |   8169 | 0.17
 24     |  1845003 |    790 | 0.04
 25     |  2228886 |    108 | 0.00
 26     |  5120482 |   8444 | 0.16
 27     |  1626565 |   1018 | 0.06
 28     |  1258755 |     51 | 0.00
 29     |  9047296 |  38796 | 0.43
 31     | 12038190 |  16214 | 0.13
 32     |  2221348 |  11647 | 0.52
 33     |  8962200 |  91427 | 1.02
 35     | 22953725 | 110180 | 0.48
 41     |  6122025 |  26301 | 0.43
 42     |  4181139 |  10553 | 0.25
 43     |  6456747 |  11158 | 0.17
 50     |  1507819 |   1058 | 0.07
 51     |  1985314 |   1000 | 0.05
 52     |  3960937 |   3779 | 0.10
 53     |  1318887 |    889 | 0.07

Ação: 358038 registtos deletados. DELETE FROM ibge_pontos_raw WHERE nv_geo_coord::int>3;

Filtro 2: eliminação dos endereços com nome de rua nulo. Por definição sem o nome de rua não temos endereço, portanto, como neste momento não tentaremos interpolar ou fazer inferência por outras fontes, a melhor estratégia é remover a informação expúria.

Nota. A condição de nome de edificio foi suficiente.

select count(*) from ibge_pontos_raw where NOM_TIPO_SEGLOGR = 'EDF'-- 1266130

select count(*) from vw01_ibge_pontos_raw where via= or via is null; -- 1266130

Nota sobre Uso do Geohash6 na não-duplicação de nome de rua. movable/geohash estima que Ghs6 tem ~1km2 e Ghs5 da ordem de 25km2. Ghs6 mellhor para pequenos municípios, dentro ou abaixo da mediana Brasil.

select PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY round(sqrt((info->'area_km2')::float)) ) sqside_km_mdn from optim.jurisdiction where jurisd_base_id=76 and isolevel=3 ; -- 20km

.