2 583
edições
(Criou página com '== Openaddresses.io == Conforme [https://github.com/openaddresses/openaddresses/blob/master/CONTRIBUTING.md git/openaddresses/CONTRIBUTING.md] e versão de julho de 2023 de Cartago, podemos importar e exportar: <pre> 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, N...') |
mSem resumo de edição |
||
Linha 2: | Linha 2: | ||
Conforme [https://github.com/openaddresses/openaddresses/blob/master/CONTRIBUTING.md git/openaddresses/CONTRIBUTING.md] e versão de julho de 2023 de Cartago, podemos importar e exportar: | Conforme [https://github.com/openaddresses/openaddresses/blob/master/CONTRIBUTING.md git/openaddresses/CONTRIBUTING.md] e versão de julho de 2023 de Cartago, podemos importar e exportar: | ||
< | <syntaxhighlight lang="sql"> | ||
DROP VIEW IF EXISTS co_marco202307_limpo_cartago_vw | DROP VIEW IF EXISTS co_marco202307_limpo_cartago_vw | ||
; | ; | ||
Linha 14: | Linha 14: | ||
NULL AS postcode, | NULL AS postcode, | ||
NULL AS district, | NULL AS district, | ||
' | 'VAC' AS region, -- Valle del Cauca | ||
geohash9 AS id, | geohash9 AS id, | ||
-- ('01'::bit(2) || natcod.b32ghs_to_vbit(geohash9))::bit(64)::bigint AS id, | -- ('01'::bit(2) || natcod.b32ghs_to_vbit(geohash9))::bit(64)::bigint AS id, | ||
Linha 27: | Linha 27: | ||
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_ghs.csv' CSV HEADER; | ||
-- copy (select * from co_marco202307_limpo_cartago_vw) to '/tmp/co202307_cartago_int.csv' CSV HEADER; | -- copy (select * from co_marco202307_limpo_cartago_vw) to '/tmp/co202307_cartago_int.csv' CSV HEADER; | ||
</ | </syntaxhighlight> | ||
Para o país inteiro, fazer o JOIN: | |||
<syntaxhighlight lang="sql"> | |||
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; | |||
</syntaxhighlight> |
edições