Documentação

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

osmc Discussão

osmc:Metodologia/Algoritmo SQL (ver código-fonte)

Edição das 11h13min de 15 de junho de 2024

590 bytes adicionados ,  15 junho
m
→‎Configuração do país: reestruturando, deixando aviso do bug para os comentários
m (→‎Configuração do país: reestruturando, deixando aviso do bug para os comentários)
Linha 17: Linha 17:
A configuração é efetivada pela chamada com o país específico, por exemplo Brasil <code>SELECT osmc.L0cover_upsert('BR');</code>
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:
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 tabela abaixo, gerada pelo relatório  ''v001_osmc_coverage_l0_list'' apresentado em seguida, ilustra os casos típicos do Brasil, Camarões e Colômbia:
 
<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"
{| class="wikitable"
!        pais          !! is_contained !!        cbits        !! b16 !! area_km2 !! status
!        pais          !! is_contained !!        cbits        !! b16 !! area_km2  
|-
|-
| BR = 76 = 0001001100  || f            || 0001001100.00000110 || 06  ||  1099512 ||      1
| BR = 76 = 0001001100  || f            || 0001001100.00000110 || 06  ||  1099512  
|-
|-
| BR = 76 = 0001001100  || t            || 0001001100.00000000 || 00  ||  275241 ||      1
| BR = 76 = 0001001100  || t            || 0001001100.00000000 || 00  ||  275241  
|-
|-
| BR = 76 = 0001001100  || t            || 0001001100.00000001 || 01  ||  757637 ||      1
| BR = 76 = 0001001100  || t            || 0001001100.00000001 || 01  ||  757637  
|-
|-
| BR = 76 = 0001001100  || t            || 0001001100.00000010 || 02  ||  645014 ||      1
| BR = 76 = 0001001100  || t            || 0001001100.00000010 || 02  ||  645014  
|-
|-
|...
|...
|-
|-
| BR = 76 = 0001001100  || t            || 0001001100.00001110 || 0e  ||    54071 ||      1
| BR = 76 = 0001001100  || t            || 0001001100.00001110 || 0e  ||    54071  
|-
|-
| BR = 76 = 0001001100  || t            || 0001001100.00001111 || 0f  ||    17440 ||      1
| BR = 76 = 0001001100  || t            || 0001001100.00001111 || 0f  ||    17440  
|-
|-
| BR = 76 = 0001001100  || t            || 0001001100.00010000 || 10  ||    1519 ||      1
| BR = 76 = 0001001100  || t            || 0001001100.00010000 || 10  ||    1519  
|-
|-
| BR = 76 = 0001001100  || t            || 0001001100.00010001 || 11  ||    3618 ||      1
| BR = 76 = 0001001100  || t            || 0001001100.00010001 || 11  ||    3618  
|-
|-
| CM = 120 = 0001111000 || f            || 0001111000.1001    || 9    ||    68719 ||      1
| CM = 120 = 0001111000 || f            || 0001111000.1001    || 9    ||    68719  
|-
|-
| CM = 120 = 0001111000 || t            || 0001111000.0001    || 1    ||    2469 ||      1
| CM = 120 = 0001111000 || t            || 0001111000.0001    || 1    ||    2469
|-
|-
| CM = 120 = 0001111000 || t            || 0001111000.0010    || 2    ||    24181 ||      1
| CM = 120 = 0001111000 || t            || 0001111000.0010    || 2    ||    24181  
|-
|-
|...
|...
|-
|-
| CM = 120 = 0001111000 || t            || 0001111000.1101    || d    ||    47183 ||      1
| CM = 120 = 0001111000 || t            || 0001111000.1101    || d    ||    47183  
|-
|-
| CM = 120 = 0001111000 || t            || 0001111000.1110    || e    ||    5164 ||      1
| CM = 120 = 0001111000 || t            || 0001111000.1110    || e    ||    5164
|-
|-
| CO = 170 = 0010101010 || t            || 0010101010.0000    || 0    ||    8083 ||      1
| CO = 170 = 0010101010 || t            || 0010101010.0000    || 0    ||    8083  
|-
|-
| CO = 170 = 0010101010 || t            || 0010101010.0001    || 1    ||  141953 ||      1
| CO = 170 = 0010101010 || t            || 0010101010.0001    || 1    ||  141953
|-
|-
| CO = 170 = 0010101010 || t            || 0010101010.0010    || 2    ||    57590 ||      1
| CO = 170 = 0010101010 || t            || 0010101010.0010    || 2    ||    57590
|-
|-
|...
|...
|-
|-
| CO = 170 = 0010101010 || t            || 0010101010.1110    || e    ||    84471 ||      1
| CO = 170 = 0010101010 || t            || 0010101010.1110    || e    ||    84471
|-
|-
| CO = 170 = 0010101010 || t            || 0010101010.1111    || f    ||    61264 ||      1
| CO = 170 = 0010101010 || t            || 0010101010.1111    || f    ||    61264  


|}
|}


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>.
A coluna ''cbits'' é composta de duas partes, o código do país e o código da sua célula ''L0''. O código é traduzido em hexadecimal (base16) na coluna ''b16'', e suas células formam a cobertura do país, ou seja, a grade de nível zero. Nos  [[osmc:Convenções/Grade científica multifinalitária|geocódigos de notação científica]] aparecerão sempre como primeiro dígito.  
 
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]].  
 
<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,
      round(st_area(geom)/1000.0^2) 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>
 
=== Seletor de jurisdição e L0 ===
Em aplicações que partem de um cenário global, ou de pontos que precisam ser selecionados como interiores ou não ao território nacional, é importante detectar  não só a célula ''L0'' mas também o polígono territorial. Situações:
 
* mais geral, sem nação definida: ponto sem projeção, dado em latitude-longitude. O [[osmc:Metodologia#Seletor_de_jurisdição|seletor de jurisdição baseado em BBOX WGS84]] que determina a projeção a usar.
* menos exigente, com ponto já na projeção nacional porém sem exigência de saber se fora ou dentro do território: aplicativos dedicados não esperam receber pontos exteriores, portanto não carece conferir se, mesmo em células ''L0'' de borda, é ou não interior. O seletor de célula se reduz ao BBOX da projeção, dispensa uso do polígono.
* mais exigente: em aplicações onde certeza de ser ponto interior é necessária. Aí entra o uso do flag, pois se for selecionada uma célula ''L0'' com flag de borda, o seu contorno poligonal também precisará ser verificado. Tipicamente 3 passos na comparação do ponto ''pt'' com a célula ''cell'': <code>pt && cell AND (not_border OR ST_Contains(pt,cell))</code>. A otimização desse tipo de query requer de indexador específico.
 


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


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]].
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]].
Peter
2 384

edições

Disponível em “https://wiki.addressforall.org/doc/Especial:MobileDiff/4008”