osmc talk:Metodologia/Algoritmo SQL: mudanças entre as edições

Edição atual tal como às 00h30min de 15 de junho de 2024

Chutando o quadrado inicial de um país

ver osmc:Metodologia/Algoritmo_SQL/Issues#Issue_02_-_Função_estimadora_de_cobertura_interior_e_quadrado_envolvente

Reengenharia das chamadas API

Ver também osmc:Api. Com base no site online podemos recuperar as APIs... Ideal seria ter isso tudo no Swagger, Claiton já fez isso.

Revisão das configuração do país

A configuração é efetivada pela chamada com o país específico, por exemplo Brasil SELECT osmc.L0cover_upsert('BR');

Deveria se chamar L0_upsert_get(contry). A função de que fato implementa as configurações é osmc.L0_upsert(). Importante notar que, dentro dela, temos um comando unnest(a,b) que não deve ser confundido com o produto cruzado, o PostgreSQL alinha as duas arrays mesmo que de tamanhos diferentes, fazendo uma espécie de left join. Ilustrando com duas arrays reduzidas:

SELECT count(*) n, count(distinct prefix) n_prefix  -- n=5 | n_prefix=3
FROM unnest('{40,41,42}'::int[],'{00,01,02,0a,0b}'::text[]) t(prefix,quadrant);

A função L0_upsert() atualiza a tabela osmc.coverage com linhas is_country e sem popular as colunas cindex, is_overlay ou kx_prefix (utilizadas apenas por coberturas municipais). A consulta abaixo ilustra os casos típicos do Brasil e Colômbia.

CREATE VIEW report.v001_osmc_coverage_l0_list AS
 SELECT isolabel_ext ||' = '||(b'0000000000000000000000'||cb10)::bit(32)::int ||' = '||cb10 as pais,
       is_contained,
       cb10::text||'.'||cb_suffix as cbits, natcod.vbit_to_baseh(cb_suffix,16) as b16,
       status,  round(st_area(geom)/1000000.0) as area_km2
 FROM (
  select *, substring(cbits,1,10) as cb10, substring(cbits,11) as cb_suffix
  from osmc.coverage
 ) t 
 WHERE is_country ORDER BY 1,2,cbits
;

pais	is_contained	cbits	b16	area_km2	status
BR = 76 = 0001001100	f	0001001100.00000110	06	1099512	1
BR = 76 = 0001001100	t	0001001100.00000000	00	275241	1
BR = 76 = 0001001100	t	0001001100.00000001	01	757637	1
BR = 76 = 0001001100	t	0001001100.00000010	02	645014	1
...
BR = 76 = 0001001100	t	0001001100.00001110	0e	54071	1
BR = 76 = 0001001100	t	0001001100.00001111	0f	17440	1
BR = 76 = 0001001100	t	0001001100.00010000	10	1519	1
BR = 76 = 0001001100	t	0001001100.00010001	11	3618	1
CM = 120 = 0001111000	f	0001111000.1001	9	68719	1
CM = 120 = 0001111000	t	0001111000.0001	1	2469	1
CM = 120 = 0001111000	t	0001111000.0010	2	24181	1
...
CM = 120 = 0001111000	t	0001111000.1101	d	47183	1
CM = 120 = 0001111000	t	0001111000.1110	e	5164	1
CO = 170 = 0010101010	t	0010101010.0000	0	8083	1
CO = 170 = 0010101010	t	0010101010.0001	1	141953	1
CO = 170 = 0010101010	t	0010101010.0010	2	57590	1
...
CO = 170 = 0010101010	t	0010101010.1110	e	84471	1
CO = 170 = 0010101010	t	0010101010.1111	f	61264	1

A coluna cbits é composta de duas partes, o código do país e o código da sua célula L0 (traduzido em hexadecimal=base16). A interseção da geometria da célula com o polígono do país tem a sua área indicada pela coluna area_km2. A coluna is_contained é um flag, verdadeiro quando a célula tem interseção com as bordas do país. Ver Issue 02 para detalhes sobre a dedução do caso cheio pela área. São esperadas potências de 2 nas células cheias de is_contained. Para o Brasil é esperada a potência 20 no tamanho de lado do quadrado: ${\sqrt {1099512\ km^{2}}}=1048576\ m=2^{20}$ . Para Camarões (CM) potência 18: ${\sqrt {68719\ km^{2}}}=262143.1\ m\approx 262144\ m=2^{18}$ .

PS: "bug de nome de coluna" a corrigir, a coluna is_contained deveria indicar que a célula está totalmente contida no polígono do país, mas está sendo usado o seu NOT.

Os códigos de país nessa versão ainda eram códigos ISO ocupando 10 bits. Por exemplo Brasil 0001001100 é 76. Na versão corrente são códigos internos (sem compromisso com ISO) de 8 bits, garantindo melhor ocupação nos 57 bits (56 bits para acomodar 14 dígitos hexadecimais) disponíveis de geocódigo hInt64.

@@ Linha 1: / Linha 1: @@
 == Chutando o quadrado inicial de um país ==
-A função f(x) que retorna potências side e next ajuda tanto a avaliar a área de uma célula de cobertura, para saber pelo side (se diff_perc é zero então é interior), como avaliar qual poderia ser um quadro envolvente de cobertura de um país ou município, sempre em km2. Abaixo a área do Brasil.
+ver [[osmc:Metodologia/Algoritmo_SQL/Issues#Issue_02_-_Função_estimadora_de_cobertura_interior_e_quadrado_envolvente]]
-<pre>
+== Reengenharia das chamadas API ==
--- A = 8510417.771 km2:
-with t as (select sqrt(8510417.771 *1000^2) as x)
-select p as side_pow_exp, 2^p as side_pow_val,
-       round( (100.0*(2^p-x)/2^p)::numeric , 2)::real  side_diff_perc,
-       p+1 as next_pow_exp, 2^(p+1) as next_pow_val,
-       round( (100.0*(2^(p+1)-x)/2^(p+1))::numeric , 2)::real  next_diff_perc
-from (select round(log(x)/log(2)) p,x  from t) t2;
--- side_pow_exp | side_pow_val | side_diff_perc | next_pow_exp | next_pow_val | next_diff_perc
--- -------------+--------------+----------------+--------------+--------------+----------------
---           21 |      2097152 |         -39.11 |           22 |      4194304 |          30.45
--- A = 757637 km2:
- side_pow_exp | side_pow_val | side_diff_perc |
---------------+--------------+----------------|
-|      1048576 |          16.99 |
--- A = 1099512 km2:
-|      1048576 |              0 |
-</pre>
-No caso da área do Brasil (de 8510417.771 km2) temos o next como estimativa de quadrado envolvente, com lados de 2^22 km:
-# quadrado com 2^22 metros de lado contém o Brasil.
-# Primeiro dígito base4 é a cobertura de 4 quadrados com 2^21 metros cada
-# Primeiro dígito base16 é a cobertura de 16 quadrados com 2^20 metros de lado cada.
-Nas áreas de células conferimos qual tem ''side_diff_perc=0'', que foi apenas o caso de A = 1099512 km2.
+Ver também [[osmc:Api]]. Com base no site online podemos recuperar as APIs... Ideal seria  ter isso tudo no Swagger, Claiton já fez isso.
+== Revisão das configuração do país ==
+A configuração é efetivada pela chamada com o país específico, por exemplo Brasil <code>SELECT osmc.L0cover_upsert('BR');</code>
+Deveria se chamar <code>L0_upsert_get(contry)</code>. A função de que fato implementa as configurações é [https://github.com/osm-codes/GGeohash/blob/main/src/step04def-ini.sql#L1 osmc.L0_upsert()]. Importante notar que, dentro dela, temos um comando <code>unnest(a,b)</code> que não deve ser confundido com o produto cruzado, o PostgreSQL alinha as duas arrays mesmo que de tamanhos diferentes, fazendo uma espécie de ''left join''. Ilustrando com duas arrays reduzidas:
+<syntaxhighlight lang="sql">
+SELECT count(*) n, count(distinct prefix) n_prefix  -- n=5 | n_prefix=3
+FROM unnest('{40,41,42}'::int[],'{00,01,02,0a,0b}'::text[]) t(prefix,quadrant);
+</syntaxhighlight>
+A função <code>L0_upsert()</code> atualiza a tabela  ''osmc.coverage'' com linhas ''is_country'' e sem popular as colunas ''cindex'', ''is_overlay'' ou ''kx_prefix'' (utilizadas apenas por coberturas municipais). A consulta abaixo ilustra os casos típicos do Brasil e Colômbia.
+<syntaxhighlight lang="sql">
+CREATE VIEW report.v001_osmc_coverage_l0_list AS
+ SELECT isolabel_ext ||' = '||(b'0000000000000000000000'||cb10)::bit(32)::int ||' = '||cb10 as pais,
+       is_contained,
+       cb10::text||'.'||cb_suffix as cbits, natcod.vbit_to_baseh(cb_suffix,16) as b16,
+       status,  round(st_area(geom)/1000000.0) as area_km2
+ FROM (
+  select *, substring(cbits,1,10) as cb10, substring(cbits,11) as cb_suffix
+  from osmc.coverage
+ ) t
+ WHERE is_country ORDER BY 1,2,cbits
+;
+</syntaxhighlight>
+{| class="wikitable"
+!         pais          !! is_contained !!        cbits        !! b16 !! area_km2 !! status
+|-
+| BR = 76 = 0001001100  || f            || 0001001100.00000110 || 06   ||  1099512 ||      1
+|-
+| BR = 76 = 0001001100  || t            || 0001001100.00000000 || 00   ||   275241 ||      1
+|-
+| BR = 76 = 0001001100  || t            || 0001001100.00000001 || 01   ||   757637 ||      1
+|-
+| BR = 76 = 0001001100  || t            || 0001001100.00000010 || 02   ||   645014 ||      1
+|-
+|...
+|-
+| BR = 76 = 0001001100  || t            || 0001001100.00001110 || 0e   ||    54071 ||      1
+|-
+| BR = 76 = 0001001100  || t            || 0001001100.00001111 || 0f   ||    17440 ||      1
+|-
+| BR = 76 = 0001001100  || t            || 0001001100.00010000 || 10   ||     1519 ||      1
+|-
+| BR = 76 = 0001001100  || t            || 0001001100.00010001 || 11   ||     3618 ||      1
+|-
+| CM = 120 = 0001111000 || f            || 0001111000.1001     || 9    ||    68719 ||      1
+|-
+| CM = 120 = 0001111000 || t            || 0001111000.0001     || 1    ||     2469 ||      1
+|-
+| CM = 120 = 0001111000 || t            || 0001111000.0010     || 2    ||    24181 ||      1
+|-
+|...
+|-
+| CM = 120 = 0001111000 || t            || 0001111000.1101     || d    ||    47183 ||      1
+|-
+| CM = 120 = 0001111000 || t            || 0001111000.1110     || e    ||     5164 ||      1
+|-
+| CO = 170 = 0010101010 || t            || 0010101010.0000     || 0    ||     8083 ||      1
+|-
+| CO = 170 = 0010101010 || t            || 0010101010.0001     || 1    ||   141953 ||      1
+|-
+| CO = 170 = 0010101010 || t            || 0010101010.0010     || 2    ||    57590 ||      1
+|-
+|...
+|-
+| CO = 170 = 0010101010 || t            || 0010101010.1110     || e    ||    84471 ||      1
+|-
+| CO = 170 = 0010101010 || t            || 0010101010.1111     || f    ||    61264 ||      1
+|}
+A coluna ''cbits'' é composta de duas partes, o código do país e o código da sua célula ''L0'' (traduzido em hexadecimal=base16). A interseção da geometria da célula com o polígono do país tem a sua área indicada pela coluna ''area_km2''. A coluna ''is_contained'' é um flag, verdadeiro quando a célula tem interseção com as bordas do país. Ver  [[osmc:Metodologia/Algoritmo_SQL/Issues#Issue_02_-_Função_estimadora_de_cobertura_interior_e_quadrado_envolvente|Issue 02 para detalhes sobre a dedução do caso cheio pela área]]. São esperadas [[osmc:Metodologia#Projeção_e_cobertura_nacionais|potências de 2]] nas células cheias de ''is_contained''. Para o Brasil é esperada a potência 20 no tamanho de lado do quadrado: <math>\sqrt{1099512\ km^2} = 1048576 \ m = 2^{20}</math>. Para Camarões (CM) potência 18: <math>\sqrt{68719\ km^2} = 262143.1 \ m \approx 262144 \ m = 2^{18}</math>.
+:PS: '''"bug de nome de coluna"''' a corrigir, a coluna ''is_contained'' deveria indicar que a célula está totalmente contida no polígono do país, mas está sendo usado o seu NOT.
+Os códigos de país nessa versão ainda eram códigos ISO ocupando 10 bits. Por exemplo Brasil <code>0001001100</code> é 76. Na versão corrente são códigos internos (sem compromisso com ISO) de 8 bits, garantindo melhor ocupação nos 57 bits (56 bits para acomodar 14 dígitos hexadecimais) disponíveis de geocódigo [[Código_Natural/Representação_interna#hInt|hInt64]].