a4a:Convenções/Dados/SQL: mudanças entre as edições

← Edição anterior

a4a:Convenções/Dados/SQL (ver código-fonte)

Edição das 19h59min de 22 de abril de 2024

3 395 bytes adicionados , 22 abril

m

→‎Ver também

Peter

2 402

edições

@@ Linha 1: / Linha 1: @@
-Tabelas da base corrente <code>DL05s_main</code>. Documentação gerada por recursos do "psql" e [https://tableconvert.com/markdown-to-mediawiki tableconvert.com/markdown-to-mediawiki].
+Tabelas da base corrente <code>DL05s_main</code>. Documentação gerada por recursos do "psql" e [https://tableconvert.com/markdown-to-mediawiki tableconvert.com/markdown-to-mediawiki] (ou [https://www.vertopal.com/en/convert/markdown-to-wiki-mediawiki vertopal.com/en/convert/markdown-to-wiki-mediawiki]).
 Ver também [[Usuário:Peter/Modelos endereço]]
@@ Linha 109: / Linha 109: @@
 GROUP by 1,2,3 order by 1, 4 desc
 ; -- Chile (CL) cabe em 27 bits, Brasil (BR) não cabe. Poucos casos valem adaptar exceções.
+-- R3
+SELECT '['||isolabel_ext||'](https://afa.codes/'||isolabel_ext||')' as AFAcode_map,
+       name,
+       jurisd_local_id as local_id,
+       '['||osm_id||'](https://osm.org/relation/'||osm_id::text||')' as osm_id,
+       '['||wikidata_id::text||'](https://wikidata.org/entity/Q'||wikidata_id::text||')' as wikidata_id
+FROM optim.jurisdiction
+ORDER BY jurisd_local_id, isolabel_ext;
 </syntaxhighlight>
@@ Linha 125: / Linha 134: @@
 * osmc.jurisdictions_select: JSON para interface de seletor de jurisdição.
 * public.qwerty: (resgatar para optim) gerador de comando bash "curl" para criar CSVs de todas as jurisdições.
+'''R0a-packs''': select isolabel_ext, legalname as donor_name,shortname,  housenumber_system_type as hn_sys, pack_number,license_data->'family' as lic_fam,  jsonb_object_keys_asarray(make_conf_tpl->'layers') as layers from optim.vw01full_donated_packtpl where shortname!='OSM' order by packtpl_id
 '''R0b'''. Funções que fazem uso da tabela:
@@ Linha 231: / Linha 242: @@
 |-
 | CL   ||        1 ||    0 ||    1
+|}
+<span id="optim.jurisdiction.R3"></span>'''R3'''. Listagem simples Markdown para relatórios internos e conferir se IDs batem com links.
+{| class="wikitable"
+!width="38%"| afacode_map
+!width="14%"| name
+!width="4%"| local_id
+!width="19%"| osm_id
+!width="22%"| wikidata_id
+|-
+| [https://afa.codes/BR BR]
+| Brasil
+| 0
+| [https://osm.org/relation/59470 59470]
+| [https://wikidata.org/entity/Q155 155]
+|-
+| [https://afa.codes/BR-RO BR-RO]
+| Rondônia
+| 11
+| [https://osm.org/relation/325866 325866]
+| [https://wikidata.org/entity/Q43235 43235]
+|-
+| [https://afa.codes/BR-RO-AltaFlorestaOeste BR-RO-AltaFlorestaOeste]
+| Alta Floresta D’Oeste
+| 1100015
+| [https://osm.org/relation/325842 325842]
+| [https://wikidata.org/entity/Q1761456 1761456]
+|-
+| [https://afa.codes/BR-RO-Ariquemes BR-RO-Ariquemes]
+| Ariquemes
+| 1100023
+| [https://osm.org/relation/325888 325888]
+| [https://wikidata.org/entity/Q1646975 1646975]
+|-
+| [https://afa.codes/BR-RO-Cabixi BR-RO-Cabixi]
+| Cabixi
+| 1100031
+| [https://osm.org/relation/325882 325882]
+| [https://wikidata.org/entity/Q940433 940433]
+|-
+| [https://afa.codes/BR-RO-Cacoal BR-RO-Cacoal]
+| Cacoal
+| 1100049
+| [https://osm.org/relation/325872 325872]
+| [https://wikidata.org/entity/Q1761637 1761637]
+|-
+|...||...||...||...||...
+|-
+| [https://afa.codes/BR-AC BR-AC]
+| Acre
+| 12
+| [https://osm.org/relation/326266 326266]
+| [https://wikidata.org/entity/Q40780 40780]
+|-
+| [https://afa.codes/BR-AC-Acrelandia BR-AC-Acrelandia]
+| Acrelândia
+| 1200013
+| [https://osm.org/relation/326273 326273]
+| [https://wikidata.org/entity/Q953086 953086]
+|-
+| [https://afa.codes/BR-AC-AssisBrasil BR-AC-AssisBrasil]
+| Assis Brasil
+| 1200054
+| [https://osm.org/relation/326269 326269]
+| [https://wikidata.org/entity/Q1754403 1754403]
+|-
+| [https://afa.codes/BR-AC-Brasileia BR-AC-Brasileia]
+| Brasiléia
+| 1200104
+| [https://osm.org/relation/326270 326270]
+| [https://wikidata.org/entity/Q899406 899406]
+|-
+| [https://afa.codes/BR-AC-Bujari BR-AC-Bujari]
+| Bujari
+| 1200138
+| [https://osm.org/relation/326259 326259]
+| [https://wikidata.org/entity/Q1754408 1754408]
+|-
+| [https://afa.codes/BR-AC-Capixaba BR-AC-Capixaba]
+| Capixaba
+| 1200179
+| [https://osm.org/relation/326268 326268]
+| [https://wikidata.org/entity/Q1800737 1800737]
+|-
+| [https://afa.codes/BR-AC-CruzeiroSul BR-AC-CruzeiroSul]
+| Cruzeiro do Sul
+| 1200203
+| [https://osm.org/relation/326274 326274]
+| [https://wikidata.org/entity/Q941136 941136]
 |}
@@ Linha 629: / Linha 730: @@
 $f$ LANGUAGE SQL IMMUTABLE;
 </pre>
+------
+==  Ver também ==
+* Criação ou Reconstrução de qualquer DL (teste ou   produção)  https://github.com/digital-guard/preserv/wiki/Datalake