CNEFE/Pontos basicos: mudanças entre as edições
< CNEFE
Sem resumo de edição |
mSem resumo de edição |
||
Linha 18: | Linha 18: | ||
-- drop view vw01_ibge_pontos_raw cascade; | -- drop view vw01_ibge_pontos_raw cascade; | ||
Create table ibge_pontos_raw2 as | Create table ibge_pontos_raw2 as | ||
SELECT * FROM ( | SELECT ST_GeoHash(geom,9) as ghs9, * FROM ( | ||
SELECT cod_unico_endereco::bigint as gid, -- invalido, tem duplicação. | SELECT DISTINCT cod_unico_endereco::bigint as gid, -- invalido, tem duplicação. | ||
COD_MUNICIPIO::int AS jurisdic_id, -- cod_ibge | 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 | substring(cod_setor,1,15)::bigint as num_setor, -- melhor que CEP para nome de via unico | ||
Linha 32: | Linha 32: | ||
ST_SetSRID(ST_MakePoint(LONGITUDE::float,LATITUDE::float),4326) AS geom | ST_SetSRID(ST_MakePoint(LONGITUDE::float,LATITUDE::float),4326) AS geom | ||
FROM ibge_pontos_raw | FROM ibge_pontos_raw | ||
) t WHERE via>'' | ) 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. | |||
</syntaxhighlight> | </syntaxhighlight> | ||
* a condição <code>NOM_TIPO_SEGLOGR = 'EDF'</code> ocorre | |||
* a condição <code>NOM_TIPO_SEGLOGR = 'EDF'</code> 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 <code>dsc_modificador='SN'</code> 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. | * 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); <code>select count(*) ntot_geral from ibge_pontos_raw2;</code> | |||
== Filtros na origem == | == Filtros na origem == | ||
Linha 85: | Linha 91: | ||
: select count(*) from ibge_pontos_raw where NOM_TIPO_SEGLOGR = 'EDF'-- 1266130 | : 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 | : 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. [https://www.movable-type.co.uk/scripts/geohash.html 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. <code>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 </code>. | : Nota sobre Uso do Geohash6 na não-duplicação de nome de rua. [https://www.movable-type.co.uk/scripts/geohash.html 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. <code>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 </code>. |
Edição atual tal como às 11h20min de 9 de setembro de 2024
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
.