a4a:Convenções/Dados/Export

Exemplos de views e demais operações para exportação dos dados finais Addressforall para outros formatos: OSM, OpenAdresses, ESRI, OvertureMaps.

Openaddresses.io

Conforme git/openaddresses/CONTRIBUTING.md e versão de julho de 2023 de Cartago, podemos importar e exportar:

DROP FOREIGN TABLE IF EXISTS co_marco202307_limpo_fdw CASCADE
;
CREATE FOREIGN TABLE co_marco202307_limpo_fdw (
 geohash9 text,
 dpto_mpio int,
 via_name text,
 house_number text,
 complemento text,
 lat float,
 lon float,
 n_originales int,
 has_dup_address boolean
)
SERVER files OPTIONS
 (filename '/tmp/mnd_uniq_good.csv', format 'csv', header 'true', delimiter ',')
;
------
DROP VIEW IF EXISTS co_marco202307_limpo_cartago_vw
;
CREATE VIEW co_marco202307_limpo_cartago_vw AS 
 SELECT * FROM (
   SELECT house_number AS number,
        via_name AS street,
        NULL as unit,
        lat, lon,
        'Cartago' AS city,
        NULL AS postcode,
        NULL AS district,
        'VAC' AS region, -- Valle del Cauca
        geohash9 AS id,
        -- ('01'::bit(2) || natcod.b32ghs_to_vbit(geohash9))::bit(64)::bigint AS id,
        NULL AS addrtype,
        NULL AS notes
   FROM co_marco202307_limpo_fdw
   WHERE NOT(has_dup_address) AND dpto_mpio=76147
 ) t
 ORDER BY id
;

copy (select * from co_marco202307_limpo_cartago_vw) to '/tmp/co202307_cartago_ghs.csv' CSV HEADER;
-- copy (select * from co_marco202307_limpo_cartago_vw) to '/tmp/co202307_cartago_int.csv' CSV HEADER;

Para o país inteiro, fazer o JOIN:

DROP VIEW IF EXISTS co_marco202307_limpo_vw
;
CREATE VIEW co_marco202307_limpo_vw AS 
    SELECT house_number AS number,
        via_name AS street,
        NULL as unit,
        lat, lon,
        j.name AS city,
        NULL AS postcode,
        NULL AS district,
        j.parent_abbrev AS region, -- Valle del Cauca
        geohash9 AS id,
        -- ('01'::bit(2) || natcod.b32ghs_to_vbit(geohash9))::bit(64)::bigint AS id,
        NULL AS addrtype,
        NULL AS notes
   FROM co_marco202307_limpo_fdw m INNER JOIN optim.jurisdiction j 
     ON j.jurisd_base_id=170 AND j.jurisd_local_id=m.dpto_mpio 
   WHERE NOT(has_dup_address)
 ORDER BY geohash9
;
copy (select * from co_marco202307_limpo_vw) to '/tmp/co202307_ghs.csv' CSV HEADER;