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%;"> |
Edição das 04h14min de 17 de junho de 2024
Lib de Grades
Biblioteca para simplificar a geração de grades. Exemplificando implementação dentro do schema grid_br
das grades do Brasil. Convensionou-se em GGeohash usar YX no lugar de XY.
A seguir avaliando o uso default de XYref no lugar de XYcenter, para o desenho da célula.
drop FUNCTION if exists grid_br.xyS_collapseTo_ijS(int,int,int);
drop FUNCTION if exists grid_br.xyS_collapseTo_ijS(int[]);
drop FUNCTION if exists grid_br.xylevel_collapseTo_ijS(int,int,int);
drop FUNCTION if exists grid_br.xylevel_collapseTo_ijS(int[]);
CREATE FUNCTION grid_br.xyS_collapseTo_ijS(y int, x int, s int default 1048576) RETURNS int[] AS $f$
SELECT array[ (y-6727000)/s, (x-2715000)/s, s ]
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION grid_br.xyS_collapseTo_ijS(yxs int[]) RETURNS int[] AS $wrap$
SELECT grid_br.xyS_collapseTo_ijS(yxs[1], yxs[2], yxs[3])
$wrap$ LANGUAGE SQL IMMUTABLE
;
CREATE FUNCTION grid_br.xylevel_collapseTo_ijS(y int, x int, level int default 0) RETURNS int[] AS $wrap$
SELECT grid_br.xyS_collapseTo_ijS($1, $2, (2^(20-level))::int)
$wrap$ LANGUAGE SQL IMMUTABLE
;
CREATE FUNCTION grid_br.xylevel_collapseTo_ijS(yxl int[]) RETURNS int[] AS $wrap$
SELECT grid_br.xyS_collapseTo_ijS(yxl[1], yxl[2], yxl[3])
$wrap$ LANGUAGE SQL IMMUTABLE
;
-----
drop FUNCTION if exists grid_br.ijS_to_xySref(int,int,int);
drop FUNCTION if exists grid_br.ijS_to_xySref(int[]);
CREATE or replace FUNCTION grid_br.ijS_to_xySref(i int, j int, s int) RETURNS int[] AS $f$
SELECT array[ 6727000 + i*s, 2715000 + j*s, s ]
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION grid_br.ijS_to_xySref(yxl int[]) RETURNS int[] AS $wrap$
SELECT grid_br.ijS_to_xySref(yxl[1], yxl[2], yxl[3])
$wrap$ LANGUAGE SQL IMMUTABLE;
Testes para validação, usando funções ou tabelas já homologadas:
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;
Comparando com a definição de grade do Brasil, de zero a "e":
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
Conferindo a localização do ponto xyRef:
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;
Depois entender os pontos de borda:
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;
teste
Fonte 80%
select lkjsdklsj+2;
Fonte normal:
select lkjsdklsj+2;