|
|
Linha 187: |
Linha 187: |
|
| |
|
| == Gerando a grade L0 de cobertura do país == | | == Gerando a grade L0 de cobertura do país == |
| | Existem duas possibilidades de trabalho com a grade: |
| | |
| | # Com seletores e algoritmos de ''encode''/''decode''. |
| | # Com geometria das grades de células maiores, partindo de ''L0'', e depois, acima de certo nível, usando funções de geração de grade virtual. |
|
| |
|
| Como vimos, as configurações são transferidas para as mesmas tabelas onde foram geradas e arquivadas as geometrias L0. Podemos simplesmente recuperá-las. Tabela atual ''osmc.coverage'': | | Como vimos, as configurações são transferidas para as mesmas tabelas onde foram geradas e arquivadas as geometrias L0. Podemos simplesmente recuperá-las. Tabela atual ''osmc.coverage'': |
Linha 192: |
Linha 196: |
| Column | Type | Collation | Nullable | Default | | Column | Type | Collation | Nullable | Default |
| ---------------+-------------+-----------+----------+--------- | | ---------------+-------------+-----------+----------+--------- |
| | bit varying | | |
| | cbits | bit varying | | | |
| isolabel_ext | text | | | | | isolabel_ext | text | | | |
| cindex | text | | | | | cindex | text | | | |
Linha 227: |
Linha 231: |
| [[Arquivo:BRgrid-L0-QGISv1.png|centro|semmoldura|620px]] | | [[Arquivo:BRgrid-L0-QGISv1.png|centro|semmoldura|620px]] |
|
| |
|
| <syntaxhighlight lang="sql">
| | Funções ''osmc.decode_scientific_absolute_geoms'' e ''osmc.L0cover_br_geoms'' em https://github.com/osm-codes/GGeohash/blob/main/src/step03def-lib.sql#L1520 |
| -- FUNCAO MIGROU PARA O GIT, APAGAR!
| |
| CREATE FUNCTION osmc.decode_scientific_absolute_geoms(
| |
| p_code text, -- um ou mais (separados por virgula) afaCodes científicos separados por virgula
| |
| p_iso text, -- pais de contextualização do afaCode.
| |
| p_base integer DEFAULT 18, -- detecta antes se usa gambiarra se falsa célula ... não devia precisar.
| |
| p_use_resilient boolean DEFAULT true
| |
| ) RETURNS TABLE (
| |
| code text,
| |
| area real,
| |
| side real,
| |
| truncated_code text,
| |
| base text,
| |
| geom geometry,
| |
| geom4326 geometry
| |
| ) language SQL IMMUTABLE
| |
| AS $f$
| |
| SELECT
| |
| TRANSLATE(code_tru,'gqhmrvjknpstzy','GQHMRVJKNPSTZY') as code,
| |
| ST_Area(v.geom) as area,
| |
| SQRT(ST_Area(v.geom)) as side,
| |
| truncated_code,
| |
| osmc.string_base(p_base) as base,
| |
| v.geom,
| |
| CASE WHEN p_use_resilient THEN ST_Transform_resilient(v.geom,4326,0.005) ELSE ST_Transform(v.geom,4326) END as geom4326
| |
| FROM (
| |
| SELECT DISTINCT code16h,
| |
| | |
| -- trunca
| |
| CASE
| |
| WHEN p_base <> 18 AND length(code16h) > 12 AND up_iso IN ('BR') THEN substring(code16h,1,12)
| |
| WHEN p_base <> 18 AND length(code16h) > 11 AND up_iso IN ('EC','CO','UY') THEN substring(code16h,1,11)
| |
| WHEN p_base <> 18 AND length(code16h) > 10 AND up_iso IN ('CM') THEN substring(code16h,1,10)
| |
| WHEN p_base = 18 AND length(code) > 11 AND up_iso IN ('BR') THEN substring(code,1,11)
| |
| WHEN p_base = 18 AND length(code) > 10 AND up_iso IN ('UY') THEN substring(code,1,10)
| |
| ELSE (CASE WHEN p_base=18 THEN code ELSE code16h END)
| |
| END AS code_tru,
| |
| | |
| -- flag
| |
| CASE
| |
| WHEN p_base <> 18 AND length(code16h) > 12 AND up_iso IN ('BR') THEN TRUE
| |
| WHEN p_base <> 18 AND length(code16h) > 11 AND up_iso IN ('EC','CO','UY') THEN TRUE
| |
| WHEN p_base <> 18 AND length(code16h) > 10 AND up_iso IN ('CM') THEN TRUE
| |
| WHEN p_base = 18 AND length(code) > 11 AND up_iso IN ('BR') THEN TRUE
| |
| WHEN p_base = 18 AND length(code) > 10 AND up_iso IN ('UY') THEN TRUE
| |
| ELSE NULL
| |
| END AS truncated_code,
| |
| | |
| -- vbit code16h
| |
| CASE
| |
| WHEN length(code16h) > 12 AND up_iso IN ('BR') THEN natcod.baseh_to_vbit(substring(code16h,1,12),16)
| |
| WHEN length(code16h) > 11 AND up_iso IN ('EC','CO','UY') THEN natcod.baseh_to_vbit(substring(code16h,1,11),16)
| |
| WHEN length(code16h) > 10 AND up_iso IN ('CM') THEN natcod.baseh_to_vbit(substring(code16h,1,10),16)
| |
| ELSE natcod.baseh_to_vbit(code16h,16)
| |
| END AS codebits,
| |
| | |
| code, up_iso
| |
| | |
| FROM
| |
| (
| |
| SELECT code, upper(p_iso) AS up_iso,
| |
| CASE
| |
| WHEN p_base = 18 THEN osmc.decode_16h1c(code,upper(p_iso))
| |
| ELSE code
| |
| END AS code16h
| |
| FROM regexp_split_to_table(lower(p_code),',') code
| |
| ) u
| |
| ) c,
| |
| LATERAL
| |
| (
| |
| SELECT ggeohash.draw_cell_bybox(ggeohash.decode_box2(osmc.vbit_withoutL0(codebits,c.up_iso),bbox, CASE WHEN c.up_iso='EC' THEN TRUE ELSE FALSE END),false,ST_SRID(geom)) AS geom
| |
| FROM osmc.coverage
| |
| WHERE is_country IS TRUE AND isolabel_ext = c.up_iso -- cobertura nacional apenas
| |
| AND
| |
| CASE
| |
| WHEN up_iso IN ('CO','CM') THEN ( ( osmc.extract_L0bits(cbits,'CO') # codebits::bit(4) ) = 0::bit(4) ) -- 1 dígito base16h
| |
| ELSE ( ( osmc.extract_L0bits(cbits,up_iso) # codebits::bit(8) ) = 0::bit(8) ) -- 2 dígitos base16h
| |
| END
| |
| ) v
| |
| WHERE
| |
| CASE WHEN up_iso = 'UY' THEN c.code16h NOT IN ('0eg','10g','12g','00r','12r','0eh','05q','11q') ELSE TRUE END
| |
| $f$;
| |
| | |
| DROP FUNCTION if exists osmc.L0cover_br_geoms
| |
| ;
| |
| CREATE FUNCTION osmc.L0cover_br_geoms()
| |
| RETURNS TABLE (
| |
| gid int,
| |
| cbits varbit,
| |
| b16_label text,
| |
| prefix text,
| |
| isolabel_ext text,
| |
| bbox integer[],
| |
| is_contained boolean,
| |
| geom geometry,
| |
| geom_cell geometry,
| |
| geom_srid4326 geometry
| |
| ) language SQL AS $f$
| |
| SELECT row_number() over() as gid,
| |
| jurisd_base_id::bit(10) || (natcod.baseh_to_vbit(prefix,16)) as cbits,
| |
| CASE WHEN left(prefix,1)='0' THEN substring(prefix,2,1) ELSE prefix END as b16_label,
| |
| prefix,-- natcod.vbit_to_baseh(substring(cbits,11),16) as b16_label,
| |
| 'BR', bbox,
| |
| CASE WHEN ST_ContainsProperly(geom_country,geom_cell) IS FALSE THEN TRUE ELSE FALSE END,
| |
| geom,
| |
| geom_cell,
| |
| ST_Transform(geom,4326)
| |
| FROM (
| |
| SELECT 76 AS jurisd_base_id, prefix, bbox,geom_country,
| |
| ST_Intersection(ggeohash.draw_cell_bybox(bbox,false,952019),geom_country) AS geom,
| |
| ggeohash.draw_cell_bybox(bbox,false,952019) AS geom_cell
| |
| FROM unnest(
| |
| '{00,01,02,03,04,05,06,07,08,09,0a,0b,0c,0d,0e,0f,10,11}'::text[],
| |
| array[20,21,22,23,15,16,17,18,19,11,12,13,6,7,8,2,24,14]
| |
| ) t(prefix,quadrant),
| |
| LATERAL (
| |
| SELECT osmc.ij_to_bbox(quadrant%5,quadrant/5,2715000,6727000,1048576)
| |
| ) u(bbox),
| |
| LATERAL (
| |
| SELECT ST_Transform(geom,952019)
| |
| FROM optim.vw01full_jurisdiction_geom g
| |
| WHERE g.isolabel_ext = 'BR' AND jurisd_base_id = 76
| |
| ) r(geom_country)
| |
| WHERE quadrant IS NOT NULL
| |
| ) y
| |
| | |
| UNION ALL
| |
| | |
| SELECT 18+(row_number() over()) as gid,
| |
| 76::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits,
| |
| code as b16_label,
| |
| '0'||lower(code) as prefix,
| |
| 'BR', NULL as bbox, false as is_contained,
| |
| NULL as geom, -- st_intersect(geom_BR,geom_cell)
| |
| geom as geom_cell, geom4326
| |
| FROM osmc.decode_scientific_absolute_geoms('fT,fY,fP,fN','BR',18)
| |
| | |
| ORDER BY 1
| |
| $f$;
| |
| COMMENT ON FUNCTION osmc.L0cover_br_geoms()
| |
| IS 'L0cover BR from configs ingest (gid<=18) into osmc.coverage using osmc.l0cover_upsert().'
| |
| ;
| |
| </syntaxhighlight>
| |
|
| |
|
| ===Recorte L1 scientifica === | | ===Recorte L1 scientifica === |