Usuário:Peter/Lembretes SQL e docs
Table osmc.coverage
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??
Exemplos: BR_new/data/coverage.csv, CO_new/data/coverage.csv. As colunas "status" e "cover" são copiadas em "status" e "kx_prefix".
| 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). |
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]))
Queries
Queries que geram reports e sugestão de análise das views próprias.
-- 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;
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.
| 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.
PS: ignorando EC e UY, que não estão configurados.
| 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".
| 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
| 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
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
;