search

digital-guard / preserv

Digital Preservation Project
http://git.digital-guard.org/preserv
Apache License 2.0
0 stars 0 forks source link

Make join preserv-BR/data/RJ/Niteroi/_pk018 #18

Closed crebollobr closed 2 years ago

crebollobr commented 2 years ago

Para testar o make join fiz manualmente no ingest2 contando quantas linhas tem select count(*) from pk18_002_p1_parcel join pk18_002_p4_cadparcel ON tx_insct = tx_insctec; Registros no join acima 57605

registro no make all_joins 56250

Pode ser alguma coisa com tipo de dado?

ppKrauss commented 2 years ago

@0e1 precisa verificar se existe uma cláusula DISTINCT ou WHERE além do ON. Ideal no futuro acrescentar um flag de DEBUG para as funções PostgreSQL mostrarem o que estão fazendo.

0e1 commented 2 years ago

O parâmetro para avaliar o resultado do update da tabela ingest.feature_asis deve ser o resultado de: select count(*) from pk18_002_p1_parcel join pk18_002_p4_cadparcel ON tx_insct = tx_insctec;?

Meu entendimento é de que não.

Vejam, pk18_002_p1_parcel e pk18_002_p4_cadparcel não são os dados ingestados. Essas tabelas contém os dados que vão ser ingestados. O que implica que a quantidade de dados ingestados pode ser menor que a quantidade nas tabelas.

Os dados cadastrais em pk18_002_p4_cadparcel não são ingestados quando properties é nula.

Os dados de parcel em pk18_002_p1_parcel não não ingestados quando geom é nula ou inválida.

Os dados originais de parcel possuem 74954 feições. São ingestadas 74891. Uma diferença de 63 feições.

Os dados originais de cadparcel possuem 83804 feições. São ingestadas 83804.

Então, ao fazer o produto de parcel e cadparcel (ingestados) é esperado que o resultado seja menor que fazer o produto de pk18_002_p1_parcel e pk18_002_p4_cadparcel.

Considerando a observação que faço mais abaixo, make join-parcel atualiza 55818 registros da tabela ingest.feature_asis. Já select count(*) from pk18_002_p1_parcel join pk18_002_p4_cadparcel ON tx_insct = tx_insctec; retorna 57605 registros. Uma diferença de 1787. Considerando que são 63 feições a menos e que cadparcel possui 3046 tx_insctec repetidos, me parece um resultado razoável, não indicando problema na operação de update.

@ppKrauss A função que faz o update de ingest.feature_asis está aqui. Revisei a função, não vejo nada que possa estar distorcendo o update.

Observação importante, que notei investigando esse caso.

É importante adicionar method_opts: -i ao cadparcel.

Sem essa opção o xlsx2csv não descarta linhas em branco. E para esse caso, a conversão estava produzindo um arquivo com 221129 linhas ao invés de um arquivo com 83805 linhas. As linhas adicionais são todas iguais a ,,,.

Exemplo:

1051690462,"RUA AIRES I DE OLIVEIRA,DES",0,Vital Brasil
1051690476,"RUA AIRES I DE OLIVEIRA,DES",116,Vital Brasil
1051690506,"RUA AIRES I DE OLIVEIRA,DES",114,Vital Brasil
1051690509,"RUA AIRES I DE OLIVEIRA,DES",0,Vital Brasil
1051690521,"RUA AIRES I DE OLIVEIRA,DES",122,Vital Brasil
1051690530,"RUA AIRES I DE OLIVEIRA,DES",0,Vital Brasil
1051690778,"RUA JOSE BOTELHO,MTO",23,Vital Brasil
1051690800,"RUA JOSE BOTELHO,MTO",57,Vital Brasil
1051690848,"RUA JOSE BOTELHO,MTO",55,Vital Brasil
1039990000,NI LOGRADOURO NAO CADASTRADO,0,NAO INFORMADO - MIGRACAO
,,,
,,,
,,,
,,,
,,,
,,,
,,,
,,,
,,,

Saída de make join-parcel:

a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ make join-parcel 

------------------------------------------
------ Join entre parcel_ext e cadparcel_cmpl ------
Continue?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.join('parcel_ext','tx_insct','4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip','cadparcel_cmpl','tx_insctec','39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip')"
       join        
-------------------
 Join 55818 items.
(1 row)

Confira os resultados nas tabelas ingest.feature_asis.

Saída de make cadparcel:

a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ make cadparcel

------------------------------------------
------ Layer tipo cadparcel_cmpl  ------
-- Incluindo dados do arquivo-4 do package-18_002 na base ingest1 --
 Nome-hash do arquivo-4: 39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip
 Tabela do layer: pk18_002_p4_cadparcel
 Sub-arquivos do arquivo-4 com o conteúdo alvo: Solicitação0710_InscTec_LogNPorBairro.*
 Tema dos sub-arquivos: Dados de Lotes
Run with tmux and sudo! (DANGER: seems not idempotent on psql)
a4a
Above user is root? If not, you have permissions for all paths?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "DROP FOREIGN TABLE IF EXISTS pk18_002_p4_cadparcel"
DROP FOREIGN TABLE
Extracting ....
cd /tmp/sandbox/_pk18_002; 7z  x -y /var/www/preserv.addressforall.org/download/39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip "*Solicitação0710_InscTec_LogNPorBairro*" ; chmod -R a+rx . > /dev/null

7-Zip [64] 16.02 : Copyright (c) 1999-2016 Igor Pavlov : 2016-05-21
p7zip Version 16.02 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,64 bits,4 CPUs Intel Xeon E312xx (Sandy Bridge) (206A1),ASM)

Scanning the drive for archives:
1 file, 3197132 bytes (3123 KiB)                      

Extracting archive: /var/www/preserv.addressforall.org/download/39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip
--
Path = /var/www/preserv.addressforall.org/download/39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip
Type = zip
Physical Size = 3197132

Everything is Ok

Size:       4330060
Compressed: 3197132
Conferindo se SRID 4326 esta configurado:
 srid |              proj4text               
------+--------------------------------------
 4326 | +proj=longlat +datum=WGS84 +no_defs 
(1 row)

Tudo bem até aqui?  [ENTER para continuar ou ^C para rodar WS/ingest-step1]

Convertendo de Solicitação0710_InscTec_LogNPorBairro.xlsx para Solicitação0710_InscTec_LogNPorBairro.csv em UTF8 com LF ...
xlsx2csv -i "/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro.xlsx"  "/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro.csv"
sed Alterando primeira linha para minúsculas ...
sed -i '1 s/./\L&/g' "/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro.csv"
psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.fdw_generate_direct_csv( '/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro.csv', 'pk18_002_p4_cadparcel' )"
NOTICE:  foreign table "pk18_002_p4_cadparcel" does not exist, skipping
 fdw_generate_direct_csv 
-------------------------
 pk18_002_p4_cadparcel
(1 row)

psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.any_load('csv2sql','/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro','cadparcel_cmpl','pk18_002_p4_cadparcel','18_002','39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip',array['row_number() OVER () AS gid', 'tx_insctec', 'tx_lgrd_no AS via_name', 'nm_lgrd_nr AS house_number', 'nm_cd_bair AS nsvia_name'])"
                   any_load                   
----------------------------------------------
 From file_id=14 inserted type=cadparcel_cmpl+
 in feature_asis 83804 items.
(1 row)

Confira os resultados nas tabelas ingest.layer_file e ingest.feature_asis.
FIM.
a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$

Saída de make parcel:

a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ make parcel

------------------------------------------
------ Layer tipo parcel_ext  ------
-- Incluindo dados do arquivo-1 do package-18_002 na base ingest1 --
 Nome-hash do arquivo-1: 4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip
 Tabela do layer: pk18_002_p1_parcel
 Sub-arquivos do arquivo-1 com o conteúdo alvo: Lotes.*
 Tema dos sub-arquivos: Lotes
Run with tmux and sudo! (DANGER: seems not idempotent on psql)
a4a
Above user is root? If not, you have permissions for all paths?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "DROP TABLE IF EXISTS pk18_002_p1_parcel CASCADE"
DROP TABLE
Extracting ....
cd /tmp/sandbox/_pk18_002; 7z  x -y /var/www/preserv.addressforall.org/download/4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip "*Lotes*" ; chmod -R a+rx . > /dev/null

7-Zip [64] 16.02 : Copyright (c) 1999-2016 Igor Pavlov : 2016-05-21
p7zip Version 16.02 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,64 bits,4 CPUs Intel Xeon E312xx (Sandy Bridge) (206A1),ASM)

Scanning the drive for archives:
1 file, 9126004 bytes (8913 KiB)                      

Extracting archive: /var/www/preserv.addressforall.org/download/4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip
--
Path = /var/www/preserv.addressforall.org/download/4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip
Type = zip
Physical Size = 9126004

Everything is Ok  

Files: 5
Size:       21694931
Compressed: 9126004
Conferindo se SRID 4326 esta configurado:
 srid |              proj4text               
------+--------------------------------------
 4326 | +proj=longlat +datum=WGS84 +no_defs 
(1 row)

Tudo bem até aqui?  [ENTER para continuar ou ^C para rodar WS/ingest-step1]

Executando shp2pgsql ...
cd /tmp/sandbox/_pk18_002; shp2pgsql  -s 4326 "Lotes.shp" pk18_002_p1_parcel | psql -q postgres://postgres@localhost/ingest1 2> /dev/null
Field shapestare is an FTDouble with width 24 and precision 15
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
                         addgeometrycolumn                          
--------------------------------------------------------------------
 public.pk18_002_p1_parcel.geom SRID:4326 TYPE:MULTIPOLYGON DIMS:2 
(1 row)

psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.any_load('shp2sql','/tmp/sandbox/_pk18_002/Lotes','parcel_ext','pk18_002_p1_parcel','18_002','4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip',array['gid', 'tx_insct', 'geom'])"

                 any_load                 
------------------------------------------
 From file_id=13 inserted type=parcel_ext+
 in feature_asis 74891 items.
(1 row)

Confira os resultados nas tabelas ingest.layer_file e ingest.feature_asis.
FIM.
a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$
crebollobr commented 2 years ago

Para esse tratamento de dados qual seria o método para validação?

0e1 commented 2 years ago

O processo de ingestão pode filtrar dados.

A função ingest.join faz um update de _featureasis a partir da tabela _cadastralasis.

Então, comparar join entre dados não filtrados com join entre dados filtrados pode resultar em quantidades diferentes.

Assim, para avaliar tem que utilizar os dados filtrados disponíveis em _featureasis e _cadastralasis.

0e1 commented 2 years ago

Esse sql pode ser usado:

SELECT COUNT (*)
FROM
(    
    SELECT  *
    FROM ingest.feature_asis 
    WHERE file_id IN 
    (
        SELECT file_id 
        FROM ingest.layer_file 
        WHERE ftid IN 
            (
            SELECT ftid::int 
            FROM ingest.feature_type 
            WHERE ftname=lower('parcel_ext')
            ) 
            AND pck_fileref_sha256 = '4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip'
    )
) AS asis

INNER JOIN

(
    SELECT  *
    FROM ingest.cadastral_asis 
    WHERE file_id IN 
        (
        SELECT file_id 
        FROM ingest.layer_file 
        WHERE ftid IN 
            (
            SELECT ftid::int 
            FROM ingest.feature_type 
            WHERE ftname=lower('cadparcel_cmpl')
            ) 
            AND pck_fileref_sha256 = '39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip'
        )   
) AS cadis

ON asis.properties->'tx_insct' = cadis.properties->'tx_insctec'

Mas devagar com o andor. A quantidade retornada pelo sql anterior será a quantidade de dados de _featureasis que será atualizada pelo make join-parcel? Existe a possibilidade de não ser.

Ao executá-lo você obterá o seguinte:

 count 
-------
 57588
(1 row)

Sim, 57588 é diferente dos 55818 que são atualizados após o make join-parcel realizado. Por quê? Bem, a operação é feita com os dados que foram ingestados, incluindo os problemas que eles tiverem.

Ao agrupar pelos valores em ON asis.properties->'tx_insct' e cadis.properties->'tx_insctec' usando o sql:

SELECT COUNT (*)
FROM
(
    SELECT DISTINCT asis.properties->'tx_insct'
    FROM
    (    
        SELECT  *
        FROM ingest.feature_asis 
        WHERE file_id IN 
        (
            SELECT file_id 
            FROM ingest.layer_file 
            WHERE ftid IN 
                (
                SELECT ftid::int 
                FROM ingest.feature_type 
                WHERE ftname=lower('parcel_ext')
                ) 
                AND pck_fileref_sha256 = '4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip'
        )
    ) AS asis

    INNER JOIN

    (
        SELECT  *
        FROM ingest.cadastral_asis 
        WHERE file_id IN 
            (
            SELECT file_id 
            FROM ingest.layer_file 
            WHERE ftid IN 
                (
                SELECT ftid::int 
                FROM ingest.feature_type 
                WHERE ftname=lower('cadparcel_cmpl')
                ) 
                AND pck_fileref_sha256 = '39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip'
            )   
    ) AS cadis

    ON asis.properties->'tx_insct' = cadis.properties->'tx_insctec'

    GROUP BY cadis.properties->'tx_insctec', asis.properties->'tx_insct'
)
AS d
;

obtemos a quantidade distinta de linhas:

 count 
-------
 55706
(1 row)

Como 55706 é diferente de 57588, o resultado possui linhas repetidas. E fazer um update nesse situação gera, comportamento não previsível, conforme

_When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromitem list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable. em PostgreSQL: Documentation: 13: UPDATE

Referencio essa documentação porque a função por trás do make join-parcel é um update com clausula from.

Ah, 55706 também é diferente dos 55818 registros atualizados pela função ingest.join. Essa diferença de 112 eu não sei explicar, ainda. Mas deve estar relacionada a esse comportamento do update quando utilizado com from.

Em resumo, a diferença entre um update com clausula from e o join realizado na mão está relacionada às linhas repetidas produzidas, ou seja, aos problemas com os dados.

ppKrauss commented 2 years ago

Olá @0e1 e @crebollobr, a discussão gerou subsídios suficientes para criarmos um resumo na documentação (ou em um FAQ).

Meu parecer, por favor confirmem se resume corretamente:

  1. As tabelas AsIs da Geometria e dos dados cadastrais estão sendo ingeridas 100%
  2. A tabela AsIs cadastral pode ser utilizada para gerar listagens sumarizadas de nomes oficiais, etc. mas será descartada por sua redundância com a tabela da geometria, depois de feito o UPDATE.
  3. 100% da Geometria é mantida no AsIs mas o UPDATE pode trazer menos de 100% dos dados cadastrais
  4. Podemos definir um critério de "dados cadastrais bons" e "dados geométricos bons" para gerar o critério de "percentual de UPDATEs mínimo". Se ocorrer JOIN-UPDATE com no mínimmo 80% das geometrias está aprovado, se menos o dado volta ou uma curadoria decide o que fazer.
    PS: menos de 80% pode ser indício de problemas com os identificadores ou "JOIN aleatório".
0e1 commented 2 years ago

#

Olá @0e1 e @crebollobr, a discussão gerou subsídios suficientes para criarmos um resumo na documentação (ou em um FAQ).

Meu parecer, por favor confirmem se resume corretamente:

1. As tabelas AsIs da Geometria e dos dados cadastrais estão sendo ingeridas 100%

Os dados cadastrais estão. Os geométricos não estão sendo ingeridos 100%, devido a geom IS NOT NULL AND ST_IsValid(geom) em any_load.

2. A tabela AsIs cadastral pode ser utilizada para gerar listagens sumarizadas de nomes oficiais, etc. mas será descartada por sua redundância com a tabela da geometria, depois de feito o UPDATE.

Ok.

3. 100% da Geometria é mantida no AsIs mas o UPDATE pode trazer menos de 100% dos dados cadastrais

Sim. Majoritariamente pelos problemas com os identificadores que relacionam os dados.

4. Podemos definir um critério de "dados cadastrais bons" e "dados geométricos bons" para gerar o critério de **"percentual de UPDATEs mínimo"**. Se ocorrer JOIN-UPDATE com no mínimmo 80% das geometrias está aprovado, se menos o dado volta ou uma curadoria decide o que fazer. PS: menos de 80% pode ser indício de problemas com os identificadores ou "JOIN aleatório".

Concordo.

Via email: mas ficou a dúvida de porque não usou DISTINCT... e se os dados duplicam com resultados diferentes, porque não excluir tais casos? Apenas dados 100% devem ser agregados ao resultado, o resto será trabalho humano e investimento em horas humanas para decidir qual dado mais usar.

Concordo que mais casos devem ser excluídos do UPDATE. Eu não tinha me apercebido de como o UPDATE age em relação as linhas duplicadas presentes no join realizado quando FROM está presente. Então isso é uma coisa que precisa ser consertada. Ou seja, atender a isso: When using FROM you should ensure that the join produces at most one output row for each row to be modified.

Para atender a isso, e a apenas dados 100%, qualquer identificador em _featureasis que se relaciona com mais de um identificador em _cadastrasasis não deve ser atualizado.

Usar DISTINCT não resolve, primeiro que acho que não dá pra combinar com UPDATE. Segundo, porque de um grupo de linhas duplicadas ele retorna uma delas.

O esqueleto da função ingest.join é:

UPDATE asis
SET asis.properties
FROM cad_asis
WHERE asis.x = cad_asis.y

Esse UPDATE funciona fazendo o join de asis com cad_asis e atualizando asis. Pra não ter o comportamento indesejado do UPDATE, o join de asis X cad_asis não deve ter linhas duplicadas.

Então, ao invés de usar a tabela cadastral_asis na clausula FROM do UPDATE, usar um join de asis X cad_asis removidas todas as linhas duplicadas. O esquelto da função, ficaria algo como:

UPDATE asis
SET asis.properties
FROM join_asis_cadasis_clean
WHERE asis.x = join_asis_cadasis_clean.y
ppKrauss commented 2 years ago

@0e1 sobre o último comentário, fica a sugestão se consulta direta:

WITH checkDup AS (
  SELECT cad_id 
  FROM ingest.cadastral_asis
  WHERE file_id=123
  GROUP BY 1
  HAVING COUNT(*)>1
), join_asis_cadasis_clean AS (
  SELECT *
  FROM    ingest.cadastral_asis
  WHERE properties IS NOT NULL AND properties->>'xpto'>'' 
               AND  cad_id NOT IN (SELECT cad_id FROM checkDup)
)
 UPDATE ingest.feature_asis
 SET properties=j.properties
 FROM  join_asis_cadasis_clean j
 WHERE feature_asis.x = j.cad_id

PS: WITH é mais didático e elegante, mas pode perder performance. Na dúvida usar subqueries.

0e1 commented 2 years ago

Só pra comentar que a alteração https://github.com/AddressForAll/WS/commit/c271f26ae93adbbcba232b4ba89afec5ebacfd5c pretende consertar o problema relacionado a linhas duplicadas.

@crebollobr Acredito que a forma correta de validar o join realizado é comparar o resultado do código a seguir com o resultado exibido após a realização do join:

SELECT COUNT (*)
FROM
(
    SELECT asis.properties->'usuario'
    FROM
    (    
        SELECT  *
        FROM ingest.feature_asis 
        WHERE file_id IN 
        (
            SELECT file_id 
            FROM ingest.layer_file 
            WHERE ftid IN 
                (
                SELECT ftid::int 
                FROM ingest.feature_type 
                WHERE ftname=lower('geoaddress_ext')
                ) 
                AND pck_fileref_sha256 = '43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip'
        )
    ) AS asis

    INNER JOIN

    (
        SELECT  *
        FROM ingest.cadastral_asis 
        WHERE file_id IN 
            (
            SELECT file_id 
            FROM ingest.layer_file 
            WHERE ftid IN 
                (
                SELECT ftid::int 
                FROM ingest.feature_type 
                WHERE ftname=lower('address_cmpl')
                ) 
                AND pck_fileref_sha256 = '9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip'
            )   
    ) AS cadis

    ON asis.properties->'usuario' = cadis.properties->'INSCRINRO'

    GROUP BY asis.properties->'usuario'

    HAVING COUNT(*)=1
)
AS d
;

Exemplo de execução completa para Cusco:

a4a@ubuntu:/opt/gits/_dg/preserv-PE/data/CUS/Cusco/_pk001$ make all_layers 

------------------------------------------
------ Layer tipo geoaddress_ext  ------
-- Incluindo dados do arquivo-1 do package-1_001 na base ingest1 --
 Nome-hash do arquivo-1: 43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip
 Tabela do layer: pk1_001_p1_geoaddress
 Sub-arquivos do arquivo-1 com o conteúdo alvo: Ubicaciones/Inscripciones.*
 Tema dos sub-arquivos: Inscripciones
Run with tmux and sudo! (DANGER: seems not idempotent on psql)
a4a
Above user is root? If not, you have permissions for all paths?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "DROP TABLE IF EXISTS pk1_001_p1_geoaddress CASCADE"
NOTICE:  table "pk1_001_p1_geoaddress" does not exist, skipping
DROP TABLE
Extracting ....
cd /tmp/sandbox/_pk1_001; 7z  x -y /var/www/preserv.addressforall.org/download/43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip "*Ubicaciones/Inscripciones*" ; chmod -R a+rx . > /dev/null

7-Zip [64] 16.02 : Copyright (c) 1999-2016 Igor Pavlov : 2016-05-21
p7zip Version 16.02 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,64 bits,4 CPUs Intel Xeon E312xx (Sandy Bridge) (206A1),ASM)

Scanning the drive for archives:
1 file, 323770562 bytes (309 MiB)                     

Extracting archive: /var/www/preserv.addressforall.org/download/43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip
--
Path = /var/www/preserv.addressforall.org/download/43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip
Type = zip
Physical Size = 323770562
Comment = Written using ZipTricks 5.6.0

Everything is Ok                      

Files: 6
Size:       323769599
Compressed: 323770562
Conferindo se SRID 32719 esta configurado:
 srid  |                         proj4text                         
-------+-----------------------------------------------------------
 32719 | +proj=utm +zone=19 +south +datum=WGS84 +units=m +no_defs 
(1 row)

Tudo bem até aqui?  [ENTER para continuar ou ^C para rodar WS/ingest-step1]

Executando shp2pgsql ...
cd /tmp/sandbox/_pk1_001; shp2pgsql  -s 32719 "Ubicaciones/Inscripciones.shp" pk1_001_p1_geoaddress | psql -q postgres://postgres@localhost/ingest1 2> /dev/null
Shapefile type: PointZ
Postgis type: POINT[4]
                        addgeometrycolumn                        
-----------------------------------------------------------------
 public.pk1_001_p1_geoaddress.geom SRID:32719 TYPE:POINT DIMS:4 
(1 row)

psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.any_load('shp2sql','/tmp/sandbox/_pk1_001/Ubicaciones/Inscripciones','geoaddress_ext','pk1_001_p1_geoaddress','1_001','43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip',array['gid', 'usuario', 'geom'])"
                  any_load                   
---------------------------------------------
 From file_id=5 inserted type=geoaddress_ext+
 in feature_asis 102553 items.
(1 row)

Confira os resultados nas tabelas ingest.layer_file e ingest.feature_asis.
FIM.

------------------------------------------
------ Layer tipo address_cmpl  ------
-- Incluindo dados do arquivo-2 do package-1_001 na base ingest1 --
 Nome-hash do arquivo-2: 9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip
 Tabela do layer: pk1_001_p2_address
 Sub-arquivos do arquivo-2 com o conteúdo alvo: Padron de Direcciones Cusco.*
 Tema dos sub-arquivos: Direcciones
Run with tmux and sudo! (DANGER: seems not idempotent on psql)
a4a
Above user is root? If not, you have permissions for all paths?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "DROP FOREIGN TABLE IF EXISTS pk1_001_p2_address"
NOTICE:  foreign table "pk1_001_p2_address" does not exist, skipping
DROP FOREIGN TABLE
Extracting ....
cd /tmp/sandbox/_pk1_001; 7z  x -y /var/www/preserv.addressforall.org/download/9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip "Padron de Direcciones Cusco*" ; chmod -R a+rx . > /dev/null

7-Zip [64] 16.02 : Copyright (c) 1999-2016 Igor Pavlov : 2016-05-21
p7zip Version 16.02 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,64 bits,4 CPUs Intel Xeon E312xx (Sandy Bridge) (206A1),ASM)

Scanning the drive for archives:
1 file, 4348258 bytes (4247 KiB)                      

Extracting archive: /var/www/preserv.addressforall.org/download/9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip
--
Path = /var/www/preserv.addressforall.org/download/9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip
Type = zip
Physical Size = 4348258

Everything is Ok

Size:       5043527
Compressed: 4348258
Conferindo se SRID 32719 esta configurado:
 srid  |                         proj4text                         
-------+-----------------------------------------------------------
 32719 | +proj=utm +zone=19 +south +datum=WGS84 +units=m +no_defs 
(1 row)

Tudo bem até aqui?  [ENTER para continuar ou ^C para rodar WS/ingest-step1]

Convertendo de Padron de Direcciones Cusco.xlsx para Padron de Direcciones Cusco.csv em UTF8 com LF ...
xlsx2csv -i "/tmp/sandbox/_pk1_001/Padron de Direcciones Cusco.xlsx" "/tmp/sandbox/_pk1_001/Padron de Direcciones Cusco.csv"
psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.fdw_generate_direct_csv( '/tmp/sandbox/_pk1_001/Padron de Direcciones Cusco.csv', 'pk1_001_p2_address' )"
NOTICE:  foreign table "pk1_001_p2_address" does not exist, skipping
 fdw_generate_direct_csv 
-------------------------
 pk1_001_p2_address
(1 row)

psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.any_load('csv2sql','/tmp/sandbox/_pk1_001/Padron de Direcciones Cusco','address_cmpl','pk1_001_p2_address','1_001','9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip',array['\"INSCRINRO\"', '\"PROVINCIA\"', '\"DISTRITO\"', '\"TIPURB\"', '\"URB\"', '\"TIPCAL\"', '\"CALLE\" AS via_name', '\"PRENRO\" AS house_number'])"
                 any_load                  
-------------------------------------------
 From file_id=6 inserted type=address_cmpl+
 in feature_asis 117257 items.
(1 row)

Confira os resultados nas tabelas ingest.layer_file e ingest.feature_asis.
psql postgres://postgres@localhost/ingest1 -c "DROP FOREIGN TABLE IF EXISTS pk1_001_p2_address"
DROP FOREIGN TABLE
FIM.
--ALL LAYERS--
a4a@ubuntu:/opt/gits/_dg/preserv-PE/data/CUS/Cusco/_pk001$ make all_joins 

------------------------------------------
------ Join entre geoaddress_ext e address_cmpl ------
Continue?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.join('geoaddress_ext','usuario','43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip','address_cmpl','"INSCRINRO"','9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip')"
       join        
-------------------
 Join 97586 items.
(1 row)

Confira os resultados nas tabelas ingest.feature_asis.
--ALL JOINS--

a4a@ubuntu:/opt/gits/_dg/preserv-PE/data/CUS/Cusco/_pk001$ psql postgres://postgres@localhost/ingest1
psql (13.5 (Ubuntu 13.5-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

ingest1=# 
ingest1=# SELECT COUNT (*)
ingest1-# FROM
ingest1-# (
ingest1(#     SELECT asis.properties->'usuario'
ingest1(#     FROM
ingest1(#     (    
ingest1(#         SELECT  *
ingest1(#         FROM ingest.feature_asis 
ingest1(#         WHERE file_id IN 
ingest1(#         (
ingest1(#             SELECT file_id 
ingest1(#             FROM ingest.layer_file 
ingest1(#             WHERE ftid IN 
ingest1(#                 (
ingest1(#                 SELECT ftid::int 
ingest1(#                 FROM ingest.feature_type 
ingest1(#                 WHERE ftname=lower('geoaddress_ext')
ingest1(#                 ) 
ingest1(#                 AND pck_fileref_sha256 = '43484294f9190a3a050e097e0c106f8da16169a3e1dff9d00ea04aae6e9cbe1a.zip'
ingest1(#         )
ingest1(#     ) AS asis
ingest1(# 
ingest1(#     INNER JOIN
ingest1(# 
ingest1(#     (
ingest1(#         SELECT  *
ingest1(#         FROM ingest.cadastral_asis 
ingest1(#         WHERE file_id IN 
ingest1(#             (
ingest1(#             SELECT file_id 
ingest1(#             FROM ingest.layer_file 
ingest1(#             WHERE ftid IN 
ingest1(#                 (
ingest1(#                 SELECT ftid::int 
ingest1(#                 FROM ingest.feature_type 
ingest1(#                 WHERE ftname=lower('address_cmpl')
ingest1(#                 ) 
ingest1(#                 AND pck_fileref_sha256 = '9b76c8f77f5e08b9bae2d839540a6d02d640c62bf422efd3a9f629fdd247b97e.zip'
ingest1(#             )   
ingest1(#     ) AS cadis
ingest1(#         
ingest1(#     ON asis.properties->'usuario' = cadis.properties->'INSCRINRO'
ingest1(# 
ingest1(#     GROUP BY asis.properties->'usuario'
ingest1(#     
ingest1(#     HAVING COUNT(*)=1
ingest1(# )
ingest1-# AS d
ingest1-# ;
 count 
-------
 97586
(1 row)

Exemplo de execução completa para Niterói:

a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ make cadparcel

------------------------------------------
------ Layer tipo cadparcel_cmpl  ------
-- Incluindo dados do arquivo-4 do package-18_002 na base ingest1 --
 Nome-hash do arquivo-4: 39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip
 Tabela do layer: pk18_002_p4_cadparcel
 Sub-arquivos do arquivo-4 com o conteúdo alvo: Solicitação0710_InscTec_LogNPorBairro.*
 Tema dos sub-arquivos: Dados de Lotes
Run with tmux and sudo! (DANGER: seems not idempotent on psql)
a4a
Above user is root? If not, you have permissions for all paths?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "DROP FOREIGN TABLE IF EXISTS pk18_002_p4_cadparcel"
NOTICE:  foreign table "pk18_002_p4_cadparcel" does not exist, skipping
DROP FOREIGN TABLE
Extracting ....
cd /tmp/sandbox/_pk18_002; 7z  x -y /var/www/preserv.addressforall.org/download/39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip "*Solicitação0710_InscTec_LogNPorBairro*" ; chmod -R a+rx . > /dev/null

7-Zip [64] 16.02 : Copyright (c) 1999-2016 Igor Pavlov : 2016-05-21
p7zip Version 16.02 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,64 bits,4 CPUs Intel Xeon E312xx (Sandy Bridge) (206A1),ASM)

Scanning the drive for archives:
1 file, 3197132 bytes (3123 KiB)                      

Extracting archive: /var/www/preserv.addressforall.org/download/39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip
--
Path = /var/www/preserv.addressforall.org/download/39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip
Type = zip
Physical Size = 3197132

Everything is Ok

Size:       4330060
Compressed: 3197132
Conferindo se SRID 4326 esta configurado:
 srid |              proj4text               
------+--------------------------------------
 4326 | +proj=longlat +datum=WGS84 +no_defs 
(1 row)

Tudo bem até aqui?  [ENTER para continuar ou ^C para rodar WS/ingest-step1]

Convertendo de Solicitação0710_InscTec_LogNPorBairro.xlsx para Solicitação0710_InscTec_LogNPorBairro.csv em UTF8 com LF ...
xlsx2csv -i "/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro.xlsx" "/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro.csv"
psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.fdw_generate_direct_csv( '/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro.csv', 'pk18_002_p4_cadparcel' )"
NOTICE:  foreign table "pk18_002_p4_cadparcel" does not exist, skipping
 fdw_generate_direct_csv 
-------------------------
 pk18_002_p4_cadparcel
(1 row)

psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.any_load('csv2sql','/tmp/sandbox/_pk18_002/Solicitação0710_InscTec_LogNPorBairro','cadparcel_cmpl','pk18_002_p4_cadparcel','18_002','39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip',array['row_number() OVER () AS gid', 'tx_insctec', 'tx_lgrd_no AS via_name', 'nm_lgrd_nr AS house_number', 'nm_cd_bair AS nsvia_name'])"
                  any_load                   
---------------------------------------------
 From file_id=3 inserted type=cadparcel_cmpl+
 in feature_asis 83804 items.
(1 row)

Confira os resultados nas tabelas ingest.layer_file e ingest.feature_asis.
psql postgres://postgres@localhost/ingest1 -c "DROP FOREIGN TABLE IF EXISTS pk18_002_p4_cadparcel"
DROP FOREIGN TABLE
FIM.
a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ make parcel

------------------------------------------
------ Layer tipo parcel_ext  ------
-- Incluindo dados do arquivo-1 do package-18_002 na base ingest1 --
 Nome-hash do arquivo-1: 4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip
 Tabela do layer: pk18_002_p1_parcel
 Sub-arquivos do arquivo-1 com o conteúdo alvo: Lotes.*
 Tema dos sub-arquivos: Lotes
Run with tmux and sudo! (DANGER: seems not idempotent on psql)
a4a
Above user is root? If not, you have permissions for all paths?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "DROP TABLE IF EXISTS pk18_002_p1_parcel CASCADE"
DROP TABLE
Extracting ....
cd /tmp/sandbox/_pk18_002; 7z  x -y /var/www/preserv.addressforall.org/download/4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip "*Lotes*" ; chmod -R a+rx . > /dev/null

7-Zip [64] 16.02 : Copyright (c) 1999-2016 Igor Pavlov : 2016-05-21
p7zip Version 16.02 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,64 bits,4 CPUs Intel Xeon E312xx (Sandy Bridge) (206A1),ASM)

Scanning the drive for archives:
1 file, 9126004 bytes (8913 KiB)                      

Extracting archive: /var/www/preserv.addressforall.org/download/4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip
--
Path = /var/www/preserv.addressforall.org/download/4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip
Type = zip
Physical Size = 9126004

Everything is Ok  

Files: 5
Size:       21694931
Compressed: 9126004
Conferindo se SRID 4326 esta configurado:
 srid |              proj4text               
------+--------------------------------------
 4326 | +proj=longlat +datum=WGS84 +no_defs 
(1 row)

Tudo bem até aqui?  [ENTER para continuar ou ^C para rodar WS/ingest-step1]

Executando shp2pgsql ...
cd /tmp/sandbox/_pk18_002; shp2pgsql  -s 4326 "Lotes.shp" pk18_002_p1_parcel | psql -q postgres://postgres@localhost/ingest1 2> /dev/null
Field shapestare is an FTDouble with width 24 and precision 15
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
                         addgeometrycolumn                          
--------------------------------------------------------------------
 public.pk18_002_p1_parcel.geom SRID:4326 TYPE:MULTIPOLYGON DIMS:2 
(1 row)

psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.any_load('shp2sql','/tmp/sandbox/_pk18_002/Lotes','parcel_ext','pk18_002_p1_parcel','18_002','4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip',array['gid', 'tx_insct', 'geom'])"
                any_load                 
-----------------------------------------
 From file_id=4 inserted type=parcel_ext+
 in feature_asis 74891 items.
(1 row)

Confira os resultados nas tabelas ingest.layer_file e ingest.feature_asis.
FIM.
a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ make join-parcel 

------------------------------------------
------ Join entre parcel_ext e cadparcel_cmpl ------
Continue?
 [press ENTER for yes else ^C]
psql postgres://postgres@localhost/ingest1 -c "SELECT ingest.join('parcel_ext','tx_insct','4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip','cadparcel_cmpl','tx_insctec','39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip')"
       join        
-------------------
 Join 54564 items.
(1 row)

Confira os resultados nas tabelas ingest.feature_asis.
a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ 

a4a@ubuntu:/opt/gits/_dg/preserv-BR/data/RJ/Niteroi/_pk018$ psql postgres://postgres@localhost/ingest1
psql (13.5 (Ubuntu 13.5-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

ingest1=# SELECT COUNT (*)
ingest1-# FROM
ingest1-# (
ingest1(#     SELECT asis.properties->'tx_insct'
ingest1(#     FROM
ingest1(#     (    
ingest1(#         SELECT  *
ingest1(#         FROM ingest.feature_asis 
ingest1(#         WHERE file_id IN 
ingest1(#         (
ingest1(#             SELECT file_id 
ingest1(#             FROM ingest.layer_file 
ingest1(#             WHERE ftid IN 
ingest1(#                 (
ingest1(#                 SELECT ftid::int 
ingest1(#                 FROM ingest.feature_type 
ingest1(#                 WHERE ftname=lower('parcel_ext')
ingest1(#                 ) 
ingest1(#                 AND pck_fileref_sha256 = '4bde69d0057c0785598f98963fdae3dc90420fb8f163652d70b659210da0ae2b.zip'
ingest1(#         )
ingest1(#     ) AS asis
ingest1(# 
ingest1(#     INNER JOIN
ingest1(# 
ingest1(#     (
ingest1(#         SELECT  *
ingest1(#         FROM ingest.cadastral_asis 
ingest1(#         WHERE file_id IN 
ingest1(#             (
ingest1(#             SELECT file_id 
ingest1(#             FROM ingest.layer_file 
ingest1(#             WHERE ftid IN 
ingest1(#                 (
ingest1(#                 SELECT ftid::int 
ingest1(#                 FROM ingest.feature_type 
ingest1(#                 WHERE ftname=lower('cadparcel_cmpl')
ingest1(#                 ) 
ingest1(#                 AND pck_fileref_sha256 = '39d34950c7c61a89cfc2b4d70b0b34a85b346b41741331009205de0f06c86805.zip'
ingest1(#             )   
ingest1(#     ) AS cadis
ingest1(#         
ingest1(#     ON asis.properties->'tx_insct' = cadis.properties->'tx_insctec'
ingest1(# 
ingest1(#     GROUP BY asis.properties->'tx_insct'
ingest1(#     
ingest1(#     HAVING COUNT(*)=1
ingest1(# )
ingest1-# AS d
ingest1-# ;
 count 
-------
 54564
(1 row)

ingest1=#