osmc:Metodologia/Algoritmo SQL: mudanças entre as edições
(view v002) |
mSem resumo de edição |
||
Linha 1: | Linha 1: | ||
Detalhamento metodológico, descrevendo os algoritmos implementados em linguagem SQL. | Detalhamento metodológico, descrevendo os algoritmos implementados em linguagem SQL. | ||
Para obtenção do código-fonte: | |||
# Versão desejada se homologada: git. | |||
# Versão corrente do servidor: se em dúvida, usar <code>pg_dump --schema-only -f dl05_dump_test.sql dl05</code>. | |||
== Configuração do país == | == Configuração do país == |
Edição das 18h12min de 2 de junho de 2024
Detalhamento metodológico, descrevendo os algoritmos implementados em linguagem SQL. Para obtenção do código-fonte:
- Versão desejada se homologada: git.
- 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:
- 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. 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:
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);