Install City codes para testes e produção

O dataset https://datasets.ok.org.br/city-codes é um dos "core datasets" em ambos, Projeto OSMcodes-BR e Projeto AddressForAll-BR. Em geral a ingestão se dá numa das bases de ingestão, por exemplo ingest1, de modo que seu conteúdo pode também ser normalizado como tabela city do esquema ingest de transferência.

Ver complementação com OSM_ID por https://github.com/datasets-br/city-codes/wiki/Ingest%C3%A3o-dump_wikidata-no-PostgreSQL

Preparo prévio

No filesystem basta fazer a carga do arquivo CSV:

  # mkdir -p /tmp/pg_io; chmod 777 /tmp/pg_io # para quem não criou 
  cd /tmp/pg_io;
  rm br-city-codes*.*
  wget https://github.com/datasets-br/city-codes/raw/master/data/br-city-codes.csv
  # wget -O br-city-codes-datapackage.json https://github.com/datasets-br/city-codes/raw/master/datapackage.json

Na base, podemos supor psql "postgres://postgres@localhost/dl03t_main", e que nela já estejam instaladas as extensions mais comumente utilizadas do projeto, ou seja, rodar inicializações SQL:

-- CREATE extension IF NOT EXISTS postgis;
CREATE extension IF NOT EXISTS adminpack;

CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER    IF NOT EXISTS files FOREIGN DATA WRAPPER file_fdw;

CREATE schema    IF NOT EXISTS ingest;
CREATE schema    IF NOT EXISTS tmp_orig;
CREATE schema    IF NOT EXISTS api;
CREATE schema	 IF NOT EXISTS optim;


-- -- -- -- -- --
-- inicializações de tabela e leitor de dataset:

CREATE TABLE ingest.city ( -- only current city
  ibge_id int   NOT NULL PRIMARY KEY, -- BR official id, control
  name    text  NOT NULL, --CHECK(length(name)<60), -- admin-level3
  state   text  NOT NULL, -- CHECK(length(name)=2), -- UF, admin-level2
  abbrev3 text,  --CHECK(length(name)<=3),
  wikidata_id  bigint,  --  from '^Q\d+'
  lexlabel     text NOT NULL,  -- cache from name. 'sao.paulo'
  isolabel_ext text NOT NULL,  -- cache from name and state. BR-SP-SaoPaulo
  ddd          integer,
  info JSONb, -- postalCode_ranges, notes,   creation, extinction
  UNIQUE(state,name),
  UNIQUE(state,lexlabel)
);

CREATE FOREIGN TABLE tmp_orig.fdw_br_city_codes (
  name text,
  state text,
  "wdId" text,
  "idIBGE" int,
  "lexLabel" text,
  creation integer,
  extinction integer,
  "postalCode_ranges" text,
  ddd integer,
  abbrev3 text,
  notes text
) SERVER files OPTIONS (
   filename '/tmp/pg_io/br-city-codes.csv'
   ,format 'csv'
   ,delimiter ','
   ,header 'true'
);
-- conferir CSV com 
-- SELECT * FROM tmp_orig.fdw_br_city_codes LIMIT 10;

CREATE or replace FUNCTION lexname_to_unix(p_lexname text) RETURNS text AS $$
  SELECT string_agg(initcap(p),'') FROM regexp_split_to_table($1,'\.') t(p)
$$ LANGUAGE SQL IMMUTABLE;

Ingestão ou refresh

O problema na atualização de tabelas core é que não dá para atualizar com simples DROP nem DELETE/INSERT. É preciso fazer um UPDATE bem controlado para não impactar nas demais tabelas e views. Abaixo supondo carga inicial, onde esse impacto não existe:

INSERT INTO ingest.city(ibge_id,name,state,abbrev3,wikidata_id,lexlabel,isolabel_ext,ddd,info)
  SELECT   "idIBGE"::int, name,
            state, abbrev3, -- upper
            substr("wdId",2)::bigint,
            "lexLabel",
            'BR-'||state||'-'||lexname_to_unix("lexLabel"),
            ddd,
            jsonb_build_object(
              'postalCode_ranges',"postalCode_ranges",
              'notes',notes,
              'creation',creation,
              'extinction',extinction
            ) AS  info
  FROM tmp_orig.fdw_br_city_codes
ON CONFLICT DO NOTHING
; -- 5570 municípios em agosto de 2020.

Para levar isso da ingestão para a tabela final optim.city é preciso fazer os devidos UPDATES... Mas estamos supondo a carga inicial, de modo que basta fazer ALTER TABLE mudando schema para optim ou clonando a tabela. Cabe lembrar que "não existe milagre dos clones", mas como é um projeto bem controlado, existe o milagre do makefile que gera as estruturas automaticamente. O que o make faz é repetir o CREATE TABLE para optim e dar um insert.

CREATE TABLE optim.city ( -- only current city
  ibge_id int   NOT NULL PRIMARY KEY, -- BR official id, control
  name    text  NOT NULL, --CHECK(length(name)<60), -- admin-level3
  state   text  NOT NULL, -- CHECK(length(name)=2), -- UF, admin-level2
  abbrev3 text,  --CHECK(length(name)<=3),
  wikidata_id  bigint,  --  from '^Q\d+'
  lexlabel     text NOT NULL,  -- cache from name. 'sao.paulo'
  isolabel_ext text NOT NULL,  -- cache from name and state. BR-SP-SaoPaulo
  ddd          integer,
  info JSONb, -- postalCode_ranges, notes,   creation, extinction
  UNIQUE(state,name),
  UNIQUE(state,lexlabel)
);
-- IF EMPTY:
INSERT INTO optim.city SELECT * FROM ingest.city;