Ir para o conteúdo

osmc:Metodologia/Algoritmo SQL: mudanças entre as edições

osmc:Metodologia/Algoritmo SQL (ver código-fonte)

Edição das 00h02min de 19 de junho de 2024

5 475 bytes removidos ,  19 junho
m
→‎Gerando a grade L0 de cobertura do país
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 ===
Peter
2 384

edições

Disponível em “https://wiki.addressforall.org/doc/Especial:MobileDiff/4041”