a4a:Convenções/Dados/Export

Openaddresses.io

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

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;