osmc:BR/SQL
Documentação do processo SQL de geração das grades do Brasil, detalhando o apresentado em osmc:Metodologia/Algoritmo SQL.
Teste online
A visualização Web é oferecida no site AFAcodes, por exemplo as células 8a, 8b e 9 são visualizadas por https://afa.codes/BR+8a,8b,9
. São construídas pelo endpoint https://afa.codes/geo:osmcodes:BR+{codigos}.json
. Por exemplo https://afa.codes/geo:osmcodes:BR+8a,8b,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('8a,8b,9','BR',18)
. 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('8a,8b,9','BR',18)
, que discutimos neste artigo Wiki.
Scripts
----------------------------------------- DROP SCHEMA IF EXISTS grid_br CASCADE ; CREATE SCHEMA grid_br ; CREATE TABLE grid_br.all_levels( gid_vbit varbit NOT NULL PRIMARY KEY ,hlevel real NOT NULL CHECK(hlevel>=0.0 AND hlevel<30.0) ,code_b16h text NOT NULL ,geom geometry NOT NULL ,UNIQUE(code_b16h) ,UNIQUE(geom) ); ----- -- function grid_br.generate_all_levels() returns TABLE LIKE grid_br.all_levels ?? see------ INSERT INTO grid_br.all_levels SELECT DISTINCT gid_vbit,hlevel,code_b16h,geom FROM osmc.grid_generate_all_levels(4.0, 'BR', '{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,ft,fy,fp,fn}', 18, 0.005, 0.00000005) ; ------ CREATE TABLE grid_br.isoadmin_l0 AS SELECT osm_id,isolabel_ext,geom FROM optim.jurisdiction_geom WHERE isolabel_ext='BR'; CREATE TABLE grid_br.isoadmin_l1 AS SELECT osm_id,isolabel_ext,geom FROM optim.jurisdiction_geom WHERE isolabel_ext~'^BR\-..$'; CREATE TABLE grid_br.isoadmin_l2 AS SELECT osm_id,isolabel_ext,geom FROM optim.jurisdiction_geom WHERE isolabel_ext~'^BR\-..\-'; -- CREATE VIEW grid_br.vw_level00_0 AS SELECT * FROM grid_br.all_levels WHERE hlevel=0; CREATE VIEW grid_br.vw_level00_5 AS SELECT * FROM grid_br.all_levels WHERE hlevel=0.5; CREATE VIEW grid_br.vw_level01_0 AS SELECT * FROM grid_br.all_levels WHERE hlevel=1.0; CREATE VIEW grid_br.vw_level01_5 AS SELECT * FROM grid_br.all_levels WHERE hlevel=1.5; CREATE VIEW grid_br.vw_level02_0 AS SELECT * FROM grid_br.all_levels WHERE hlevel=2.0; CREATE VIEW grid_br.vw_level02_5 AS SELECT * FROM grid_br.all_levels WHERE hlevel=2.5; CREATE VIEW grid_br.vw_level03_0 AS SELECT * FROM grid_br.all_levels WHERE hlevel=3.0; CREATE VIEW grid_br.vw_level03_5 AS SELECT * FROM grid_br.all_levels WHERE hlevel=3.5; CREATE VIEW grid_br.vw_level04_0 AS SELECT * FROM grid_br.all_levels WHERE hlevel=4.0; --pg_dump -U postgres -n grid_br lixo5 | gzip -c > grid_br_dump.sql.gz -- Exemplo com geometria de fundo no QGIS: CREATE VIEW grid_br.vw_level03_5_MG AS SELECT * FROM grid_br.all_levels WHERE hlevel=3.5 AND st_intersects(geom,(select st_transform(geom,952019) from grid_br.isoadmin_l1 where isolabel_ext='BR-MG')) ;
PoC dos municípios
São Bento do Sapucai (BR-SP-SaoBentoSapucai), Nova Friburgo (BR-RJ-NovaFriburgo), Caruaru (BR-PE-Caruaru).
DROP VIEW grid_br.vw_level03_5_poc; DROP VIEW grid_br.vw_level04_0_poc; drop view grid_br.vw_poc; CREATE VIEW grid_br.vw_poc AS SELECT osm_id, isolabel_ext, st_transform(geom,952019) as geom from grid_br.isoadmin_l2 where isolabel_ext IN ('BR-SP-SaoBentoSapucai','BR-RJ-NovaFriburgo','BR-PE-Caruaru') ; -- PERIGO TESTE precario - adaptar com grid_generate_all_levels_subcover, que faz seleção simbolica antes. INSERT INTO grid_br.all_levels SELECT DISTINCT t.gid_vbit,t.hlevel,t.code_b16h,t.geom FROM osmc.grid_generate_all_levels(9.5, 'BR', '{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,ft,fy,fp,fn}', 18, 0.005, 0.00000005) t INNER JOIN grid_br.vw_poc p ON t.hlevel>4 AND st_intersects(t.geom,p.geom) ; -- restante ok: CREATE VIEW grid_br.vw_level03_5_poc AS SELECT g.* FROM grid_br.all_levels g INNER JOIN grid_br.vw_poc p ON hlevel=3.5 AND st_intersects(g.geom,p.geom) ; CREATE VIEW grid_br.vw_level04_0_poc AS SELECT g.* FROM grid_br.all_levels g INNER JOIN grid_br.vw_poc p ON hlevel=4 AND st_intersects(g.geom,p.geom) ; CREATE VIEW grid_br.vw_level04_5_poc AS SELECT g.* FROM grid_br.all_levels g INNER JOIN grid_br.vw_poc p ON hlevel=4 AND st_intersects(g.geom,p.geom) ;