Ingestão MMDM DANE

[luisfelipebr]

sha256sum pronto. Segue dúvidas:

1. SRID: A documentação descreve o seguinte CRS. Qual o SRID associado? EPSG:4686?

Código del Sistema: GCS_MAGNA Descripción del Sistema: Datum: D_MAGNA Prime Meridian: Greenwich Angular Unit: Degree Responsable: European Petroleum Survey Group - EPSG

2. Os dados estão em txt. Separador de colunas = "|". Separador decimal = ",". Qual método utilizar?

3. Estou utilizando house_number temporariamente

[ppKrauss]

Precisamos analisar as duas coisas, o perfil dos dados e a documentação, precisam estar consistentes. No perfil dos dados temos por exemplo:

• geo:11.13518055,-74.22753138 = 47001 | 1 | CL 142 1 99 DP S 63, bate com ponto dentro de CO-47001
• ... favor conferir uma dezena pelo menos, ver por exemplo se bate com endereço Google.

Aqui interpretamos como padrão Geo URI, portanto WGS84, portanto SRID 4326.

Analisando o GCS_MAGNA por sua string PROJ: +proj=tmerc +lat_0=4.596200416666666 +lon_0=-74.07750791666666 +k=1 +x_0=1000000 +y_0=1000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m.

• Unidade metro, não graus
• Projeção T. de Mercator, não LatLong.

... Precisamos entender melhor a que se refere a documentação, se aos dados publicados ou se ao sistema-origem da aquisição dos dados de localização (ex. mapas da prefeituras), que aparentemente teriam sido transformados.

[ppKrauss]

Amostragem dos dados

select concat('[',dpto_mpio,'](https://osm.codes/CO-',dpto_mpio,')') as dpto_mpio,
          direccion_normalizada,
          concat('[',geouri,'](https://osm.codes/',geouri,';u=20)') as geouri
from (
  SELECT dpto_mpio, direccion_normalizada,
         'geo:'||replace(latitud,',','.')||','||replace(longitud,',','.') as geouri
  FROM mmdm12345 TABLESAMPLE BERNOULLI (0.00035) 
) t order by 1,2;

dpto_mpio	direccion_normalizada	geouri
05001	CL 077 035 045	geo:6.26879817,-75.54595267
05001	KR 44A 103 50 IN 212	geo:6.292857,-75.55057099999999
05001	KR 81B 1A S 4	geo:6.210014,-75.604601
05001	KR 83F 15A 144	geo:6.222929,-75.606906
05001	KR 87A 68B 78	geo:6.279211,-75.594533
05154	KR SI SI 9A	geo:7.988121,-75.197445
05212	CL 48 61 43 IN 136	geo:6.342021,-75.514975
05282	CL 50 51 63 CALLE ZN ROSA	geo:5.926781,-75.672739
05360	KR 46A 66 13	geo:6.1794074,-75.5979124
08001	KR 10 64B 47	geo:10.9591137,-74.81286599
08001	KR 24 63B 56	geo:10.97170049,-74.80539577
08078	CL 18 11 100	geo:10.79690829,-74.9232678
11001	AK 72 128B 65 BQ 2 AP 601	geo:4.72036188,-74.07604144
11001	CL 137A 113 00	geo:4.74300293,-74.10441274
11001	CL 151 109A 83 SS 1 GJ 173	geo:4.7520536,-74.09848932
11001	CL 59 S 52 24 TO 10 AP 401	geo:4.58340187,-74.15351702
11001	KR 16 98 31 AP 303	geo:4.68362373,-74.04951543
11001	KR 3 17 29 AP TO 401	geo:4.601719,-74.069101
11001	KR 31 4A 04	geo:4.605166,-74.100904
11001	KR 57A 55 40 IN BQ 63 AP 202	geo:4.650772,-74.09053
11001	KR 7 12B 63 OF 406	geo:4.60066785,-74.074273
11001	KR 73 76A 59	geo:4.572794,-74.170256
11001	KR 8 13 34 S ACUARELA TO 5 AP 602	geo:4.579276,-74.08916
11001	KR 87B 19A 66 IN 3 AP 204	geo:4.66405438,-74.13296346
11001	KR 95 70A 63 S BQ 2 AP 501	geo:4.63124406,-74.20142715
11001	TV 53 S 19 42 AP 302	geo:4.610634,-74.12471
13001	1 LT 5A	geo:10.51716864,-75.48422674
15001	KR 1 38 12 BALCONES DE BETANIA	geo:5.543989,-73.348972
15087	KR 3B 4 92	geo:5.986898,-72.91348
15172	CL 7B 1 BIS A 04 LC 7	geo:5.17069782,-73.36612029
15322	CA 2 MZ H	geo:5.013027,-73.470919
15723	KR 4 3 61	geo:6.09437477,-72.71311282
15816	VILLA LAURA VDA CENTRO	geo:5.93594376,-73.50813225
17001	CL 48 22A 14 LC	geo:5.06535743,-75.50005809
19392	SANLORENZO	geo:2.197905,-76.830761
19807	CL 18 26 76 LC 3	geo:2.35627657,-76.68879839
20001	KR 40B 3C 107	geo:10.481285,-73.282102
23001	CL 15 12 11 O	geo:8.7545569,-75.90348992
23168	AL LADO DE LA LT 6A	geo:9.148273,-75.624083
23189	KR 14 17 31	geo:8.873543,-75.627179
23555	KR 5C 21 14	geo:8.41299247,-75.58049324
25286	CL 12 2C 55 BQ 6 CA 2	geo:4.704956,-74.209065
25486	CL 6 11A 13 SAN J TO 7 AP 301 MZ D	geo:5.07306593,-73.87496514
25777	KR 6 1A 96 IN 1	geo:5.06103843,-74.23597655
41001	KR 8A 35 22	geo:2.94764164,-75.28796403
41885	KR 9 3 58	geo:2.66539538,-75.52030631
44001	CL 14K 22A 91	geo:11.535245,-72.92397199999999
44279	CL 19 23 04	geo:10.881689,-72.8594928
50001	CL 17B S 45C 38 C	geo:4.109502,-73.65205
50001	CL 4C 34C 45 CA 5B MZ H	geo:4.11977577,-73.6332609
50001	CL 5 S 50 27 AP 2	geo:4.121475,-73.647227
50001	SECT 2 M Z 9 CA DESOCUPADA	geo:4.099779,-73.635376
50006	TV 29 16D 05 E CA 8 MZ	geo:3.9918893,-73.77056071
50287	CL 13A 9 73 CA 1 MZ 115	geo:3.46680929,-73.61739617
52435	CL CRA 4 CALL 1 FRENTE AL PQ PRINCIPAL	geo:1.14087492,-77.86471058
52720	LC DE TERRENO 1B MARAMBA	geo:1.02455229,-77.61095814
54223	LA ESPERANZA	geo:7.50648759,-72.81912972
66170	CL 15 9A 21 LA BADEA	geo:4.82538454,-75.68602812
66682	CL 23A 9 46 LOS GUAYACANES	geo:4.87483284,-75.62070406
66682	LC 26 EL LEMBO	geo:4.9131945,-75.62736617
68081	CL 48C	geo:7.05864378,-73.82591119
68432	KR 9 26 12 HB CHICAS	geo:6.712657,-72.729948
68615	KR 11 9 41	geo:7.264671,-73.149841
68679	LC 24 CD LA NUEVA B MZ G	geo:6.5445898,-73.13529216
70001	CL 23B 9 79 AP 3	geo:9.299384,-75.409153
70713	KR 3 5 14	geo:9.76978089,-75.64284151
73443	LC 36 MZ 4	geo:5.18915856,-74.88559773
73616	LA CRISTALINA	geo:3.34752258,-75.76823261
76001	CL 85 22 96	geo:3.43638826,-76.46844059
76001	KR 68A 26 47 CA	geo:3.395825,-76.521577
76036	LC 6 MZ G	geo:4.17353262,-76.16944348
76109	CL 11B S 57 04	geo:3.86534398,-77.01662012
76109	KR 74 S	geo:3.861656,-76.992858
76130	CL 11A 9 16	geo:3.400614,-76.419124
76250	KR 9 10 38	geo:4.50850945,-76.23728742
76364	ET VIII ROBLES DEL CASTILLO CA 36	geo:3.27542395,-76.5053469
76828	CL 21 17 10	geo:4.2122969999999995,-76.316245
81001	CA 5 MZ 13	geo:7.087236,-70.728372
86568	CL 10CR 27 4	geo:0.495104,-76.498027
88001	CL 15 14 101 IN 31	geo:12.58474269,-81.71229811

[ppKrauss]

Achei SRID de MAGNA correto

A abreviação GCS de GCS_MAGNA refere-se apenas ao termo genérico "Geographic Coordinate System".

Existe um homônimo, https://epsg.io/4997 (nome oficial "MAGNA-SIRGAS") que é consistente com as unidade, longitude-latitude:

select proj4text from spatial_ref_sys  where srid=4997;
-- +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs

Agora sim, temos compatibilidade entre o padrão Geo URI e o PDF descritor dos dados:

A string PROJ mostra que o SIRGAS é sinônoimo de WGS85, ou seja, podemos usar SRID=4326.

[0e1]

O necessário para ingerir os dados já está pronto:

• sql_view, para criar pontos substituindo ',' por ponto;
• execturar comando após a extração do arquivo.

No entanto a ingestão não acontece. Precisar decidir como lidar com linhas do tipo:

grep -a -n "PISO" Marco_Maestro_Direcciones_Multiproposito.txt

19852623:76275|1|CL 12 21 37 �PISO 12 21 37�PISO|3,327908|-76,236952
19864909:76275|1|CL 12 16 66 �PISO 12 16 66�PISO|3,326374|-76,233133
19876209:76275|1|KR 9 S I 10 2 5 9 10 25 �PISO|3,322549|-76,228034
19876660:76275|1|CL 13 S I 13 0 1�PISO 13 13 01 �PISO|3,318168|-76,233134
19881506:76275|1|CL 10 S I 10A 1 0�PISO 10 10A 10�PISO|3,322482|-76,228113
19883133:76109|1|KR 65A 24 2 KR 65 A 24 2 APARTAMETO SEGUDOPISO|3,87148|-76,998876 

Teoricamente o charset do arquivo é us-ascii:

file -i Marco_Maestro_Direcciones_Multiproposito.txt
Marco_Maestro_Direcciones_Multiproposito.txt: text/plain; charset=us-ascii

[ppKrauss]

No entanto a ingestão não acontece. Precisar decidir como lidar com linhas do tipo:

Torcendo para que seja só bug de conversão de charset no linux... Parece que adulterou os dados originais.

[ppKrauss]

Duplicação de pontos e complemento

Talvez não sejam tantos endereços assim, mas a palavra "PISO" evidencia que a string de endereço está incluindo o complemento, o que nos força a escolher um dentre vários endereços horizontais repetidos.

select  st_geohash( ST_SetSRID(ST_MakePoint( replace(longitud,',','.')::float, replace(latitud,',','.')::float) , 4326), 8) as geohash, count(*) n, array_agg(direccion_normalizada)  from mmdm12345 where direccion_normalizada  like '%PISO%' group by 1 having count(*)>1 order by 2 desc

Exemplo de repetição com margem de metros:

geohash8	n	prefixo-comum { endereços }
d2g69pmx	10	CL 64B 105H {"CL 64B 105G 03 CASATERCERPISO","KR 105G 64A 09 CASASEGUNDOPISO","CL 64B 105H 21 CASATERCERPISO","CL 64B 105H 15 CASATERCERPISO","CL 64B 105H 09 CASASEGUNDOPISO","CL 64B 105H 05 CASAPRIMERPISO","CL 64B 105G 21 CASAPRIMERPISO","CL 64B 105G 03 CASASEGUNDOPISO","CL 64B 105G 09 CASASEGUNDOPISO PISO","CL 64B 105H 15 SEGUNDOPISO"}
d2g69puv	9	CL 65A 108 {"CL 65A 108 47 CASASEGUNDOPISO 201","CL 65A 108 53 CASASEGUNDOPISO","CL 65A 108 83 CASAPRIMERPISO","CL 65A 108 53 CASATERCERPISO","CL 65A 108 71 CASAPRIMERPISO 101","CL 65A 108 53 CASAPRIMERPISO","CL 65A 108 65 CASASEGUNDOPISO","CL 65A 108 47 CASASEGUNDOPISO 202","CL 65A 108 77 CASAPRIMERPISO"}
d2g66w7c	9	CL 62 13A 25 {"CL 62 13A 25 TERCERPISO 16","CL 62 13A 25 TERCERPISO 13","CL 62 13A 25 TERCERPISO 4","CL 62 13A 25 TERCERPISO 15","CL 62 13A 25 TERCERPISO 3","CL 62 13A 25 TERCERPISO 6","CL 62 13A 25 TERCERPISO 5","CL 62 13A 25 TERCERPISO 7","CL 62 13A 25 TERCERPISO 2"}
d2g69pvn	8	CL 65B 108 {"CL 65B 108 29 CASATERCERPISO","CL 65B 108 41 CASAPRIMERPISO","CL 65B 108 53 CASASEGUNDOPISO","CL 65B 108 53 TERCERPISO","CL 65B 108 35 PISOTERCEROCASA","CL 65B 108 41 CASATERCERPISO","CL 65B 108 41 CASAPISOPRIMER","CL 65B 108 35 CASAPRIMERPISO"}
d2f5qpe6	7	... {"PT 5 SEGUNDOPISO CA 122","PT 5 PRIMERPISO CA 122","PT 5 SEGUNDOPISO CA 123","PT 5 SEGUNDOPISO CA 125","PT 4 PRIMERPISO CA 126","PT 4 130SEGUNDOPISO","PT 4 SEGUNDOPISO CA 126"}
d2g69psr	7	{"CL 64C 108 83 CASASEGUNDOPISO","CL 64C 108 77 CASAPRIMERPISO","CL 64C 108 77 CASASEGUNDOPISO","CL 64C 108 83 CASAPRIMERPISO","CL 64C 108 71 CASATERCERPISO","KR 64 108 71 CASAPRIMERPISO","CL 64C 108 77 CASASEGUNDOPISO 201"}
d2g69ptq	6	{"CL 64F 106A 25 CASAPRIMERPISO","CL 64F 106A 33 CASATERCERPISO","CL 64F 106A 45 CASASEGUNDOPISO","CL 64F 106A 33 CASASEGUNDOPISO","CL 64F 106A 39 CASASEGUNDOPISO","CL 64F 106A 45 CASAPRIMERPISO"}
d2g69ptj	6	{"CL 64C 106A 54 TERCERPISO","CL 64C 106A 53 CASASEGUNDOPISO","KR 108 64C 10 TERCERPISO","CL 64C 106A 54 CASASEGUNDOPISO","CL 64C 106A 74 SEGUNDOPISO","CL 64C 106A 70 CUARTOPISO"}
d29fcku7	6	{"KR 9 S I 10A 5 1 PISO 9 10A 51","CL 10 S I 9 6 0PISO 10 9 60PISO","CL 10A S I 8 1 3PISO 10A 8 13PISO","KR 9 S I 10 2 5 9 10 25PISO","KR 9 S I 10 2 5 9 10 25 PISO","CL 10 S I 8 8 2PISO 10 8 82"}
d2g69pv3	6	{"KR 107C 65A 03 CASACUARTOPISO","CL 65B 107C 11 CASAPRIMERPISO","KR 107C 65 27 CASATERCERPISO","CL 65B 107C 11 CASASEGUNDOPISO","CL 65B 107C 05 CASATERCERPISO","CL 65B 65A 15 CASAPRIMERPISO"}
d347b15s	6	{"KR 76 92 18 TERCERPISO","KR 75B 92 83 TERCETPISO","KR 76 92 96 SEGUNDOPISO","KR 76 92 154 SEGUNDOPISO","KR 75B 92 75 PRIMERPISO","KR 76 92 124 PRIMERPISO"}
d2g69puy	6	{"CL 65A 108 89 CASASEGUNDOPISO","CL 65A 108 83 CASATERCEROPISO","CL 65A 108 83 CASASEGUNDOPISO","CL 65A 108 89 CASAPRIMERPISO","CL 65A 108 77 CASATERCERPISO","CL 65A 108 71 CASASEGUNDOPISO"}
d2g69pug	6	{"CL 64F 108 12 CASAQUINTOPISO","CL 65 108 35 CASAPRIMERPISO","KR 64F 108 18 CASASEGUNDOPISO","CL 65 108 41 CASASEGUNDOPISO","CL 65 108 23 CASASEGUNDOPISO","CL 65 108 41 CASATERCERPISO"}
d347056b	5	{"TV 34 E 32C 76 PRIMERPISO","TV 34DDS 32C 50 SEGUNDOPISO","TV 34 E 32C 26 SEGUNDOPISO","TV 34DD 32C 33 SEGUNDOPISO","TV 34 E 32C 16 SEGUNDOPISO"}
d2g69psv	5	{"CL 64C 106A 73 SEGUNDOPISO","CL 64C 106A 69 PISOSEGUNDO","CL 64C 106A 61 TERCERPISO","CL 64C 106A 77 CASATERCERPISO","CL 65C 106A 69 CASAPRIMERPISO"}
d29fcku2	5	{"CL 10 S I 9 7 0PISO 10 9 70","CL 10 S I 10 6 2 10 10 62PISO","CL 10A S I 8 6 1PISO 10 8 61PISO","CL 10 S I 10 5 0PISO 10 10 50PISO","CL 10 S I 8 11 8PISO 10 8 118"}
d29fcku6	5	{"CL 10A S I 8 6 1PISO 10ANO 8 61PISO","CL 10 S I 8 1 8PISO 10 8 18","CL 10 S I 10A 1 0PISO 10 10A 10PISO","CL 10 S I 10 4 4 10 10 44 PISO","CL 10 S I 9 2 4PISO 10 9 24"}
d2g69pu8	5	{"KR 109A 64C 10 CASAPRIMERPISO","CL 64C 108 84 CASASEGUNDOPISO","CL 64C 108 78 CASAPRIMERPISO","KR 109A 64C 04 CASASEGUNDOPISO 201","CL 64C 108 84 CASATERCERPISO"}
d2g69puu	4	{"CL 65 108 41 CASAPRIMERPISO","CL 65 108 35 CASASEGUNDOPISO","CL 64F 108 12 CASATERCERPISO","CL 65 108 29 CASAPRIMERPISO"}
d347b15t	4	{"CL 92 75B 20 SEGUNDOPISO","KR 76 92 94 TERCERPISO","CL 92 75B 24 TERCERPISO","CL 94 75B 29 PRIMERPISO"}
d2g69pvd	4	{"KR 107C 65A 03 CASATERCERPISO","KR 107C 65 21 CASAPRIMERPISO","KR 107C 65 27 CASAPRIMERPISO","KR 107C 65 21 CASASEGUNDOPISO"}

Os mais repetidos:

geohash8	n
d34k15mg	3112
d2g6dnve	3041
d2d2qyuq	2623
d344zpus	2606
d345p1jm	2582
d347bx8j	2434
d2g63605	2407
d2g607jh	2279
d2g6fxph	2252
d2g4y8tr	2173
d2g6ch05	2090
d34k03fn	2075
d347by11	2059
d2g62nys	2040
d2g4rv3h	2018
d2g6fz1t	1980
d2g6fy9y	1958
d34k04y8	1954
d2g63wtr	1889
d2g71bgb	1816
d2g6bgzu	1813
d2g631xm	1797
d2g4y87z	1786
d2g6d0zk	1779
...
d2g7s4x9	275
d2g741r4	275
...

Consultando o geohash do primeiro, d34k15mg, confirmamos se tratar de fato de um grande conjunto habitacional, Florida Norteamerica:

Aparentemente para todos os outros, o último, d2g741r4, Conjunto Monterredondo:

Reduzindo de Geohash8 (\~15 metros) para Geohash9 (\~5m), as duplicações praticamente não se reduzem, a ordem dos mais repetidos continua praticamente a mesma:

geohash9	n
d34k15mg8 = d34k15mg	3111
d2g6dnvek = d2g6dnve	2995
d2d2qyuqp = d2d2qyuq	2623
d345p1jmz = d345p1jm	2582
d344zpusw = d344zpus	2472
d347bx8jt = ...	2431
d2g63605b	2407
d2g6fxphr	2174
d2g4y8tr4	2173
d2g607jh7	2058
d347by11m	2057
d2g62nysr	2038
d2g4rv3h4	1985
d2g6fy9y3	1949
d34k04y8y	1945
d2g6ch050	1923
d2g6fz1tw	1919
d2g63wtrs	1889
d34k03fns	1817
d2g631xmv	1792
...	...

[0e1]

Alguns minutos atrás iniciei teste da ingestão particionada dos dados, usando o divipola. Cerca de 2 minutos por divipola, nesse inicio. Dados sendo ingeridos na ingest99 e output da ingestão indo para testeloop na pasta do pacote no servidor.

[0e1]

Atualização: dados sem divipola paralizaram a ingestão particionada por divipola. https://github.com/digital-guard/preserv-CO/commit/30ed80c0afa149090573ebeb3ea458680d39113a associa -1 a esses dados. Teste foi reiniciado.

[0e1]

Por problemas na manipulação de gid ocorreu o erro

ERROR: integer out of range

O https://github.com/digital-guard/preserv/commit/7713bee2ea334ee92e2b90611330e0514e2f6a80 corrige a situação. Novo teste iniciado.

[0e1]

como a ingestão já foi realizada pelo menos uma vez, issue obsoleta.