Comments (2)
Cuidado, incluir ordenada,
COPY (
SELECT c.name, c.state, c.wdid as "wdId", c.idibge as "idIBGE",
c.lexlabel as "lexLabel", c.creation, c.extinction,
c.postalcode_ranges as "postalCode_ranges",
ddd.ddd, c.notes
FROM dataset.vw8_anatel_ddd as ddd RIGHT JOIN dataset.vw2_br_city_codes c
ON c.state=ddd.uf AND c.lexlabel=ddd.namelex -- or synonym!
ORDER BY std_collate(name), name, state,3
) TO '/tmp/test.csv' CSV HEADER;
E fica ainda pendente o tratamento de sinônimos
from city-codes.
Concluído com sinônimos:
-- SOLUCAO correta fazendo inclusão da resolução por sinônimo:
COPY (
WITH dd AS (
SELECT DISTINCT c.name, c.state, ddd.ddd
FROM dataset.vw8_anatel_ddd as ddd
INNER JOIN dataset.vw2_br_city_codes_tojoin_synonyms c
ON c.state=ddd.uf AND c.lexlabel_join=ddd.namelex AND NOT(c.is_original)
)
SELECT name, state, wdid as "wdId", idibge as "idIBGE",
lexlabel as "lexLabel", creation, extinction,
postalcode_ranges as "postalCode_ranges",
CASE WHEN ddd IS NULL -- tenta resolver por sinônimo
THEN (SELECT ddd FROM dd WHERE dd.name=t.name AND dd.state=t.state)
ELSE ddd
END,
notes
FROM (
SELECT c.name, c.state, c.wdid, c.idibge, c.lexlabel, c.creation, c.extinction,
c.postalcode_ranges, ddd.ddd, c.notes
FROM dataset.vw8_anatel_ddd as ddd RIGHT JOIN dataset.vw2_br_city_codes c
ON c.state=ddd.uf AND c.lexlabel=ddd.namelex
) t ORDER BY std_collate(name), name, state, 3
) TO '/tmp/test_good.csv' CSV HEADER;
from city-codes.
Related Issues (20)
- Implementar filtro de restrição de itens no dump_wikidata HOT 1
- Corrigindo consistência na Wikidata
- Wikidata area code for all cities of Brasil HOT 3
- Conferir e substituir suspeitos de erro ou duplicidade na Wikidata HOT 1
- Fixar conjunto reduzido de propriedades ou excluir P18
- incluir coluna de abreviações ANATEL2013
- usar versao nova do lexLabel
- Incluir abreviação de 3 letras por uf HOT 2
- acertar o novo script
- Falha na fonte de GeoJSON HOT 1
- incluir campos novos de área, localização e população
- Lagoa dos Patos na contabilidade de áreas municipais
- Código IBGE de brasília
- Incluir abreviação de 3 letras de MT
- Incluir abreviação de 3 letras de MG
- Código TOM do Município HOT 1
- Atualizar até setembro de 2019 HOT 5
- Inventário das alterações toponímicas e territoriais
- Incluir URI da DBPedia do município
- Pinhais-PR está sem código
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from city-codes.