osmc:CM/SQL: mudanças entre as edições
(report.v004_osmc_coverage_distrib) |
|||
(7 revisões intermediárias pelo mesmo usuário não estão sendo mostradas) | |||
Linha 2: | Linha 2: | ||
== Teste online == | == Teste online == | ||
A visualização Web é oferecida no site AFAcodes, por exemplo as células 8a, 8b e 9 são visualizadas por <code>https://afa.codes/CM+8b,8e,9</code>. São construídas pelo ''endpoint'' <code><nowiki>https://afa.codes/geo:osmcodes:CM+{codigos}.json</nowiki></code>. Por exemplo <code>https://afa.codes/geo:osmcodes:CM+8b,8e,9.json</code>. | A visualização Web é oferecida no site AFAcodes, por exemplo as células científicas "8a", "8b" e "9" são visualizadas por <code>https://afa.codes/CM+8b,8e,9</code>. São construídas pelo ''endpoint'' <code><nowiki>https://afa.codes/geo:osmcodes:CM+{codigos}.json</nowiki></code>. Por exemplo <code>https://afa.codes/geo:osmcodes:CM+8b,8e,9.json</code>. | ||
No NGINX o ''endpoint'' redireciona a solicitação para o PostgreSQL através do PostgREST, no SQL-schema <code>api</code>. No exemplo é utilizada a chamada <code>api.osmcode_decode_scientific_absolute('8b,8e,9','CM',16)</code>. Maiores detalhes sobre a API em [[osmc:Api]]. | No NGINX o ''endpoint'' redireciona a solicitação para o PostgreSQL através do PostgREST, no SQL-schema <code>api</code>. No exemplo é utilizada a chamada <code>api.osmcode_decode_scientific_absolute('8b,8e,9','CM',16)</code>. Maiores detalhes sobre a API em [[osmc:Api]]. | ||
Linha 8: | Linha 8: | ||
As funções API trabalham apenas com JSON. A função equivalente porém retornando dados SQL seria <code>osmc.decode_scientific_absolute_geoms('8b,8e,9','CM',16)</code>, que discutimos neste artigo Wiki. <br/>A geração de céulas menores, da ordem de metros ou poucos quilômetros, dispensa a inclusão de pontos, <code>osmc.decode_scientific_absolute_geoms('8b,8e,9','CM',16,true)</code>. | As funções API trabalham apenas com JSON. A função equivalente porém retornando dados SQL seria <code>osmc.decode_scientific_absolute_geoms('8b,8e,9','CM',16)</code>, que discutimos neste artigo Wiki. <br/>A geração de céulas menores, da ordem de metros ou poucos quilômetros, dispensa a inclusão de pontos, <code>osmc.decode_scientific_absolute_geoms('8b,8e,9','CM',16,true)</code>. | ||
==Grade L0== | == Grades Científicas == | ||
O sistema de ''grades científicas'' é pré-requisito para definir o sistema de grades logísticas: geometricamente são as mesmas células, resultando nas logísticas como subconjunto. | |||
Na sua definição técnica, a cobertura municipal é uma lista de geocódigos científicos, e, como todas são de células com lado superior a 4km, podemos deixar prontas as grades L0 a L5 de camarões. Abaixo disso oferecemos apenas grades logísticas. Nos níveis de cobertura municipal, como L5, incluímos na coluna ''info'' a lista de apelidos oficiais da célula — e sendo uma célula de borda, pode acontecer da mesma célula ser cobertura para dois municípios. | |||
===Grade L0=== | |||
[[Arquivo:Grid-CM-L0.png|miniaturadaimagem|280px|Grade scientífica nível ''L0''.]] | [[Arquivo:Grid-CM-L0.png|miniaturadaimagem|280px|Grade scientífica nível ''L0''.]] | ||
A grade ''L0'' de células inteiras e respectivos rótulos, ilustrada ao lado, foi gerada por: | A grade ''L0'' de células inteiras e respectivos rótulos, ilustrada ao lado, foi gerada por: | ||
Linha 19: | Linha 24: | ||
INCLUDING ALL | INCLUDING ALL | ||
); | ); | ||
INSERT INTO grid_cm.l0 | INSERT INTO grid_cm.l0 | ||
SELECT * | SELECT *, ST_Transform_resilient(geom,geom4326) as geom4326 | ||
FROM osmc.decode_scientific_absolute_geoms( | FROM osmc.decode_scientific_absolute_geoms( | ||
'1,2,3,4,5,6,7,8,9,a,b,c,d', | '1,2,3,4,5,6,7,8,9,a,b,c,d', | ||
Linha 28: | Linha 33: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
A lista de de geocódigos é conhecida, faz parte da configuração do país, fixada no [https://git.afa.codes/CM/blob/main/conf.yaml#L37 git oficial do país], como ''grid_l0_cell_sci_base''. | A lista de de geocódigos no argumento da função ''decode'' é conhecida, faz parte da configuração do país, fixada no [https://git.afa.codes/CM/blob/main/conf.yaml#L37 git oficial do país], como ''grid_l0_cell_sci_base''. | ||
A tabela gerada tem a seguinte estrutura: | A tabela gerada tem a seguinte estrutura: | ||
Linha 34: | Linha 39: | ||
Column | Type | Description | Column | Type | Description | ||
----------------+----------+------------------- | ----------------+----------+------------------- | ||
gid_vbit | varbit | PK. The complete hierarchical geocode, with country prefix. | |||
code_b16h | text | Geocode, withoutout the country prefix. | |||
is_border | text | flag indicating cell intersecting the country border. | |||
geom | geometry | Original geometri, in the native SRID projection. | geom | geometry | Original geometri, in the native SRID projection. | ||
geom4326 | geometry | | geom4326 | geometry | The geometry with no projection, by ST_Transform_resilient(). | ||
</pre> | </pre> | ||
Os recortes podem ser obtidos pela função de recortes por jurisdição, ''osmc.grid_jurisdiction_cutcells''(), que faz simplesmente o ST_Intersect da geometria da jurisdição com as células. | Os recortes podem ser obtidos pela função de recortes por jurisdição, ''osmc.grid_jurisdiction_cutcells''(), que faz simplesmente o ST_Intersect da geometria da jurisdição com as células. A redundância na geometria, com coluna geom4326, só é necessária nas células muito grandes. Faz uso da função ST_Transform_resilient() para resolver o https://gis.stackexchange.com/q/444441/7505 | ||
==Grade L1== | ===Grade L1=== | ||
[[Arquivo:Grid-CM-L0.png|miniaturadaimagem|280px|Grade scientífica nível ''L1''.]] | [[Arquivo:Grid-CM-L0.png|miniaturadaimagem|280px|Grade scientífica nível ''L1''.]] | ||
rascunho A grade ''L1'' de células inteiras e respectivos rótulos, ilustrada ao lado, foi gerada por: | rascunho A grade ''L1'' de células inteiras e respectivos rótulos, ilustrada ao lado, foi gerada por: | ||
Linha 72: | Linha 75: | ||
; | ; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== Grades logísticas == | |||
Todos os municípios tiveram suas respectivas coberturas geradas pelo procedimento descrito em ?? (Claiton), que faz a inclusão dos seus resultados na mesma tabela onde foram inclusas as coberturas nacionais, ''osmc.cover''. As coberturas municipais oficiais são listadas em https://git.afa.codes/CM/blob/main/data/coverage.csv | |||
Caso seja necessário gerar nova versão a partir do gerenciado pela base, basta exportar para CVS esta ''query'': | |||
<syntaxhighlight lang="sql"> | |||
DROP VIEW IF EXISTS report.v003_osmc_coverage_export CASCADE | |||
; | |||
CREATE VIEW report.v003_osmc_coverage_export AS | |||
SELECT isolevel, isolabel_ext, status, | |||
array_agg(DISTINCT b16 ORDER BY b16) FILTER(WHERE not(is_overlay)) as cover, | |||
array_agg(DISTINCT b16 ORDER BY b16) FILTER(WHERE is_overlay) as overlay, | |||
max(round(length(suffix)/2.0 - 2, 1)) FILTER(WHERE not(is_overlay)) as cover_cells_level, | |||
max(round(length(suffix)/2.0 - 2, 1)) FILTER(WHERE is_overlay) as overlay_cells_level | |||
FROM ( | |||
SELECT *, natcod.vbit_to_baseh(substring(cbits,11),16) as b16, | |||
substring(cbits,11) suffix, | |||
cardinality(string_to_array(isolabel_ext,'-')) isolevel | |||
FROM osmc.coverage | |||
) t -- WHERE status=1? | |||
GROUP BY 1,2,3 | |||
ORDER BY 1,2,3 | |||
; | |||
DROP VIEW IF EXISTS report.v004_osmc_coverage_distrib CASCADE | |||
; | |||
CREATE VIEW report.v004_osmc_coverage_distrib AS | |||
SELECT substring(isolabel_ext,1,2) as country, isolevel, cover_cells_level, | |||
count(*) n_municipalities, | |||
round(avg(cardinality(cover))) avg_n_cells | |||
FROM report.v003_osmc_coverage_export | |||
GROUP BY 1,2,3 | |||
ORDER BY 1,2,3 | |||
; | |||
-- select isolabel_ext, cover_cells_level,overlay_cells_level from report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%' order by 2, 1; | |||
-- select cover_cells_level, n_municipalities, avg_n_cells from report.v004_osmc_coverage_distrib where country='CM' and isolevel=3; | |||
-- COPY (SELECT isolabel_ext, status, cover, overlay FROM report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%') TO 'out.csv' CSV HEADER; | |||
</syntaxhighlight> | |||
A seguir apenas dois exemplos, [https://afa.codes/CM-CE-Yoko CM-CE-Yoko] (com ''overlay'' no centro urbano) e [https://afa.codes/CM-NO-Dembo Dembo no Norte] (ex. "46B4" cai na área de ''overlay''). |
Edição atual tal como às 23h00min de 5 de junho de 2024
Documentação do processo SQL de geração das grades em Camarões, detalhando o apresentado em osmc:Metodologia/Algoritmo SQL.
Teste online
A visualização Web é oferecida no site AFAcodes, por exemplo as células científicas "8a", "8b" e "9" são visualizadas por https://afa.codes/CM+8b,8e,9
. São construídas pelo endpoint https://afa.codes/geo:osmcodes:CM+{codigos}.json
. Por exemplo https://afa.codes/geo:osmcodes:CM+8b,8e,9.json
.
No NGINX o endpoint redireciona a solicitação para o PostgreSQL através do PostgREST, no SQL-schema api
. No exemplo é utilizada a chamada api.osmcode_decode_scientific_absolute('8b,8e,9','CM',16)
. Maiores detalhes sobre a API em osmc:Api.
As funções API trabalham apenas com JSON. A função equivalente porém retornando dados SQL seria osmc.decode_scientific_absolute_geoms('8b,8e,9','CM',16)
, que discutimos neste artigo Wiki.
A geração de céulas menores, da ordem de metros ou poucos quilômetros, dispensa a inclusão de pontos, osmc.decode_scientific_absolute_geoms('8b,8e,9','CM',16,true)
.
Grades Científicas
O sistema de grades científicas é pré-requisito para definir o sistema de grades logísticas: geometricamente são as mesmas células, resultando nas logísticas como subconjunto.
Na sua definição técnica, a cobertura municipal é uma lista de geocódigos científicos, e, como todas são de células com lado superior a 4km, podemos deixar prontas as grades L0 a L5 de camarões. Abaixo disso oferecemos apenas grades logísticas. Nos níveis de cobertura municipal, como L5, incluímos na coluna info a lista de apelidos oficiais da célula — e sendo uma célula de borda, pode acontecer da mesma célula ser cobertura para dois municípios.
Grade L0
A grade L0 de células inteiras e respectivos rótulos, ilustrada ao lado, foi gerada por:
DROP TABLE IF EXISTS grid_cm.l0
;
CREATE TABLE grid_cm.l0 (
LIKE osmc.tflike_decode_scientific_absolute_geoms
INCLUDING ALL
);
INSERT INTO grid_cm.l0
SELECT *, ST_Transform_resilient(geom,geom4326) as geom4326
FROM osmc.decode_scientific_absolute_geoms(
'1,2,3,4,5,6,7,8,9,a,b,c,d',
'CM',
16
);
A lista de de geocódigos no argumento da função decode é conhecida, faz parte da configuração do país, fixada no git oficial do país, como grid_l0_cell_sci_base.
A tabela gerada tem a seguinte estrutura:
Column | Type | Description ----------------+----------+------------------- gid_vbit | varbit | PK. The complete hierarchical geocode, with country prefix. code_b16h | text | Geocode, withoutout the country prefix. is_border | text | flag indicating cell intersecting the country border. geom | geometry | Original geometri, in the native SRID projection. geom4326 | geometry | The geometry with no projection, by ST_Transform_resilient().
Os recortes podem ser obtidos pela função de recortes por jurisdição, osmc.grid_jurisdiction_cutcells(), que faz simplesmente o ST_Intersect da geometria da jurisdição com as células. A redundância na geometria, com coluna geom4326, só é necessária nas células muito grandes. Faz uso da função ST_Transform_resilient() para resolver o https://gis.stackexchange.com/q/444441/7505
Grade L1
rascunho A grade L1 de células inteiras e respectivos rótulos, ilustrada ao lado, foi gerada por:
-- geração da sequencia completa das demais coberturas candidatas:
select array_agg(i||j) from unnest('{1,2,3,4,5,6,7,8,9,a,b,c,d,e}'::text[]) t1(i), unnest('{h,m,r,v}'::text[]) t2(j);
DROP TABLE IF EXISTS grid_cm.l1
;
CREATE TABLE grid_cm.l1 (
LIKE osmc.tflike_decode_scientific_absolute_geoms
INCLUDING ALL
);
INSERT INTO grid_cm.l1 AS
select row_number() over() as gid, *
from (
SELECT 76::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits,
code as b16_label,
'0'||lower(code) as prefix, geom
FROM osmc.decode_scientific_absolute_geoms(
'1h,2h,3h,4h,5h,6h,7h,8h,9h,ah,bh,ch,dh,eh,1m,2m,3m,4m,5m,6m,7m,8m,9m,am,bm,cm,dm,em,1r,2r,3r,4r,5r,6r,7r,8r,9r,ar,br,cr,dr,er,1v,2v,3v,4v,5v,6v,7v,8v,9v,av,bv,cv,dv,ev'
,'CM',16)
where st_intersects(geom, st_transform((select geom from optim.jurisdiction_geom where isolabel_ext='BR'),952019) )
) t3
;
Grades logísticas
Todos os municípios tiveram suas respectivas coberturas geradas pelo procedimento descrito em ?? (Claiton), que faz a inclusão dos seus resultados na mesma tabela onde foram inclusas as coberturas nacionais, osmc.cover. As coberturas municipais oficiais são listadas em https://git.afa.codes/CM/blob/main/data/coverage.csv
Caso seja necessário gerar nova versão a partir do gerenciado pela base, basta exportar para CVS esta query:
DROP VIEW IF EXISTS report.v003_osmc_coverage_export CASCADE
;
CREATE VIEW report.v003_osmc_coverage_export AS
SELECT isolevel, isolabel_ext, status,
array_agg(DISTINCT b16 ORDER BY b16) FILTER(WHERE not(is_overlay)) as cover,
array_agg(DISTINCT b16 ORDER BY b16) FILTER(WHERE is_overlay) as overlay,
max(round(length(suffix)/2.0 - 2, 1)) FILTER(WHERE not(is_overlay)) as cover_cells_level,
max(round(length(suffix)/2.0 - 2, 1)) FILTER(WHERE is_overlay) as overlay_cells_level
FROM (
SELECT *, natcod.vbit_to_baseh(substring(cbits,11),16) as b16,
substring(cbits,11) suffix,
cardinality(string_to_array(isolabel_ext,'-')) isolevel
FROM osmc.coverage
) t -- WHERE status=1?
GROUP BY 1,2,3
ORDER BY 1,2,3
;
DROP VIEW IF EXISTS report.v004_osmc_coverage_distrib CASCADE
;
CREATE VIEW report.v004_osmc_coverage_distrib AS
SELECT substring(isolabel_ext,1,2) as country, isolevel, cover_cells_level,
count(*) n_municipalities,
round(avg(cardinality(cover))) avg_n_cells
FROM report.v003_osmc_coverage_export
GROUP BY 1,2,3
ORDER BY 1,2,3
;
-- select isolabel_ext, cover_cells_level,overlay_cells_level from report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%' order by 2, 1;
-- select cover_cells_level, n_municipalities, avg_n_cells from report.v004_osmc_coverage_distrib where country='CM' and isolevel=3;
-- COPY (SELECT isolabel_ext, status, cover, overlay FROM report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%') TO 'out.csv' CSV HEADER;
A seguir apenas dois exemplos, CM-CE-Yoko (com overlay no centro urbano) e Dembo no Norte (ex. "46B4" cai na área de overlay).