osmc:Metodologia/Algoritmo SQL: mudanças entre as edições
Linha 117: Linha 117:
O valor ''cbits_l0'' é o identificador de célula ''L0'' do país correto, e o identificador de país é seu prefixo. A partir do ponto ''pt'' (com geometria já no SRID do país) e ''cbits_l0'' obtemos por algoritmo  GGeohash o valor ''cbits_pt'' da célula pontual, <code>cbits_pt=encode(pt,cbits_l0)</code>.  
O valor ''cbits_l0'' é o identificador de célula ''L0'' do país correto, e o identificador de país é seu prefixo. A partir do ponto ''pt'' (com geometria já no SRID do país) e ''cbits_l0'' obtemos por algoritmo  GGeohash o valor ''cbits_pt'' da célula pontual, <code>cbits_pt=encode(pt,cbits_l0)</code>.  


'''NOTA'''. Em aplicações que não partem de um cenário global, ou de pontos que não precisam ser selecionados como interiores ou não ao território nacional, pode-se adotar um seletor mais simples:
'''NOTA'''. Em aplicações que não partem de um cenário global, ou de pontos que não precisam ser selecionados como interiores ou não ao território nacional, pode-se adotar um seletor mais simples: <code>grid_br.xyS_collapseTo_ijS(pt)</code> por default retorna as coordenadas IJ de L0.
 
: <code>SELECT cbits as cbits_l0 FROM osmc.coverage <br/>WHERE srid=$srid AND pt && geom</code>
: <code>SELECT cbits as cbits_l0 FROM osmc.coverage <br/>WHERE srid=$srid AND pt && geom</code>
: indexação prévia com <br/><code>CREATE INDEX osmc_coverage_idx1<br/>ON osmc.coverage(srid,geom) <br/>USING GIST (geom)</code>
: indexação prévia com <br/><code>CREATE INDEX osmc_coverage_idx1<br/>ON osmc.coverage(srid,geom) <br/>USING GIST (geom)</code>
Por fim, como o país não pode ter mais do que 16 células L0, uma cadeia de condições IF ou CASE é suficiente e provavelmente mais rápido do que consultar o banco de dados. Usando <code>ST_X(pt)</code> e <code>ST_Y(pt)</code> e os valores de BBOX, podemos otimizar o processo de escolha de ''L0'' e ''encode'' do ponto.
Por fim, como o país não pode ter mais do que 16 células L0, uma cadeia de condições IF ou CASE é suficiente e provavelmente mais rápido do que consultar o banco de dados. Usando <code>ST_X(pt)</code> e <code>ST_Y(pt)</code> e os valores de BBOX, podemos otimizar o processo de escolha de ''L0'' e ''encode'' do ponto.
   
 
=== Tratamento das configurações ===
Usando o exemplo do Brasil, "grid_lc" indica que a cobertura é uma matriz de 5x5 (linhas por colunas), e "grid_l0_cell" quais são as células IJ selecionadas pelo índice "grid_l0_cell_idx":
<pre>
  grid_lc: 5 5
  grid_l0_cell:    40 41 42 43 30 31 32 33 34 21 22 23 11 12 13 02 44 24
  grid_l0_cell_idx: 0 1  2  3  4  5  6  7  8  9  a  b  c  d  e  .  .  .
  grid_l0_ghost: {02:fT fY, 44:fP, 24:fN}
</pre>
As configurações podem ser reescritas com auxilio de uma query:
<syntaxhighlight lang="sql" style="font-size: 80%;">
WITH m AS ( -- background matrix                                         
select i,j from generate_series(0,4) t1(i), generate_series(0,4) t2(j)
), c AS (  -- cover cells
-- ... (natcod.baseh_to_vbit('{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,.,.,.}'::text[],16))
select i,j, (natcod.baseh_to_vbit('{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,fT,fP,fN}'::text[],16))[(p+2)/3] as cbits
from (
  select i,j, strpos('40 41 42 43 30 31 32 33 34 21 22 23 11 12 13 02 44 24', i::text||j::text)::float as p
  from m
) tt where p>0
) SELECT array_agg(a order by i) -- IJ_to_cover matrix
  FROM (
    SELECT m.i, array_agg( c.cbits order by m.j) a
    FROM c RIGHT JOIN m ON c.i=m.i AND c.j=m.j
    GROUP BY 1 ORDER BY 1
  ) t4
;
</syntaxhighlight>
 
A matriz resultante <code>IJ_to_cover={{NULL,NULL,1111101,NULL,NULL}, ..., {0000,0001,0010,0011,1111011}}</code> é a indexadora de ''L0'', a ser utilizada nas funções de encode. Conforme o tipo de resolução é possível ignorar as fantasmas (tamanho maior que 4 bits) ou utilizá-las como as demais, com o cuidado de não subdividir antes do nível ''L1.5''.
 
:<small>'''NOTA TÉCNICA'''. A rigor, por terem duas células de cobertura associadas ao quadrante ''IJ=02'', a resolução seria em duas etapas: usando "{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,.,.,.}" na cobertura, o resultado seria <code>IJ_to_cover={{NULL,NULL,"",NULL,NULL}, ...,{0000,0001,0010,0011,""}}</code>, com NULLs e bit strings vazias, "". Estabelecendo com "" a condição para resolução secundária, mas isso traria maior complexidade, mais fácil por hora ignorar a célula "fY" do mar territorial. </small> <!-- Os NULLs indicam que a célula da matriz não é usada. Os valores "" indicam que a resolução será por ''grid_l0_ghost'': é a condição para a verificação secundária, esses quadrantes no nível ''L0'' não possuem uma resolução direta, apenas no nível ''L1.5''. -->
 
Temos por fim a seguinte '''função de resolução ''L0''''':
 
<syntaxhighlight lang="sql" style="font-size: 80%;">
DROP FUNCTION grid_br.IJ0_to_L0(int,int,boolean)
;
DROP FUNCTION grid_br.IJ0_to_L0(int[],boolean)
;
 
CREATE FUNCTION grid_br.IJ0_to_L0(i int, j int, ignore_ghost boolean default true) RETURNS varbit AS $f$
    SELECT CASE WHEN ignore_ghost AND bit_length(cbits)>4 THEN NULL ELSE cbits END
    FROM (
    SELECT ('{{NULL,NULL,1111101,NULL,NULL},{NULL,1100,1101,1110,NULL},{NULL,1001,1010,1011,1111010},{0100,0101,0110,0111,1000},{0000,0001,0010,0011,1111011}}'::varbit[])[i+1][j+1]
    ) t(cbits)
$f$ LANGUAGE SQL IMMUTABLE;
 
CREATE FUNCTION grid_br.IJ0_to_L0(ij int[], ignore_ghost boolean default true) RETURNS varbit AS $wrap$
    SELECT grid_br.IJ0_to_L0($1[1], $1[2], $2)
$wrap$ LANGUAGE SQL IMMUTABLE;
</syntaxhighlight>
 
Portanto <code>grid_br.IJ0_to_L0( grid_br.xyS_collapseTo_ijS(x,y) )</code> determina a cobertura L0. Com ''cbits'' determinamos quais valores Xmax e Ymax subtrair cálculo GGeohash de uma célula de nível superior a zero.
<!--
<!--
Situações:
Situações:
Disponível em “https://wiki.addressforall.org/doc/osmc:Metodologia/Algoritmo_SQL”