exemplo 1 de Makefile, endereços de BH

-- CSV file of id_origin 5175, pack_id 12, hash 1ce29a555565be5f540ab0c6f93ac55797c368293e0a6bfb479a645a5a23f542
  -- select ingest.fdw_generate_getCSV('enderecos','br_pk012');
  --   tmp_orig.fdw_enderecos_br_pk012 was created!+
  --   source: /tmp/pg_io/br_pk012-enderecos.csv 

SELECT *, n_geom-n_l AS n_used  FROM (
  SELECT count(*) n, COUNT(distinct GEOMETRIA) n_geom, count(LETRA_IMOVEL) n_l 
  FROM tmp_orig.fdw_enderecos_br_pk012
) t; -- 735894 | 732755 | 137559 | 595196

SELECT array_agg(distinct SIGLA_TIPO_LOGRADOURO) FROM tmp_orig.fdw_enderecos_br_pk012;
--  {ACS,ALA,AVE,BEC,ELP,EST,LRG,PCA,RDP,ROD,RUA,TRE,TRI,TRV,VDP,VDT,VIA}

---------- INSERE:
WITH prep AS (
    SELECT 12 AS pack_id, 
         SIGLA_TIPO_LOGRADOURO ||
            CASE WHEN SIGLA_TIPO_LOGRADOURO IN ('RUA','VIA') THEN ' ' ELSE '. ' END ||
            NOME_LOGRADOURO AS street_name,
         NUMERO_IMOVEL || COALESCE('-'||LETRA_IMOVEL,'') AS house_number,
         ST_Transform( ST_SetSRID(GEOMETRIA::geometry, 31983), 4326) as geom,
         LETRA_IMOVEL
    FROM tmp_orig.fdw_enderecos_br_pk012
    -- WHERE LETRA_IMOVEL is null -- 579018
), prep_dup_geom AS (  -- Geometrias duplicadas:
   select geom FROM prep GROUP BY 1 HAVING count(*)>1
), prep_dup_addr AS (
   -- Endereços rua_e_numero não-vizinhos duplicados:
   SELECT * FROM (SELECT sthn, round(sqrt(st_area(ST_Envelope(u),true))) as avg_dist, n
   FROM (
     SELECT street_name||house_number as sthn, 
            st_union(geom) as u, count(*) n
     FROM prep GROUP BY 1 HAVING count(*)>1
   ) t1 ) t2
   WHERE (n>2 AND avg_dist>500) OR (n=2 AND avg_dist>3000)
)
  INSERT INTO ingest.addr_point (pack_id,vianame,housenum,geom)
    -- Main set of items:
    SELECT pack_id,street_name, house_number, geom
    FROM prep
    WHERE geom NOT IN (select geom FROM prep_dup_geom) 
          AND  street_name||house_number NOT IN (SELECT sthn FROM prep_dup_addr)
    UNION 
    -- Sample of duplicated geoms:
    SELECT pack_id,street_name, house_number, geom
    FROM prep
    WHERE LETRA_IMOVEL IS NULL AND geom IN  (select geom FROM prep GROUP BY 1 HAVING count(*)>1)
          AND  street_name||house_number NOT IN (SELECT sthn FROM prep_dup_addr)
    UNION
    -- Duplicated address:
    SELECT pack_id,street_name || ' zona_'||st_geohash(geom,6), house_number, geom
    FROM prep
    WHERE  street_name||house_number IN (SELECT sthn FROM prep_dup_addr)
    
  ON CONFLICT  DO NOTHING -- 715795; union up to 724697 but 723764 deleting dups.
;
-- test:
SELECT pack_id, ghs, n, round(n*1000000/area) as n_km2 FROM 
  (SELECT pack_id, st_geohash(geom,4) ghs, count(*) n from ingest.addr_point group by 1,2 order by 1,2) t,
  LATERAL  (SELECT ST_AREA(ST_GeomFromGeoHash(t.ghs),true) as area) t2
;
--   select distinct streetname from ingest.addr_point order by 1;

Caracterização do pacote

n	n_geom	n_l	n_perfect
735894	732755	137559	595196

pack_id	ghs	n	n_km2
12	7h2w	322312	450
12	7h2x	127453	178
12	7h2y	169285	236
12	7h2z	104714	146m

Das 732755 geometrias não-duplicadas, 723764 foram efetivamente usadas como ponto de endereço completo.

Perfil das duplicações

SELECT SIGLA_TIPO_LOGRADOURO ||'. '|| NOME_LOGRADOURO AS street_name, NUMERO_IMOVEL,
       count(*) n, count(LETRA_IMOVEL) n_l
from tmp_orig.fdw_enderecos_br_pk012 group by 1,2 having count(LETRA_IMOVEL)>0
order by 3 desc, 1,2
limit 100;

street_name	numero_imovel	n	n_l
RUA. LOURIVAL AMBROSIO BALBINO	94	26	25
VDP. SEM NOME	5	24	1
RUA. ITABIRA	413	20	19
VDP. SEM NOME	10	17	3
RUA. PADRE TIAGO DE ALMEIDA	182	16	15
RUA. PORANGA	180	16	15
BEC. SANTA LUZIA	40	15	9
RUA. CALDAS DA RAINHA	650	15	14
RUA. ESTRADA NOVA	200	15	14
RUA. PADRE TIAGO DE ALMEIDA	202	15	14
RUA. RAUL SEIXAS	460	15	14
RUA. TRES	50	15	4

Tipos destacados:

• Desmembrado: mesmo endereço de rua, diversos complementos diferentes. Caracterizado por n-n_l ≈ 1 e n_l>0. Exemplo: "RUA ITABIRA, 413"; "RUA ITABIRA, 413-A"; "RUA ITABIRA, 413-B"; etc.
  O algorito de localização por interpolação é o mesmo que endereço-complemento utilizado em condomínios horizontais, ou seja, localiza-se apenas o portão principal do condomínio. Neste caso, todavia, com 137559/735894=19% dos endereços com letra, o município deve ter seu sistema de numeracao devidamente classificado e configurado para aceitar os desmembramentos.

• Informal: caracterizado por endereço com nome de rua informal (invalido!), repetindo em diversos bairros ou loteamentos. Como consequência n_l=0 ou n/n_l>50%. Exemplos: "VDP. SEM NOME, 5"; "RUA TRES, 50".
  Para remover as duplicações em nomesde rua foi acrescentado o sufixo " zona" e Geohash de 5 dígitos relativo ao ponto de endereço.

---

Resolucao das siglas

SELECT SIGLA_TIPO_LOGRADOURO, 
   COUNT(*) n, count(distinct NOME_LOGRADOURO) ndist,
   (array_agg(distinct NOME_LOGRADOURO))[1] 
FROM tmp_orig.fdw_enderecos_br_pk012 
group by 1 order by 1;

SIGLA	n	n_nomes	amostra de nome
ACS	1	1	DOIS MIL QUATROCENTOS E TRINTA E UM
ALA	1977	53	ABIURANA
AVE	49640	305	MINISTRO GUILHERMINO DE OLIVEIRA
BEC	33489	1548	CAETANO
ELP	2	1	UM MIL QUATROCENTOS E TRINTA E CINCO
EST	714	9	ANTIGA PARA LAGOA SANTA
LRG	25	5	DA CASTANHEIRA
PCA	2808	391	ABADIA
RDP	49	12	J
ROD	2056	7	ANEL RODOVIARIO CELSO MELLO AZEVEDO
RUA	642169	10345	AGUINALDO DE OLIVEIRA MANGEROTTI
TRE	30	6	ANEL RODOVIARIO X CRISTIANO MACHADO
TRI	4	2	HORACIO DE MIRANDA PEREIRA
TRV	2020	167	ADAO
VDP	814	96	UM MIL E QUARENTA E QUATRO
VDT	14	7	ENGENHEIRO ANDRADE PINTO
VIA	82	2	GERALDO DIAS

Segundo a VIII - Tipo de logradouro, https://fazenda.pbh.gov.br/ISS/cmc/preenchforms.htm

• ELP = ?
• EST = ESTRADA
• RDP = RUA DE PEDESTRE
• TRI = TRINCHEIRA
• VDP = VIA DE PEDESTRE
• VDT = VIADUTO

---

Conferindo nomes de rua duplicados:

-- LISTA DE PARES DUPLICADOS:
SELECT pack_id, vianame, housenum
FROM  ingest.addr_point
WHERE  vianame like '%zona_%'
ORDER BY REGEXP_REPLACE(vianame,' zona_.+',''), housenum, vianame
;
-- LISTA DE NOMES DE RUA SUPOSTAMENTE DUPLICADOS:
SELECT pack_id, REGEXP_REPLACE(vianame,' zona_.+','') AS vianame, COUNT(*) n
FROM  ingest.addr_point
WHERE  vianame like '%zona_%'
GROUP BY 1,2
HAVING COUNT(*)>2
ORDER BY 3 desc, 1,2
;

Na listagem abaixo cada nome de rua com "endereço duplicado porém distante" foi acrescido de um Geohash com prefixo "zona_". Repare que na maior parte são duas ou mais linhas com mesmo nome de rua e mesma numeração predial. Foram filtradas duplicações com pontos a mais de 500 metros um do outro, conforme subquery prep_dup_addr da ingestão.

pack_id	vianame	housenum
12	AVE. A zona_7h2wn5	130
12	AVE. A zona_7h2z30	130
12	AVE. A zona_7h2wn5	146
12	AVE. A zona_7h2z30	146
12	AVE. GUARATAN zona_7h2wx4	30
12	AVE. GUARATAN zona_7h2wx5	30
12	AVE. GUARATAN zona_7h2wxh	30
12	AVE. SENADOR LEVINDO COELHO zona_7h2wmc	35
12	AVE. TERESA CRISTINA zona_7h2wqk	21
12	AVE. TERESA CRISTINA zona_7h2wxm	21
12	AVE. TERESA CRISTINA zona_7h2wqx	256
12	AVE. TERESA CRISTINA zona_7h2wxt	256
12	AVE. VEREADOR CICERO ILDEFONSO zona_7h2wws	937
12	AVE. VEREADOR CICERO ILDEFONSO zona_7h2y8r	937
12	AVE. WALDYR SOEIRO EMRICH zona_7h2wq9	135
12	AVE. WALDYR SOEIRO EMRICH zona_7h2wqd	135
12	AVE. WALDYR SOEIRO EMRICH zona_7h2wqu	135
12	BEC. A zona_7h2wm9	10
12	BEC. A zona_7h2wnh	10
12	BEC. A zona_7h2wnp	10
12	BEC. A zona_7h2ww8	10
12	BEC. A zona_7h2wx8	10
12	BEC. A zona_7h2xnz	10
12	...	...
12	VDP. SEM NOME zona_7h2y8g	60
12	VDP. SEM NOME zona_7h2wrx	7
12	VDP. SEM NOME zona_7h2wx9	7
12	VDP. SEM NOME zona_7h2xre	75
12	VDP. SEM NOME zona_7h2y8g	75
12	(13461 rows)

São ~13 duplicações de rua-numero, portanto pode-se supor que boa parte dessas suplicações sejam devidas à duplicação de nomes de rua informais, tipicamente em lotes ainda não formalizados, onde constam apenas nomes como "RUA UM", "RUA A", etc. A listagem abaixo indica o número n de housenumbers duplicados:

pack_id	vianame	n
12	RUA DOIS	631
12	RUA TRES	469
12	RUA UM	438
12	RUA QUATRO	411
12	RUA SAO SEBASTIAO	236
12	BEC. SAO JOSE	192
12	BEC. DA PAZ	164
12	RUA JOANA D'ARC	157
12	RUA C	151
12	BEC. SEM NOME	150
12	BEC. B	144
12	BEC. DAS FLORES	143
12	RUA FLOR DO CAMPO	143
12	RUA A	136
12	RUA D	129
12	RUA OITO	129
12	ROD. ANEL RODOVIARIO CELSO MELLO AZEVEDO	125
12	RUA SETE	122
12	RUA DA PAZ	121
12	RUA CINCO	114
12	RUA SAO VICENTE	114
12	BEC. SAO JORGE	109
12	RUA B	109
...	...	...
12	RUA JEQUITIBA	3
12	RUA PINHEIROS	3
12	RUA VINTE E SETE	3
12	(411 rows)

Alguns nomes de rua como "RUA SAO SEBASTIAO" ou "BEC. SAO JOSE" provavelmente foram duplicados por incompetência da Câmara Municipal; outros por falha ao associar mais de um ponto a um mesmo lote (portanto mesmo endereço oficial).