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