a4a talk:Consolidação

Lembretes

https://tableconvert.com/markdown-to-mediawiki

select array_agg(distinct isolabel_ext order by isolabel_ext) from optim.consolidated_data2;
-- {BR-CE-Fortaleza,BR-MG-BeloHorizonte,BR-MG-Contagem,BR-SP-Jundiai}

select array_agg(distinct isolabel_ext order by isolabel_ext) from optim.jurisdiction
WHERE isolabel_ext like '%SaoBentoSapucai' OR isolabel_ext like '%NovaFriburgo' OR isolabel_ext like '%BoaVista';

select isolabel_ext, 
       info->'area_km2' AS area_km2, info->'side_estim_km' AS side_estim_km, info->'is_capital' as is_cap,
       info->'population'->>'population' as pop2021,  info->'postalCode_ranges' as postalCode_ranges
from optim.jurisdiction -- ou optim.vw01full_jurisdiction_geom
WHERE isolabel_ext IN ('BR-RJ-NovaFriburgo', 'BR-RR-BoaVista', 'BR-SP-Jundiai', 'BR-SP-SaoBentoSapucai', 'BR-CE-Fortaleza', 'BR-MG-BeloHorizonte', 'BR-MG-Contagem') order by 1;

--
select '[https://AFA.codes/'||isolabel_ext||' '||isolabel_ext||']' as isolabel_ext,
       name, 
       jurisd_local_id as ibge_id, ddd,
       '[https://www.openstreetmap.org/relation/'||osm_id::text||' '||osm_id::text||']' as osm_id,
       '[https://www.wikidata.org/wiki/Q'||wikidata_id::text||' '||wikidata_id::text||']' as wikidata_id,
       housenumber_system_type
from optim.jurisdiction -- ou optim.vw01full_jurisdiction_geom                                            
WHERE isolabel_ext IN ('BR-RJ-NovaFriburgo', 'BR-RR-BoaVista', 'BR-SP-Jundiai', 'BR-SP-SaoBentoSapucai', 'BR-CE-Fortaleza', 'BR-MG-BeloHorizonte', 'BR-MG-Contagem') order by 1;

 
select isolabel_ext from  optim.vw01full_donated_packtpl
WHERE isolabel_ext IN ('BR-RJ-NovaFriburgo', 'BR-RR-BoaVista', 'BR-SP-Jundiai', 'BR-SP-SaoBentoSapucai', 'BR-CE-Fortaleza', 'BR-MG-BeloHorizonte', 'BR-MG-Contagem') order by 1;

-- pacotes:
select isolabel_ext, substring(packtpl_id::text,5) AS pack_id,
       '['|| path_cutgeo_git ||' GeoJSON]' AS cutgeo_git_link,
       '['|| (license_data->>'url')|| ' '|| (license_data->>'family') || ']' as license
from  optim.vw01full_donated_packtpl
WHERE isolabel_ext IN ('BR-RJ-NovaFriburgo', 'BR-RR-BoaVista', 'BR-SP-Jundiai', 'BR-SP-SaoBentoSapucai', 'BR-CE-Fortaleza', 'BR-MG-BeloHorizonte', 'BR-MG-Contagem') order by 1;