2 391
edições
mSem resumo de edição |
(add img e SQL) |
||
Linha 126: | Linha 126: | ||
; | ; | ||
</syntaxhighlight>Resultado da view no QGIS: | </syntaxhighlight>Resultado da view no QGIS: | ||
[[Arquivo:Report v002.png|centro|semmoldura| | [[Arquivo:Report v002.png|centro|semmoldura|620px]] | ||
===Sem recorte=== | ===Sem recorte=== | ||
Pelo AFAcode ou sua representação binária (coluna ''cbits'' de ''osmc.coverage''), a célula JSON é obtida através de uma função pública, <code>api.osmcode_decode_scientific_absolute(codigo,'BR',18)</code>. Para fornecer a célula simples ela foi reescrita como <code>osmc.decode_scientific_absolute_geoms(codigo,'BR',18)</code> (retorna tabela PostGIS e portanto não vale como API). | |||
==Ver também == | [[Arquivo:BRgrid-L0-QGISv1.png|centro|semmoldura|620px]] | ||
<syntaxhighlight lang="sql"> | |||
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. | |||
) 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, | |||
ST_Transform_resilient(v.geom,4326,0.005) 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 19+(row_number() over()) as gid, | |||
76::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits, | |||
code as b16_label, 'x' 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 into osmc.coverage using osmc.l0cover_upsert().' | |||
; | |||
</syntaxhighlight> | |||
==Ver também== | |||
*[[osmc:Metodologia/Algoritmo SQL/Issues]] | *[[osmc:Metodologia/Algoritmo SQL/Issues]] | ||
edições