osmc:BR/SQL: mudanças entre as edições
m (→Teste online) |
m (→Scripts) |
||
(2 revisões intermediárias pelo mesmo usuário não estão sendo mostradas) | |||
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('8a,8b,9','BR',18)</code>, que discutimos neste artigo Wiki. | As funções API trabalham apenas com JSON. A função equivalente porém retornando dados SQL seria <code>osmc.decode_scientific_absolute_geoms('8a,8b,9','BR',18)</code>, que discutimos neste artigo Wiki. | ||
== Scripts == | |||
<pre> | |||
-- new: ver script newGGeohash no NatCod | |||
----------------------------------------- | |||
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')) | |||
; | |||
</pre> | |||
== PoC dos municípios == | == PoC dos municípios == | ||
São Bento do Sapucai, | São Bento do Sapucai (BR-SP-SaoBentoSapucai), Nova Friburgo (BR-RJ-NovaFriburgo), Caruaru (BR-PE-Caruaru). | ||
<pre> | |||
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) | |||
; | |||
</pre> |
Edição atual tal como às 08h48min de 15 de julho de 2024
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
-- new: ver script newGGeohash no NatCod ----------------------------------------- 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) ;