2 384
edições
(add scripts) |
|||
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> | |||
----------------------------------------- | |||
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 (BR-SP-SaoBentoSapucai), Nova Friburgo (BR-RJ-NovaFriburgo), Caruaru (BR-PE-Caruaru). | 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ções