osmc:Metodologia/Algoritmo SQL

De Documentação
< osmc:Metodologia
Revisão de 18h12min de 2 de junho de 2024 por Peter (discussão | contribs)

Detalhamento metodológico, descrevendo os algoritmos implementados em linguagem SQL. Para obtenção do código-fonte:

  1. Versão desejada se homologada: git.
  2. Versão corrente do servidor: se em dúvida, usar pg_dump --schema-only -f dl05_dump_test.sql dl05.

Configuração do país

Depois de percorrido todo o roteiro de decisões soberanas, o país está apto a implementar seu geocódigo oficial. As decisões são resumidas num arquivo YAML. Exemplos:

Os parâmetros principais do YAML, ao invés de serem lidos diretamente do arquivo (pendente implementar essa estratégia), são copiadas/coladas na unção de inclusão desses dados na sua tabela. Exemplos:

A configuração é efetivada pela chamada com o país específico, por exemplo Brasil SELECT osmc.L0cover_upsert('BR');

Deveria se chamar L0_upsert_get(contry). A função de que fato implementa as configurações é osmc.L0_upsert(). Importante notar que, dentro dela, temos um comando unnest(a,b) que não deve ser confundido com o produto cruzado, o PostgreSQL alinha as duas arrays mesmo que de tamanhos diferentes, fazendo uma espécie de left join. Ilustrando com duas arrays reduzidas: 

SELECT count(*) n, count(distinct prefix) n_prefix  -- n=5 | n_prefix=3
FROM unnest('{40,41,42}'::int[],'{00,01,02,0a,0b}'::text[]) t(prefix,quadrant);

A função L0_upsert() atualiza a tabela osmc.coverage com linhas is_country e sem popular as colunas cindex, is_overlay ou kx_prefix (utilizadas apenas por coberturas municipais). A consulta abaixo ilustra os casos típicos do Brasil e Colômbia. 

CREATE VIEW report.v001_osmc_coverage_l0_list AS
 SELECT isolabel_ext ||' = '||(b'0000000000000000000000'||cb10)::bit(32)::int ||' = '||cb10 as pais,
       is_contained,
       cb10::text||'.'||cb_suffix as cbits, natcod.vbit_to_baseh(cb_suffix,16) as b16,
       status,  round(st_area(geom)/1000000.0) as area_km2
 FROM (
  select *, substring(cbits,1,10) as cb10, substring(cbits,11) as cb_suffix
  from osmc.coverage
 ) t 
 WHERE is_country ORDER BY 1,2,cbits
;
pais is_contained cbits b16 area_km2 status
BR = 76 = 0001001100 f 0001001100.00000110 06 1099512 1
BR = 76 = 0001001100 t 0001001100.00000000 00 275241 1
BR = 76 = 0001001100 t 0001001100.00000001 01 757637 1
BR = 76 = 0001001100 t 0001001100.00000010 02 645014 1
...
BR = 76 = 0001001100 t 0001001100.00001110 0e 54071 1
BR = 76 = 0001001100 t 0001001100.00001111 0f 17440 1
BR = 76 = 0001001100 t 0001001100.00010000 10 1519 1
BR = 76 = 0001001100 t 0001001100.00010001 11 3618 1
CM = 120 = 0001111000 f 0001111000.1001 9 68719 1
CM = 120 = 0001111000 t 0001111000.0001 1 2469 1
CM = 120 = 0001111000 t 0001111000.0010 2 24181 1
...
CM = 120 = 0001111000 t 0001111000.1101 d 47183 1
CM = 120 = 0001111000 t 0001111000.1110 e 5164 1
CO = 170 = 0010101010 t 0010101010.0000 0 8083 1
CO = 170 = 0010101010 t 0010101010.0001 1 141953 1
CO = 170 = 0010101010 t 0010101010.0010 2 57590 1
...
CO = 170 = 0010101010 t 0010101010.1110 e 84471 1
CO = 170 = 0010101010 t 0010101010.1111 f 61264 1

A coluna cbits é composta de duas partes, o código do país e o código da sua célula L0 (traduzido em hexadecimal=base16). A interseção da geometria da célula com o polígono do país tem a sua área indicada pela coluna area_km2. A coluna is_contained é um flag, verdadeiro quando a célula tem interseção com as bordas do país. Ver Issue 02 para detalhes sobre a dedução do caso cheio pela área. São esperadas potências de 2 nas células cheias de is_contained. Para o Brasil é esperada a potência 20 no tamanho de lado do quadrado: . Para Camarões (CM) potência 18: .

PS: "bug de nome de coluna" a corrigir, a coluna is_contained deveria indicar que a célula está totalmente contida no polígono do país, mas está sendo usado o seu NOT.

Os códigos de país nessa versão ainda eram códigos ISO ocupando 10 bits. Por exemplo Brasil 0001001100 é 76. Na versão corrente são códigos internos (sem compromisso com ISO) de 8 bits, garantindo melhor ocupação nos 57 bits (56 bits para acomodar 14 dígitos hexadecimais) disponíveis de geocódigo hInt64.

Gerando a grade L0 de cobertura do país

Como vimos, as configurações são transferidas para as mesmas tabelas onde foram geradas e arquivadas as geometrias L0. Podemos simplesmente recuperá-las. Tabela atual osmc.coverage: 

    Column     |    Type     | Collation | Nullable | Default 
---------------+-------------+-----------+----------+---------
          | bit varying |           |          | 
 isolabel_ext  | text        |           |          | 
 cindex        | text        |           |          | 
 bbox          | integer[]   |           |          | 
 status        | smallint    |           |          | 0
 is_country    | boolean     |           |          | false
 is_contained  | boolean     |           |          | false
 is_overlay    | boolean     |           |          | false
 kx_prefix     | text        |           |          | 
 geom          | geometry    |           |          | 
 geom_srid4326 | geometry    |           |          | 

DROP view if exists report.v002_osmc_coverage_l0_geoms
; 
CREATE VIEW report.v002_osmc_coverage_l0_geoms AS
 select row_number() OVER (ORDER BY cbits) AS gid, *,
        isolabel_ext ||'+'||cbits_b16 as afacode
 from (
  select isolabel_ext,cbits,
    natcod.vbit_to_baseh(substring(cbits,11)::bit varying, 16) AS cbits_b16,
    geom_srid4326
  from osmc.coverage where is_country and isolabel_ext IN ('BR','CO','CM')
 ) t
;

Resultado da view no QGIS:

Report v002.png

Sem recorte

...


Ver também

Outras dicas: 

-- Simulando a chamada online da cobertura do município de Boa Vista:
select i, natcod.vbit_to_str(i::bit(5),'32nvu') as x_b32nvu,
       substr(  api.osmcode_decode_postal( natcod.vbit_to_str(i::bit(5),'32nvu') ,'BR-RR-BoaVista')::text, 1,120) as  geojson_cit120
from generate_series(0,31) t(i);
Disponível em “https://wiki.addressforall.org/w/index.php?title=osmc:Metodologia/Algoritmo_SQL&oldid=3939”