Loading data to SQL - datasets-br/city-codes GitHub Wiki

The EXTENSION file_fdw, table tmpcsv_state_codes, etc. was generated by preparing scripts...

Genereal prepare:

          CREATE EXTENSION file_fdw;
          CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;

Loading and saving the city-codes

wget -O /tmp/br_city_codes.csv -c https://raw.githubusercontent.com/datasets-br/city-codes/master/data/br-city-codes.csv

After it, add the script below to get the table:

	  DROP FOREIGN TABLE IF EXISTS tmpcsv_br_city_codes CASCADE;
	  CREATE FOREIGN TABLE tmpcsv_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/br_city_codes.csv', 
	     format 'csv', 
	     header 'true'
	  );

-- To optimize indexing, etc.
-- CREATE MATERIALIZED VIEW mvw_br_city_codes AS SELECT * FROM  tmpcsv_br_city_codes;
-- CREATE INDEX idx_citybr ON mvw_br_city_codes("idIBGE");

-- To alter table with new columns, etc.
--CREATE TABLE br_city_codes AS SELECT * FROM  tmpcsv_br_city_codes;
--ALTER TABLE br_city_codes ADD COLUMN abbrev3 text;
-- UPDATE br_city_codes SET abbrev3=abbrev FROM tmpcsv_anatel2013_abrevs3 a 
-- WHERE a.state=br_city_codes.state AND a.ddd=br_city_codes.ddd 
--  AND name2lex(unaccent(lower(a.name)))=br_city_codes."lexLabel";

-- copy back to CSV:
COPY (
  select name, state, "wdId", "idIBGE", "lexLabel", creation, extinction,
     "postalCode_ranges", ddd, abbrev3, notes  
   from br_city_codes ORDER BY std_collate(name), name, state 
) to '/tmp/atualizacao.csv' CSV HEADER;

Loading external data

The EXTENSION file_fdw, table tmpcsv_state_codes, etc. was generated by preparing scripts...

After it, add the script below to get non-usual tables:

CREATE FOREIGN TABLE tmpcsv_cities_wiki (
  state text, name text, wdid text
) SERVER csv_files OPTIONS (
     filename '/tmp/cities_from_wiki.csv',
       format 'csv',
       header 'true'
);

CREATE FOREIGN TABLE tmpcsv_ibge_cities (
-- From https://ww2.ibge.gov.br/home/geociencias/geografia/redes_fluxos/gestao_do_territorio_2014/base.shtm
-- ftp://geoftp.ibge.gov.br/organizacao_do_territorio/redes_e_fluxos_geograficos/gestao_do_territorio/bases_de_dados/ods/Base_de_dados_dos_municipios.ods
--  Editar planilha deletando demais colunas.
	UF text,
	CodUF text,
	Codmun text,
	NomeMunic text,
	VAR03 text
)   SERVER csv_files OPTIONS ( 
     filename '/tmp/ibge_municipios.csv', --change
       format 'csv', 
       header 'true'
);

----
-- Anatel 3 letras
-- after cp data/dump_etc/anatel2013-abrevs3letras.csv /tmp
CREATE FOREIGN TABLE tmpcsv_anatel2013_abrevs3 (
  state text, abbrev text, name text, ddd int
) SERVER csv_files OPTIONS (
     filename '/tmp/anatel2013-abrevs3letras.csv',
       format 'csv',
       header 'false'
);