Usuário:Peter/Lembretes SQL e docs

Tabelas da base corrente DL05s_main. Documentação gerada por recursos do "psql" e tableconvert.com/markdown-to-mediawiki.

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.

-- R0a
select * from optim.admin_views_dependency_summary where ref_tab_name='osmc.coverage'
;
-- R0b
select schema_name, name, arguments_simplified , return_type, oid
from doc_UDF_using_a_table('osmc.cover') t order by 1,2,3
;

-- 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

R0a. 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.

R0b. Funções que fazem uso desta tabela. (removidas as variantes "EC" e "UY")

schema_name	name	arguments_simplified	return_type
api	jurisdiction_coverage	{text,integer}	jsonb
api	jurisdiction_geojson_from_isolabel	{text}	jsonb
api	osmcode_decode_postal	{text,text}	jsonb
api	osmcode_decode_postal_absolute	{text,text}	jsonb
api	osmcode_decode_scientific_absolute	{text,text,integer}	jsonb
api	osmcode_encode	{text,integer}	jsonb
osmc	afa_log_to_hbig	{text,text}	int8
osmc	cellcontainsproperly	{text,text,integer,text,integer}	text
osmc	check_coverage	{text,ARRAY,text,ARRAY,ARRAY,ARRAY,ARRAY,boolean,ARRAY}	record
osmc	cover_child_geometries	{text,text,integer,text,text,USER-DEFINED}	record
osmc	encode_point_brazil	{USER-DEFINED}	text
osmc	encode_point_colombia	{USER-DEFINED}	text
osmc	encode_postal_br	{USER-DEFINED,"double precision",integer,text}	jsonb
osmc	encode_postal_cm	{USER-DEFINED,"double precision",integer,text}	jsonb
osmc	encode_postal_co	{USER-DEFINED,"double precision",integer,text}	jsonb
osmc	encode_scientific_br	{USER-DEFINED,"double precision",integer}	jsonb
osmc	encode_scientific_cm	{USER-DEFINED,"double precision",integer}	jsonb
osmc	encode_scientific_co	{USER-DEFINED,"double precision",integer}	jsonb
osmc	encode_short_code	{text,"bit varying",text,USER-DEFINED,text,integer,text}	record
osmc	generate_cover_csv	{text,text}	text
osmc	l0_upsert	{text,smallint,integer,integer,integer,integer,integer,ARRAY,ARRAY}	text
osmc	l0cover_upsert_br	{NULL}	text
osmc	l0cover_upsert_co	{NULL}	text
osmc	update_coverage_isolevel3	{text,smallint,ARRAY,ARRAY}	text

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, municípios de "CO-ARA". As coberturas-overlay ainda não aparecem no git, e aqui apenas a célula de um ponto é utilizada.

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

CREATE VIEW optim.admin_views_dependency AS
 -- https://stackoverflow.com/a/69473861/287948
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
;
-- DROP VIEW optim.admin_udf_summary;
CREATE VIEW optim.admin_udf_summary AS
 SELECT  n.oid, quote_ident(n.nspname) as schema , quote_ident(p.proname) as function,
         pg_get_function_result(n.oid) as fres
 FROM   pg_catalog.pg_proc p
 JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
 WHERE  n.nspname not like 'pg%'
;

CREATE or replace FUNCTION doc_UDF_using_a_table(
  p_table text,
  p_schema_name text DEFAULT NULL,
  p_name_like text DEFAULT '',
  p_name_notlike text DEFAULT '',
  p_oid oid DEFAULT NULL
) RETURNS TABLE (
  id text,
  oid oid,
  schema_name text,
  name text,
  language text, --new
  definition_md5 text, -- version control
  arguments_simplified text[],
  arguments text,
  return_type text,
  prokind text,
  comment text
) AS $f$
  SELECT doc_UDF_transparent_id(u.schema_name, u.name::text, s.arguments_simplified::text[]) AS id,
         u.oid, u.schema_name, u.name::text,
         u.language, md5(u.definition),
         s.arguments_simplified::text[] as arguments_simplified,
         u.arguments::text AS arguments,
         u.return_type, u.prokind, u.comment
  FROM doc_UDF_show_simplified_signature($2,$3,$4) s
      INNER JOIN doc_UDF_show($2,$3,$4,$5) u ON s.oid=u.oid::text
  WHERE u.definition ilike '%'|| p_table ||'%'
$f$ LANGUAGE SQL IMMUTABLE;

----
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
;