osmc:Metodologia/Algoritmo SQL: mudanças entre as edições
(Configuração do país) |
mSem resumo de edição |
||
Linha 14: | Linha 14: | ||
A configuração é efetivada pela chamada com o país específico, por exemplo Brasil <code>SELECT osmc.L0cover_upsert('BR');</code> | A configuração é efetivada pela chamada com o país específico, por exemplo Brasil <code>SELECT osmc.L0cover_upsert('BR');</code> | ||
A função de que fato implementa as configurações é [https://github.com/osm-codes/GGeohash/blob/main/src/step04def-ini.sql#L1 osmc.L0_upsert()]. Importante notar que, dentro dela, temos um comando <code>unnest(a,b)</code> 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: | Deveria se chamar <code>L0_upsert_get(contry)</code>. A função de que fato implementa as configurações é [https://github.com/osm-codes/GGeohash/blob/main/src/step04def-ini.sql#L1 osmc.L0_upsert()]. Importante notar que, dentro dela, temos um comando <code>unnest(a,b)</code> 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: | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||
SELECT count(*) n, count(distinct prefix) n_prefix -- n=5 | n_prefix=3 | 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); | FROM unnest('{40,41,42}'::int[],'{00,01,02,0a,0b}'::text[]) t(prefix,quadrant); | ||
</syntaxhighlight> | |||
A função <code>L0_upsert()</code> 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. | |||
<syntaxhighlight lang="sql"> | |||
SELECT isolabel_ext ||' = '||(b'0000000000000000000000'||cb10::bit(32)::int ||' = '||cb10 as pais, | |||
is_contained, | |||
cb10::text||'.'||substring(cbits,11)::text as cbits, | |||
status, | |||
round(st_area(geom)/1000000.0) as area_km2 | |||
FROM (select *, substring(cbits,1,10) as cb10) from osmc.coverage) t | |||
WHERE is_country ORDER BY 1,2,cbits; | |||
</syntaxhighlight> | |||
{| class="wikitable" | |||
|- | |||
! pais !! is_contained !! cbits !! status !! area_km2 | |||
|- | |||
| BR = 76 = 0001001100 || f || 0001001100.00000110 || 1 || 1099512 | |||
|- | |||
| BR = 76 = 0001001100 || t || 0001001100.00000000 || 1 || 275241 | |||
|- | |||
| BR = 76 = 0001001100 || t || 0001001100.00000001 || 1 || 757637 | |||
|- | |||
| BR = 76 = 0001001100 || t || 0001001100.00000010 || 1 || 645014 | |||
|- | |||
|... | |||
|- | |||
| BR = 76 = 0001001100 || t || 0001001100.00010000 || 1 || 1519 | |||
|- | |||
| BR = 76 = 0001001100 || t || 0001001100.00010001 || 1 || 3618 | |||
|- | |||
| CM = 120 = 0001111000 || f || 0001111000.1001 || 1 || 68719 | |||
|- | |||
| CM = 120 = 0001111000 || t || 0001111000.0001 || 1 || 2469 | |||
|- | |||
| CM = 120 = 0001111000 || t || 0001111000.0010 || 1 || 24181 | |||
|- | |||
|... | |||
|- | |||
| CM = 120 = 0001111000 || t || 0001111000.1101 || 1 || 47183 | |||
|- | |||
| CM = 120 = 0001111000 || t || 0001111000.1110 || 1 || 5164 | |||
|- | |||
| CO = 170 = 0010101010 || t || 0010101010.0000 || 1 || 8083 | |||
|- | |||
| CO = 170 = 0010101010 || t || 0010101010.0001 || 1 || 141953 | |||
|- | |||
| CO = 170 = 0010101010 || t || 0010101010.0010 || 1 || 57590 | |||
|- | |||
|... | |||
|- | |||
| CO = 170 = 0010101010 || t || 0010101010.1110 || 1 || 84471 | |||
|- | |||
| CO = 170 = 0010101010 || t || 0010101010.1111 || 1 || 61264 | |||
|} | |||
Os códigos de país podem ser obtidos com zeros a esquerda, por exemplo Brasil <code>select (b'0000000000000000000000'||b'00010011')::bit(32)::int;</code> é 76. | |||
Há um bug de nomenclatura. Analisando as áreas do Brasil, o flag foi apenas para os 1099512 km2 de área, cuja raís quadada é uma das potências de dois <math>\sqrt{1099512} = 2^{20} = 1048.58</math>. | |||
------ | |||
Outras dicas: | |||
<syntaxhighlight lang="sql"> | |||
-- 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); | |||
</syntaxhighlight> | </syntaxhighlight> |
Edição das 17h08min de 26 de maio de 2024
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.
SELECT isolabel_ext ||' = '||(b'0000000000000000000000'||cb10::bit(32)::int ||' = '||cb10 as pais,
is_contained,
cb10::text||'.'||substring(cbits,11)::text as cbits,
status,
round(st_area(geom)/1000000.0) as area_km2
FROM (select *, substring(cbits,1,10) as cb10) from osmc.coverage) t
WHERE is_country ORDER BY 1,2,cbits;
pais | is_contained | cbits | status | area_km2 |
---|---|---|---|---|
BR = 76 = 0001001100 | f | 0001001100.00000110 | 1 | 1099512 |
BR = 76 = 0001001100 | t | 0001001100.00000000 | 1 | 275241 |
BR = 76 = 0001001100 | t | 0001001100.00000001 | 1 | 757637 |
BR = 76 = 0001001100 | t | 0001001100.00000010 | 1 | 645014 |
... | ||||
BR = 76 = 0001001100 | t | 0001001100.00010000 | 1 | 1519 |
BR = 76 = 0001001100 | t | 0001001100.00010001 | 1 | 3618 |
CM = 120 = 0001111000 | f | 0001111000.1001 | 1 | 68719 |
CM = 120 = 0001111000 | t | 0001111000.0001 | 1 | 2469 |
CM = 120 = 0001111000 | t | 0001111000.0010 | 1 | 24181 |
... | ||||
CM = 120 = 0001111000 | t | 0001111000.1101 | 1 | 47183 |
CM = 120 = 0001111000 | t | 0001111000.1110 | 1 | 5164 |
CO = 170 = 0010101010 | t | 0010101010.0000 | 1 | 8083 |
CO = 170 = 0010101010 | t | 0010101010.0001 | 1 | 141953 |
CO = 170 = 0010101010 | t | 0010101010.0010 | 1 | 57590 |
... | ||||
CO = 170 = 0010101010 | t | 0010101010.1110 | 1 | 84471 |
CO = 170 = 0010101010 | t | 0010101010.1111 | 1 | 61264 |
Os códigos de país podem ser obtidos com zeros a esquerda, por exemplo Brasil select (b'0000000000000000000000'||b'00010011')::bit(32)::int;
é 76.
Há um bug de nomenclatura. Analisando as áreas do Brasil, o flag foi apenas para os 1099512 km2 de área, cuja raís quadada é uma das potências de dois .
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);