Ir para o conteúdo

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

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

Edição das 13h53min de 22 de junho de 2024

1 923 bytes adicionados ,  22 junho
reestruturando para isolar testes e demonstrações
m (core functions, revisar para contemplar)
(reestruturando para isolar testes e demonstrações)
Linha 1: Linha 1:
== Lib de Grades ==
== Lib de Grades ==


Biblioteca para simplificar a geração de grades. Exemplificando implementação dentro do schema <code>grid_br</code> das grades do Brasil. Convensionou-se em GGeohash usar YX no lugar de XY.
Biblioteca para simplificar a geração de grades. Exemplificando implementação dentro do ''schema'' <code>grid_br</code> das grades do Brasil. Convencionou-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. Algumas funções de flexibilização precisam ser agrupadas como "helper functions", enquanto as demais como "core functions". Por exemplo as convenções de array de inteiros baseadas em nível (xyLevel e IJL) são ''core'', enquanto as baseadas em size-side são ''helper''.  
A seguir avaliando o uso ''default'' de XYref no lugar de XYcenter, para o desenho da célula. Algumas funções de flexibilização precisam ser agrupadas como "helper functions", enquanto as demais como "core functions". Por exemplo as convenções de array de inteiros baseadas em nível (xyL e ijL) são ''core'', enquanto as baseadas em size-side (xyS e ijS) são ''helper''.
 
===  Core ===
'''Quantizadores''': transformam as coordenadas contínuas YX, de posição no plano projetado,  em coordenadas discretas IJ, de localização na grade hierárquica.  Quem amarra a posição hierárquica &mdash; ''grid hierarchical level'' do sistema de grades &mdash; com localização YX é o tamanho de lado ''S'' (''side size'') da célula de nível ''L''. Por imposição do [[Discrete National Grid Systems/pt|padrão DNGS]] temos <math>S_{L}=2^{Lmax-L}</math>. No caso do Brasil ''Lmax''=20, no caso de Camarões ''Lmax=18''.
 
:Nota. A fórmula de ''S'' funciona também para níveis-meio, por exemplo ''L''=1.5. Isso se deve à  construção geométrica dos níveis-meio, cujas células (necessariamente de áreas iguais) são a união de 2 células do próximo nível inteiro: <br/> <math>S_{Lhalf}=\sqrt{2\cdot{Area_{\lceil Lhalf\rceil}}} = \sqrt{2} \cdot \sqrt{{{S_{\lceil Lhalf\rceil}}^2}}  =  \sqrt{2} \cdot 2^{Lmax-\lceil Lhalf\rceil}</math> onde <math>Lhalf = \forall L | L = \lceil L \rceil - 0.5</math>.
 
A decisão de usar a sequência YX e não XY precisa talvez ser revista. Na cultura escolar brasileira XY é horizontal-vertical. Na cultura das imagens de satélite e geoprocessamento XY é vertical-horizontal. Adotamos a "cultura PostGIS", das funções ''standard spatial type'' (aquelas com prefixo "ST_").


<syntaxhighlight lang="sql" style="font-size: 80%;">
<syntaxhighlight lang="sql" style="font-size: 80%;">
-- Core functions:
drop FUNCTION if exists grid_br.xyS_collapseTo_ijS(int,int,int,boolean);
drop FUNCTION if exists grid_br.xyS_collapseTo_ijS(int,int,int,boolean);
drop FUNCTION if exists grid_br.xyS_collapseTo_ijS(int[],boolean);
drop FUNCTION if exists grid_br.xyL_collapseTo_ijL(int,int,int,boolean);
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, is_half boolean default false) RETURNS int[] AS $f$
CREATE FUNCTION grid_br.xyS_collapseTo_ijS(y int, x int, s int default 1048576, is_half boolean default false) RETURNS int[] AS $f$
   SELECT array[ (y-6727000)/CASE WHEN is_half AND s>=2 THEN s/2 ELSE s END, (x-2715000)/s, s ]
   SELECT array[ (y-6727000)/CASE WHEN is_half AND s>=2 THEN s/2 ELSE s END, (x-2715000)/s, s ]
$f$ LANGUAGE SQL IMMUTABLE;
$f$ language SQL IMMUTABLE;
 
CREATE FUNCTION grid_br.xyS_collapseTo_ijS( yxs int[], is_half boolean default false ) RETURNS int[] AS $wrap$
  SELECT grid_br.xyS_collapseTo_ijS(yxs[1], yxs[2], yxs[3], is_half)
$wrap$ LANGUAGE SQL IMMUTABLE
;


CREATE FUNCTION grid_br.xylevel_collapseTo_ijS(y int, x int, intlevel int default 0) RETURNS int[] AS $wrap$
CREATE FUNCTION grid_br.xyL_collapseTo_ijL(y int, x int, intlevel int default 0) RETURNS int[] AS $f$
   SELECT grid_br.xyS_collapseTo_ijS($1, $2, (2^(20-intlevel/10.0))::int, (intlevel%2)=1)
   SELECT array[ $ijS[1], $ijS[2], intlevel ]
$wrap$ LANGUAGE SQL IMMUTABLE
  FROM ( SELECT grid_br.xyS_collapseTo_ijS($1, $2, (2^(20-intlevel/10.0))::int, (intlevel%2)=1) ) t(ijS)
$f$ 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,boolean);
drop FUNCTION if exists grid_br.ijS_to_xySref(int[],boolean);
CREATE or replace FUNCTION grid_br.ijS_to_xySref(i int, j int, s int, is_half boolean default false) RETURNS int[] AS $f$
    SELECT array[ 6727000 + i*CASE WHEN is_half AND s>=2 THEN s/2 ELSE s END, 2715000 + j*s, s ]
$f$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION grid_br.ijS_to_xySref(yxl int[], is_half boolean default false) RETURNS int[] AS $wrap$
  SELECT grid_br.ijS_to_xySref(yxl[1], yxl[2], yxl[3],is_half)
$wrap$ LANGUAGE SQL IMMUTABLE;
</syntaxhighlight>
</syntaxhighlight>


Testes para validação, usando funções ou tabelas já homologadas:
'''Construtores de geometria''':
<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>
Grade da Colômbia, 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>
Portanto <code>grid_br.ijS_to_xySref(grid_br.xylevel_collapseTo_ijS(ponto,nivel))</code> fornece automaticamente a célula ''L0'' de referência onde será aplicado algoritmo GGeohash.
=== Construtor de geometria ===
Como vimos a referência é o ponto inferior esquerdo do contorno da célula, basta criar os demais 3 pontos de um quadrado. Na função abaixo a menor célula tem 1 metro pois necessitaria fração para menos que 1 m.
Como vimos a referência é o ponto inferior esquerdo do contorno da célula, basta criar os demais 3 pontos de um quadrado. Na função abaixo a menor célula tem 1 metro pois necessitaria fração para menos que 1 m.
<syntaxhighlight lang="sql" style="font-size: 80%;">
<syntaxhighlight lang="sql" style="font-size: 80%;">
Linha 215: Linha 155:
No caso especial de fronteira nacional, como as células "0" ou "3", onde não existem "células contíguas", o dono é o país estrangeiro, portanto, a rigor, a linha não existe na grade.
No caso especial de fronteira nacional, como as células "0" ou "3", onde não existem "células contíguas", o dono é o país estrangeiro, portanto, a rigor, a linha não existe na grade.


==teste==
== Helper lib ==
Fonte 80%
Funções complementares às "core functions", a maioria implementações "wrap" para simplesmente compatibilizar tipos e estruturas. Ainda assim a ortogonalidade é requerida apenas nos tipos principais (ex. baseados no ''intLevel''), não precisam garantir a conversão direta de tipos secundários (ex. ''cell side size'').
 
<syntaxhighlight lang="sql" style="font-size: 80%;">
<syntaxhighlight lang="sql" style="font-size: 80%;">
select lkjsdklsj+2;
drop FUNCTION if exists grid_br.xyS_collapseTo_ijS(int[],boolean);
drop FUNCTION if exists grid_br.xylevel_collapseTo_ijS(int,int,int);
 
CREATE FUNCTION grid_br.xyL_collapseTo_ijS(y int, x int, intlevel int default 0) RETURNS int[] AS $wrap$
  SELECT grid_br.xyS_collapseTo_ijS($1, $2, (2^(20-intlevel/10.0))::int, (intlevel%2)=1)
$wrap$ LANGUAGE SQL IMMUTABLE
;
CREATE FUNCTION grid_br.xyL_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,boolean);
drop FUNCTION if exists grid_br.ijS_to_xySref(int[],boolean);
 
CREATE or replace FUNCTION grid_br.ijS_to_xySref(i int, j int, s int, is_half boolean default false) RETURNS int[] AS $f$
    SELECT array[ 6727000 + i*CASE WHEN is_half AND s>=2 THEN s/2 ELSE s END, 2715000 + j*s, s ]
$f$ LANGUAGE SQL IMMUTABLE;
 
CREATE FUNCTION grid_br.ijS_to_xySref(yxl int[], is_half boolean default false) RETURNS int[] AS $wrap$
  SELECT grid_br.ijS_to_xySref(yxl[1], yxl[2], yxl[3],is_half)
$wrap$ LANGUAGE SQL IMMUTABLE;
</syntaxhighlight>
</syntaxhighlight>
Fonte normal:
 
<syntaxhighlight lang="sql">
------
select lkjsdklsj+2;
== Testes da lib ==
Primeiro demonstrações (de que as funções funcionam), testes para validar hipóteses e requisitos básicos; depois [https://pt.stackoverflow.com/a/13530/4186 testes de regressão].
=== Validação primária ===
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>
Grade da Colômbia, 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>
</syntaxhighlight>
Portanto <code>grid_br.ijS_to_xySref(grid_br.xylevel_collapseTo_ijS(ponto,nivel))</code> fornece automaticamente a célula ''L0'' de referência onde será aplicado algoritmo GGeohash.
Peter
2 384

edições

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