Eliminar duplicados na origem, e indexar afa_id

[ppKrauss]

Importante resgatar os itens descartados em outra tabela. Usando

create table optim.consolidated_data_dups AS 
 SELECT rank() OVER (PARTITION BY afa_id ORDER BY house_number desc,geom) as id_rank, *
  from optim.consolidated_data
  where afa_id IN (select afa_id from optim.consolidated_data group by afa_id having count(*)>1)
; --    586

Para remover e inserir, sem risco, usar:

DELETE FROM optim.consolidated_data
  WHERE afa_id IN (select distinct afa_id from optim.consolidated_data_dups)
;
INSERT INTO optim.consolidated_data
  SELECT id,afa_id,via_type, via_name, house_number, postcode, geom_frontparcel, score, geom
  FROM optim.consolidated_data_dups  WHERE id_rank=1
;

---

Indexar afa_id como PK

ALTER TABLE optim.consolidated_data ADD PRIMARY KEY ( afa_id );

Comparar com indexão da geom, ver https://github.com/osm-codes/GGeohash/blob/main/src/step80benchmark-p1.sql