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

De Documentação
mSem resumo de edição
(Página substituída por 'RESOLVIDO! Foi para a4a:Convenções/Dados/SQL')
Etiqueta: Substituído
 
(7 revisões intermediárias pelo mesmo usuário não estão sendo mostradas)
Linha 1: Linha 1:
Tabelas da base corrente dl05. Documentação gerada por recursos do "psql" e [https://tableconvert.com/markdown-to-mediawiki tableconvert.com/markdown-to-mediawiki].
RESOLVIDO!  Foi para [[a4a:Convenções/Dados/SQL]]
 
== Table <code>osmc.coverage</code> ==
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. 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"
! Column !! Type !! Collation / Nullable / Default !! Description
|-
| cbits || bit varying ||  / /|| identificador hierárquico de célula, grade local.
|-
| isolabel_ext || text || / / || identificador da jurisdição contida na célula (ISO 3166-2  estendido para municípios).
|-
| cindex || text || / /|| contador (índice) da jurisdição, dentro da mesma célula.
|-
| bbox || integer[] ||  / / || BBOX da célula na sua projeção local. Torna a verificação geométrica mais rápida.
|-
| status || smallint ||  /  / 0|| status 0 a 2, pendente conferir semântica.
|-
| is_country || boolean ||  /  / false|| flag indicador de cobertura nacional.
|-
| is_contained || boolean || /  / false|| flag indicador de que a célula está totalmente contida na jurisdição (pendente revisar contradições).
|-
| is_overlay || boolean ||  /  / false|| flag indicador de cobertura tipo overlay (pode ser ignorada na definição de jurisdição).
|-
| kx_prefix || text ||  / /|| expressão do prefixo local (sem ID nação) em base16h, grade científica.
|-
| geom || geometry ||  / / || geometria na projeção local.
|-
| geom_srid4326 || geometry ||  / /  || geometria LatLong (WGS84).
|}
<pre>
Indexes:
    "osm_coverage_cbits10true_idx" btree ((cbits::bit(10))) WHERE is_country IS TRUE
    "osm_coverage_cbits15false_idx" btree ((cbits::bit(14)), isolabel_ext) WHERE is_country IS FALSE
    "osm_coverage_geom4326_idx1" gist (geom_srid4326)
    "osm_coverage_geom_idx1" gist (geom)
    "osm_coverage_isolabel_ext_false_idx" btree (isolabel_ext) WHERE is_country IS FALSE
    "osm_coverage_isolabel_ext_idx1" btree (isolabel_ext)
    "osm_coverage_isolabel_ext_true_idx" btree (isolabel_ext) WHERE is_country IS TRUE
Check constraints:
    "coverage_status_check" CHECK (status = ANY (ARRAY[0, 1, 2]))
</pre>
=== Queries ===
Queries que geram reports e sugestão de análise das views próprias.
<syntaxhighlight lang="sql">
-- R0
select * from optim.admin_views_dependency_summary where ref_tab_name='osmc.coverage';
 
-- R1
select distinct
  substring(isolabel_ext,1,2) as country,
  natcod.vbit_to_baseh(substring(cbits,1,8),16,true) as cbits_b16h,
  is_country, status,
  count(*) n, count(distinct isolabel_ext) n_jurisds
from  osmc.coverage
group by 1,2,3,4
order by 1,2,3 desc,4;
 
-- 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>
 
=== Reports ===
'''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"
! country !! cbits_b16h !! is_country !! status !! n !! n_jurisds
|-
| BR || 13 || t || 1 || 18 || 1
|-
| BR || 13 || f || 0 || 72726 || 5570
|-
| CM || 1e || t || 1 || 14 || 1
|-
| CM || 1e || f || 0 || 2738 || 360
|-
| CO || 2a || t || 1 || 16 || 1
|-
| CO || 2a || f || 0 || 15244 || 1115
|-
| CO || 2a || f || 2 || 22 || 1
|-
| EC || 36 || t || 1 || 22 || 1
|-
| UY || d6 || t || 1 || 19 || 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, municípios de "CO-ARA". As coberturas-overlay ainda não aparecem no git, e aqui apenas a célula de um ponto é utilizada.
 
{| class="wikitable"
! isolabel_ext !! is_overlay !! n !! cover
|-
| CO-ARA-Arauca || f || 14 || {cd4,cd5,cd6,cd7,e2a,e2e,e2f,e80,e81,e82,e83,e84,e85,e86}
|-
| CO-ARA-Arauca || t || 1 || {cd7fG}
|-
| CO-ARA-Arauquita || f || 9 || {c7e,c7f,cd0,cd1,cd2,cd3,cd4,cd5,cd6}
|-
| CO-ARA-Arauquita || t || 1 || {cd3cQ}
|-
| CO-ARA-CravoNorte || f || 13 || {e28,e2a,e2b,e2c,e2d,e2e,e2f,e38,e3a,e80,e81,e84,e85}
|-
| CO-ARA-CravoNorte || t || 1 || {e2b5Q}
|-
| CO-ARA-Fortul || f || 5 || {c6f,cc5,cd0,cd1,cd2}
|-
| CO-ARA-Fortul || t || 1 || {cd0bG}
|-
| CO-ARA-PuertoRondon || f || 9 || {c7d,c7e,c7f,cd1,cd4,cd5,e28,e2a,e80}
|-
| CO-ARA-PuertoRondon || t || 1 || {c7e4Q}
|-
| CO-ARA-Saravena || f || 4 || {cc5,cc7,cd0,cd2}
|-
| CO-ARA-Saravena || t || 1 || {cd22Q}
|-
| CO-ARA-Tame || f || 13 || {c6c,c6d,c6e,c6f,c78,c79,c7a,c7b,c7e,cc5,cd0,cd1,cd4}
|-
| CO-ARA-Tame || t || 1 || {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>

Edição atual tal como às 03h04min de 8 de fevereiro de 2024

RESOLVIDO! Foi para a4a:Convenções/Dados/SQL

Disponível em “https://wiki.addressforall.org/w/index.php?title=Usuário:Peter/Lembretes_SQL_e_docs&oldid=3620”