osmc talk:Metodologia/Algoritmo SQL: mudanças entre as edições
mSem resumo de edição |
(add comentarios sobre bug que confundem quem for ler o guia) |
||
Linha 6: | Linha 6: | ||
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. | 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]]. |
Edição atual tal como às 00h30min de 15 de junho de 2024
Chutando o quadrado inicial de um país
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: . Para Camarões (CM) potência 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.