osmc:Metodologia/Algoritmo SQL

Detalhamento metodológico, descrevendo os algoritmos implementados em linguagem SQL.

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:

• Configurações do Brasil https://git.osm.codes/BR_new/blob/main/conf.yaml#L34
• Configurações de Camarões https://git.osm.codes/CM/blob/main/conf.yaml#L37
• Configurações da Colômbia https://git.osm.codes/CO_new/blob/main/conf.yaml#L34

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:

• Inclusão da configuração do Brasil https://git.osm.codes/GGeohash/blob/main/src/step04def-ini.sql#L95
• Inclusão da configuração de Camarões https://git.osm.codes/GGeohash/blob/main/src/step04def-ini.sql#L96
• Inclusão da configuração da Colômbia https://git.osm.codes/GGeohash/blob/main/src/step04def-ini.sql#L98

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. Analisando as áreas do Brasil, o flag foi falso apenas para os $1099512\ km^{2}$ de área, cuja raíz quadada é o tamanho do lado do quadrado, e uma das potências de 2 utilizadas, ${\sqrt {1099512\ km^{2}}}=2^{20}\ km=1048,58\ km$. 4.319,4 quilômetros 1048576
PS: a coluna is_contained deveria indicar que a célula está totalmente contida no polígono do país.

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.

Ver também

• osmc:Metodologia/Algoritmo SQL/Issues

---

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);