Skip to content

Upload e integridade dos dados originais

Jump to bottom
Peter edited this page Aug 7, 2020 · 14 revisions

A seguir passo a passo didático que deu origem ao algoritmo "automatizador de ingestão".

Carga para o servidor

Os arquivos precisam ser distribuídos, via FTP ou SFTP, por pastas do município. Por exemplo, com ls /home/igor/ obtemos a listagem das pastas organizadas pelo Igor em junho de 2020:

BR-ES-Serra          BR-PR-Cascavel        BR-RS-PortoAlegre  BR-SP-LaranjalPaulista  BR-SP-SaoVicente
BR-ES-VilaVelha      BR-PR-Pinhais         BR-RS-SantaMaria   BR-SP-Osasco            BR-SP-Sorocaba
BR-ES-Vitoria        BR-PR-SaoJosePinhais  BR-SC-JaraguaSul   BR-SP-Santos
BR-MG-BeloHorizonte  BR-RJ-Niteroi         BR-SP-Itu          BR-SP-SaoBernardoCampo
BR-PE-Recife         BR-RS-Gravatai        BR-SP-Jundiai      BR-SP-SaoPaulo

Dentro de cada uma delas são mantidos os arquivos de input. Por exemplo:

ls /home/igor/BR-RS-PortoAlegre/input
# SMF-XLSX-ORIGINAIS.zip

Avaliação dos arquivos carregados:

Os arquivos de carga podem ser verificados diretamente pelo PostgreSQL, conforme a função ingest.cityfolder_input_files definida abaixo

CREATE or replace FUNCTION ingest.cityfolder_input_files(
  p_fpath text DEFAULT '/tmp/pg_io/'
) RETURNS TABLE (fid int, cityname text, fname text, is_validext boolean, fmeta jsonb) AS $f$
  WITH t0 AS ( SELECT rtrim(p_fpath,'/') AS fpath )
  , t1 AS (
    SELECT f as cityname,
           t0.fpath ||'/'|| f as f
    FROM pg_ls_dir((SELECT fpath FROM t0)) t(f), t0
    WHERE    f ~ '^BR\-[A-Z]{2,2}\-[A-Za-z]+$'
  )
  SELECT (row_number() OVER ())::int id,
         cityname, fname,
         fname ~* '\.(zip|gz|rar|geojson|csv|dwg)$' as is_validext,
         to_jsonb( pg_stat_file(fpath||'/'||fname) ) || jsonb_build_object('fpath',fpath)
  FROM (
    SELECT cityname, 
           f||'/'||'input' as fpath,
           pg_ls_dir(f||'/'||'input') as fname
    FROM t1
    ORDER BY 1,3
  ) t2
$f$  LANGUAGE SQL IMMUTABLE;
-- Exemplo:
SELECT cityname, fname, is_validext, fmeta->>'size' as bytes 
FROM ingest.cityfolder_input_files('/home/igor');

No exemplo, rodando em 1 de julho de 2020, foram listados 243 arquivos válidos:

cityname fname is_validext bytes
BR-ES-Serra 2018-01-05 BaseCartográfica.gdb.zip t 24218623
BR-ES-VilaVelha BAIRROS.zip t 68069
BR-ES-VilaVelha LOTES.zip t 6282461
BR-ES-VilaVelha QUADRAS.zip t 2327148
... ... ... ...
BR-SP-SaoPaulo SIRGAS_SHP_quadraviariaed.zip t 70291605
BR-SP-SaoPaulo SIRGAS_SHP_subprefeitura.zip t 1188797
BR-SP-SaoVicente MAPA DE LOTEAMENTOS.dwg t 22665728
BR-SP-Sorocaba lotes_prediais.zip t 14476669
BR-SP-Sorocaba shape_eixos_sorocaba.zip t 1880173

RESUMO DOS ARQUIVOS VÁLIDOS POR MUNICÍPIO:

SELECT cityname, COUNT(*) as n_files,
       sum(bytes/1048576) as "tot MiB",
       ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY bytes) /1048576 ) as "median MiB"
FROM (
  SELECT cityname, (fmeta->'size')::int as bytes 
  FROM ingest.cityfolder_input_files('/home/igor')
  WHERE is_validExt
) t
GROUP BY 1;
cityname n_files tot MiB median MiB
BR-ES-Serra 1 23 23
BR-ES-VilaVelha 4 10 3
BR-ES-Vitoria 4 16 4
BR-MG-BeloHorizonte 1 40 41
BR-PE-Recife 1 26 27
BR-PR-Cascavel 3 10 2
... .. .. ..
BR-SP-SaoBernardoCampo 5 38 3
BR-SP-SaoPaulo 197 1013 3
BR-SP-SaoVicente 1 21 22
BR-SP-Sorocaba 2 14 8

GERANDO LINHAS DE COMANDO PARA RENOMEAR:

SELECT 'sha256sum "'|| f ||'"'
FROM (
  SELECT fmeta->>'fpath' as fpath, fmeta->>'fpath' ||'/'|| fname as f
  FROM ingest.cityfolder_input_files('/home/igor')
  WHERE is_validExt
) t
;

Amostra dos resultados, e execução como comandos e respectivos resultados:

sha256sum "/home/igor/BR-ES-Serra/input/2018-01-05 BaseCartográfica.gdb.zip"
# b18fc8ebe8bccc2cfdbbbd5f4896d5f6573033ebfa80061d1d86550c5ae8521d  /home/igor/BR-ES-Serra/input/2018-01-05 BaseCartográfica.gdb.zip
sha256sum "/home/igor/BR-ES-VilaVelha/input/BAIRROS.zip"
# c0cd7b2a4cc67b5d49a4d296f41b564b23464364ab746adc6d2206d5dd9249af  /home/igor/BR-ES-VilaVelha/input/BAIRROS.zip

Pode-se portanto criar uma função shell que já execute também o mv, por exemplo

mv  "/home/igor/BR-ES-Serra/input/2018-01-05 BaseCartográfica.gdb.zip" b18fc8ebe8bccc2cfdbbbd5f4896d5f6573033ebfa80061d1d86550c5ae8521d.zip
mv  "/home/igor/BR-ES-VilaVelha/input/BAIRROS.zip" c0cd7b2a4cc67b5d49a4d296f41b564b23464364ab746adc6d2206d5dd9249af.zip

Convenções de nomes de pasta e subpasta e tipos

A cada município a pasta {municipio}/input designa pode conter ou arquivos padrão ou subpastas de arquivos mais especificos.

Os tipos respeitam a sintaxe ... E cada um dos tipos ("content type" ou simplesmente ctype) e scripts de tratamento são descritos na planilha... Para destacar esses tipos a função então foi redefinida como se segue:

CREATE or replace FUNCTION ingest.cityfolder_input_files(
  p_fpath text DEFAULT '/tmp/pg_io/'
) RETURNS TABLE (fid int, cityname text, fname text, ctype text, is_validext boolean, fmeta jsonb) AS $f$

  WITH t0 AS ( SELECT rtrim(p_fpath,'/') AS fpath )
  , t1 AS (
    SELECT f as cityname,
           t0.fpath ||'/'|| f as f
    FROM pg_ls_dir((SELECT fpath FROM t0)) t(f), t0
    WHERE    f ~ '^BR\-[A-Z]{2,2}\-[A-Za-z]+$'
  )
  ,tres AS (
    SELECT cityname, fname, 'std' AS ctype,
         to_jsonb( pg_stat_file(fpath||'/'||fname) ) || jsonb_build_object('fpath',fpath) fmeta 
    FROM (  -- t2:
      SELECT cityname, 
             f||'/'||'input' as fpath,
             pg_ls_dir(f||'/'||'input') as fname
      FROM t1
      ORDER BY 1,3
    ) t2
  )

  SELECT (row_number() OVER ())::int id,  
          cityname , fname, ctype,
          fname ~* '\.(zip|gz|rar|geojson|csv|dwg)$' as is_validext,
          fmeta 
  FROM ( -- t3:

    SELECT * FROM tres WHERE not((fmeta->'isdir')::boolean)

    UNION 

    SELECT cityname, fname,ctype,
           to_jsonb( pg_stat_file(fpath||'/'||fname) ) || jsonb_build_object('fpath',fpath) AS fmeta 
    FROM ( -- t4:
      select cityname, fname as ctype,
             (fmeta->>'fpath')||'/'|| fname AS fpath,
             pg_ls_dir((fmeta->>'fpath')||'/'||fname) AS fname
      from tres
      where (fmeta->'isdir')::boolean
    ) t4

  ) t3

$f$  LANGUAGE SQL IMMUTABLE;
-- TESTE:
select cityname, ctype, count(*) n from ingest.cityfolder_input_files('/home/igor') group by 1,2 order by 1,2;
cityname ctype n
BR-ES-Serra std 1
BR-ES-VilaVelha std 4
... ... ...
BR-SP-SaoPaulo edificacoes 96
BR-SP-SaoPaulo eixos 1
BR-SP-SaoPaulo lotes 96
BR-SP-SaoPaulo planilhas 1
BR-SP-SaoPaulo quadras 1
BR-SP-SaoPaulo subdivisao 2
BR-SP-SaoVicente std 1
... ... ...

Exemplo de São Paulo

Exemplo de processo de ingestão. As tarefas gerais são:

  1. importar "as is" dos dados geométricos pertinentes (ex. shape files)
  2. importar "as is" dos dados cadastrais pertinentes (ex. planilha IPTU_2020.csv dos endereços)
  3. garantir durante a importação a vinculação com origem (SHA256 do arquivo licenciado).
  4. converter "as is" em dado de importação padronizado, convertendo tipos e nomes de coluna.
  5. consolidar com JOIN para formato padronizado quando for pertinente.

Conforme avaliação dos dados de São Paulo realizada na issue 6 o arquivo IPTU_2020.csv é cadastral (item 2) e, neste exemplo, os dados geométricos (item 1) seriam os shape files ou tabelas denominadas sirgas_shp_lotes_*. O desafio principal é garantir o join (item 5).

Lotes, conforme BR-SP-SaoPaulo-Lotes.shp:

  • SRID 31983
  • São identificados pelo ID que é a combinação das strings dos atributos "lo_setor" (length 3), "lo_quadra" (length 3) e "lo_lote" (length 4).
  • Fórmula para construir a string: lo_setor + lo_quadra + lo_lote

*Planilha IPTU 2020:

  • Única planilha na pasta input/planilhas, IPTU_2020.csv.
  • Cada entrada é identificada na coluna: "NUMERO DO CONTRIBUINTE" [coluna 0], desprezando o dígito final (exemplo 0050120059-2 vira 0050120059).
  • O endereço está nas colunas: "NOME DE LOGRADOURO DO IMOVEL" [12], "NUMERO DO IMOVEL" [13]. Opcionais: "BAIRRO DO IMOVEL" [15], "CEP DO IMOVEL" [17]

Eixos de vias, se precisar (SIRGAS_SHP_logradouronbl):

  • As ruas são identificadas pela combinação das strings dos atributos "lg_tipo", "lg_titulo", "lg_prep" e "lg_nome".
  • Fórmula para construir a string: lg_tipo + ' ' + lg_titulo + if(lg_titulo, ' ','') + lg_prep + if(lg_prep, ' ','') + lg_nome

(wiki temporária!)

Clone this wiki locally