Documentação

Usuário:Peter/Lembretes SQL e docs: mudanças entre as edições

Página do usuário Discussão

Usuário:Peter/Lembretes SQL e docs (ver código-fonte)

Edição das 14h02min de 4 de fevereiro de 2024

4 753 bytes adicionados ,  4 fevereiro
m
ate R3
mSem resumo de edição
m (ate R3)
Linha 3: Linha 3:
Coberturas nacionais e de suas jurisdições. As geometrias nacionais são expressas em LatLong, as locais conforme sua projeção oficial.
Coberturas nacionais e de suas jurisdições. As geometrias nacionais são expressas em LatLong, as locais conforme sua projeção oficial.


Ingestão: arquivos "coverage.csv" do git. <br/> Exemplos: [https://git.osm.codes/BR_new/blob/main/data/coverage.csv BR_new/data/coverage.csv], [https://git.osm.codes/CO_new/blob/main/data/coverage.csv CO_new/data/coverage.csv].
Ingestão: arquivos "coverage.csv" do git. Função ou make para construção?? <br/> Exemplos: [https://git.osm.codes/BR_new/blob/main/data/coverage.csv BR_new/data/coverage.csv], [https://git.osm.codes/CO_new/blob/main/data/coverage.csv CO_new/data/coverage.csv]. As colunas "status" e "cover" são copiadas em "status" e "kx_prefix".


{| class="wikitable"
{| class="wikitable"
Linha 43: Linha 43:
</pre>
</pre>
=== Queries ===
=== Queries ===
Queries que geram reports e sugestão de análise das views próprias.
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
-- R0
select * from optim.admin_views_dependency_summary where ref_tab_name='osmc.coverage';
-- R1  
-- R1  
select distinct  
select distinct  
Linha 54: Linha 58:
order by 1,2,3 desc,4;
order by 1,2,3 desc,4;


-- R2
-- R2a
select substring(isolabel_ext,1,2) as country, is_country, is_overlay,
      count(*) n, count(distinct isolabel_ext) n_jurisds, count(distinct cbits) n_cells,
      min(l) l_min, max(l) l_max, round(avg(l),2) l_avg,
      PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY l) l_mdn
from (select *, round(length(cbits)/4)::int as l from osmc.coverage) t
group by 1,2,3 order by 1,2 desc,3;
 
-- R2b
select substring(isolabel_ext,1,2) as country, is_country, is_overlay,
      count(*) n, count(distinct isolabel_ext) n_jurisds,
      count(distinct kx_prefix) n_cells, count(distinct kx_prefix||'.'||cindex::text) n_cells_idx,
      count(distinct kx_prefix||'.'||isolabel_ext) n_cells_jur,
      min(l) l_min, max(l) l_max, round(avg(l),2) l_avg,
      PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY l) l_mdn
from (select *, length(kx_prefix) as l from osmc.coverage) t
group by 1,2,3 order by 1,2 desc,3;
 
-- R3
select isolabel_ext, is_overlay,
      array_agg( natcod.vbit_to_baseh(substring(cbits,11),16,true)  order by cbits) cover
from osmc.coverage
where isolabel_ext like 'CO-ARA-%'
group by 1,2 order by 1,2;
</syntaxhighlight>
</syntaxhighlight>
=== Reports ===
=== Reports ===
R1. Sumário do conteúdo corrente (final de 2023). Diferencia coberturas nacionais, expressa contagem de células e contagem de jurisdições.
'''R0'''. Views que fazem uso desta tabela:
* osmc.jurisdictions_select
* osmc.tmpvw10: temporária
* osmc.tmpvwpoeira: indica "sobras" dos polígonos que não foram aceitas como cobertura válida, por serem menores que 100 metros quadrados.
 
'''R1'''. Sumário do conteúdo corrente (final de 2023). Diferencia coberturas nacionais, expressa contagem de células e contagem de jurisdições.
   
   
{| class="wikitable"
{| class="wikitable"
Linha 82: Linha 115:
| UY || d6 || f || 0 || 1 || 1
| UY || d6 || f || 0 || 1 || 1
|}
|}
'''R2a'''. Tamanho mais frequente (mediana "mdn") da cobertura. Comprimento ''l'' dado em quantidade de dígitos hexadecimais. <br/>PS: ignorando  EC e UY, que não estão configurados.
{| class="wikitable"
! country !! is_country !! is_overlay !! n !! n_jurisds !! l_min !! l_max !! l_avg !! l_mdn
|-
| BR || t || f || 18 || 1 || 4 || 4 || 4.00 || 4
|-
| BR || f || f || 72565 || 5570 || 5 || 9 || 7.77 || 8
|-
| BR || f || t || 161 || 11 || 7 || 9 || 8.11 || 8
|-
| CM || t || f || 14 || 1 || 3 || 3 || 3.00 || 3
|-
| CM || f || f || 2730 || 360 || 3 || 7 || 5.48 || 5
|-
| CM || f || t || 8 || 1 || 7 || 7 || 7.00 || 7
|-
| CO || t || f || 16 || 1 || 3 || 3 || 3.00 || 3
|-
| CO || f || f || 14165 || 1116 || 4 || 8 || 5.82 || 6
|-
| CO || f || t || 1101 || 1091 || 6 || 9 || 7.39 || 8
|}
R2b. Mesmo que R2a porém usando "kx_prefix" direto. Linhas is_country removidas pois não usam "kx_prefix".
{| class="wikitable"
! country !! is_ctry !! is_ovly !! n !! n_jurisds !! n_cells !! n_cel_idx !! n_cel_jur !! l_min !! l_max !! l_avg !! l_mdn
|-
| BR || f || f || 72565 || 5570 || 40933 || 69557 || 72565 || 2 || 5 || 3.77 || 4
|-
| BR || f || t || 161 || 11 || 161 || 161 || 161 || 3 || 5 || 4.11 || 4
|-
| CM || f || f || 2730 || 360 || 1411 || 2302 || 2626 || 2 || 5 || 3.50 || 3
|-
| CM || f || t || 8 || 1 || 4 || 4 || 4 || 5 || 5 || 5.00 || 5
|-
| CO || f || f || 14165 || 1116 || 7552 || 13530 || 14165 || 2 || 5 || 3.82 || 4
|-
| CO || f || t || 1101 || 1091 || 1100 || 1100 || 1101 || 4 || 6 || 4.70 || 5
|}
'''R3''. Para reproduzir a planilha CSV precisamos agrupar cbits e converte-los em base16h. Exemplo
{| class="wikitable"
! isolabel_ext !! is_overlay !! cover
|-
| CO-ARA-Arauca || f || {cd4,cd5,cd6,cd7,e2a,e2e,e2f,e80,e81,e82,e83,e84,e85,e86}
|-
| CO-ARA-Arauca || t || {cd7fG}
|-
| CO-ARA-Arauquita || f || {c7e,c7f,cd0,cd1,cd2,cd3,cd4,cd5,cd6}
|-
| CO-ARA-Arauquita || t || {cd3cQ}
|-
| CO-ARA-CravoNorte || f || {e28,e2a,e2b,e2c,e2d,e2e,e2f,e38,e3a,e80,e81,e84,e85}
|-
| CO-ARA-CravoNorte || t || {e2b5Q}
|-
| CO-ARA-Fortul || f || {c6f,cc5,cd0,cd1,cd2}
|-
| CO-ARA-Fortul || t || {cd0bG}
|-
| CO-ARA-PuertoRondon || f || {c7d,c7e,c7f,cd1,cd4,cd5,e28,e2a,e80}
|-
| CO-ARA-PuertoRondon || t || {c7e4Q}
|-
| CO-ARA-Saravena || f || {cc5,cc7,cd0,cd2}
|-
| CO-ARA-Saravena || t || {cd22Q}
|-
| CO-ARA-Tame || f || {c6c,c6d,c6e,c6f,c78,c79,c7a,c7b,c7e,cc5,cd0,cd1,cd4}
|-
| CO-ARA-Tame || t || {c7acQ}
|}
== Outros lembretes ==
<pre>
CREATE VIEW optim.admin_views_dependency AS
select
  u.view_schema schema_name,
  u.view_name,
  u.table_schema referenced_table_schema,
  u.table_name referenced_table_name,
  v.view_definition
from information_schema.view_table_usage u
join information_schema.views v on u.view_schema = v.table_schema
  and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')
order by u.view_schema, u.view_name
;
CREATE VIEW optim.admin_views_dependency_summary AS
select schema_name||'.'||view_name as vw_name, referenced_table_schema||'.'||referenced_table_name as ref_tab_name
from optim.admin_views_dependency
;
</pre>
Peter
2 391

edições

Disponível em “https://wiki.addressforall.org/doc/Especial:MobileDiff/3568”