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

De Documentação
(→‎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
Grid br-L0-xyref-points.png

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;