osmc:Metodologia/Algoritmo SQL/Lib: mudanças entre as edições
(→Lib de Grades: add grid_br.xylevel_collapseTo_ijS e sua inversa) |
|||
| Linha 40: | Linha 40: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== teste == | Testes para validação, usando funções ou tabelas já homologadas: | ||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
select code_b16h, ijs[1] i, ijs[2] j, count(*) n from ( | |||
select a.code_b16h, grid_br.xylevel_collapseTo_ijS(st_y(pt.geom)::int,st_x(pt.geom)::int,hlevel::int) as ijs | |||
from grid_br.all_levels a, lateral ST_DumpPoints( ST_GeneratePoints(a.geom, 200) ) as pt | |||
where hlevel=0 | |||
) t group by 1,2,3 order by 1; | |||
</syntaxhighlight> | |||
Comparando com a definição de grade do Brasil, de zero a "e": | |||
<pre> | |||
grid_l0_cell_ij: 40 41 42 43 30 31 32 33 34 21 22 23 11 12 13 | |||
grid_l0_cell_b16: 0 1 2 3 4 5 6 7 8 9 a b c d e | |||
code_b16h | i | j | n | |||
-----------+---+---+----- | |||
0 | 4 | 0 | 200 | |||
1 | 4 | 1 | 200 | |||
2 | 4 | 2 | 200 | |||
3 | 4 | 3 | 200 | |||
4 | 3 | 0 | 200 | |||
5 | 3 | 1 | 200 | |||
6 | 3 | 2 | 200 | |||
7 | 3 | 3 | 200 | |||
8 | 3 | 4 | 200 | |||
9 | 2 | 1 | 200 | |||
a | 2 | 2 | 200 | |||
b | 2 | 3 | 200 | |||
c | 1 | 1 | 200 | |||
d | 1 | 2 | 200 | |||
e | 1 | 3 | 200 | |||
</pre> | |||
[[Arquivo:Grid br-L0-xyref-points.png|miniaturadaimagem|420x420px]] | |||
Conferindo a localização do ponto xyRef: | |||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
drop view test_l0_refpoint; | |||
create view test_l0_refpoint as | |||
select *, ST_SetSRID(ST_POINT(yxs[2], yxs[1]), 952019) as geom | |||
from ( | |||
select code_b16h, ijs[1] i, ijs[2] j, grid_br.ijS_to_xySref(ijs) as yxs, count(*) n | |||
from ( | |||
select a.code_b16h, grid_br.xylevel_collapseTo_ijS(st_y(pt.geom)::int,st_x(pt.geom)::int,hlevel::int) as ijs | |||
from grid_br.all_levels a, lateral ST_DumpPoints( ST_GeneratePoints(a.geom, 20) ) as pt | |||
where hlevel=0 and code_b16h !~ '^f' | |||
) t1 group by 1,2,3,4 order by 1 | |||
) t2; | |||
</syntaxhighlight> | |||
Depois entender os pontos de borda: | |||
<pre> | |||
select a.gid_vbit, a.code_b16h, grid_br.xylevel_collapseTo_ijS(st_y(pt.geom)::int,st_x(pt.geom)::int,hlevel::int) as ijs | |||
from grid_br.all_levels a, lateral ST_DumpPoints( a.geom ) as pt | |||
where hlevel=0 or hlevel=1 order by hlevel, 1; | |||
</pre> | |||
==teste== | |||
Fonte 80% | Fonte 80% | ||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | <syntaxhighlight lang="sql" style="font-size: 80%;"> | ||