Brazil/BR-Estável
< Brazil
Jump to navigation
Jump to search
Projeto para a preservação digital do OSM Brasil. Por partes e consolidando com Wikidata.
UFs
Depois de
osmosis --read-pbf file=brazil-latest.osm.pbf --tag-filter \
accept-relations admin_level=4,boundary=administrative \
--write-pgsql host=localhost database=pgsnapshot \
user=postgres password="myPass" &
Rodando
create view relations_uf as
SELECT tags->'ref' uf, id osm_id, tags->'name' nome, tags->'IBGE:GEOCODIGO' id_ibge,
tags->'wikidata' id_wikidata
FROM relations
WHERE tags->'IBGE:GEOCODIGO' is not null and tags->'ref' is not null
order by 1
;
SELECT r.uf, m.member_type, r.osm_id, count(*) n
FROM relation_members m INNER JOIN relations_uf r
ON m.relation_id=r.osm_id
group by 1,2,3 order by 1,2,3
; -- tabela
Obtemos a tabela esperada dos 27 UFs.