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

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

Edição das 08h54min de 3 de junho de 2024

3 714 bytes adicionados , 3 junho

→‎Sem recorte: dois digitos L2

Peter

2 391

edições

@@ Linha 273: / Linha 273: @@
 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 ===
+Com os geradores abaixo obtemos a cobetrura de células com ~524 km de lado, que permite a comparação com as células originais do IBGE, conforme [[osmc:BR#Grade_de_cobertura]].
+<syntaxhighlight lang="sql">
+-- permanece "f*" por ser menor que hmrv:
+-- osmc.decode_scientific_absolute_geoms('fT,fY,fP,fN', 'BR', 18)
+-- geração da sequencia completa das demais coberturas candidatas:
+select array_agg(i||j) from unnest('{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e}'::text[]) t1(i), unnest('{h,m,r,v}'::text[]) t2(j);
+-- Visualização no QGIS:
+drop table  if exists  tmp_br_cover_L1_scientific
+;
+create table tmp_br_cover_L1_scientific AS
+select row_number() over() as gid, *
+from (
+	SELECT 76::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits,
+	code as b16_label,
+	'0'||lower(code)  as prefix, geom
+	FROM osmc.decode_scientific_absolute_geoms(
+	'0h,1h,2h,3h,4h,5h,6h,7h,8h,9h,ah,bh,ch,dh,eh,0m,1m,2m,3m,4m,5m,6m,7m,8m,9m,am,bm,cm,dm,em,0r,1r,2r,3r,4r,5r,6r,7r,8r,9r,ar,br,cr,dr,er,0v,1v,2v,3v,4v,5v,6v,7v,8v,9v,av,bv,cv,dv,ev'
+	,'BR',18)
+    where st_intersects(geom, st_transform((select geom from  optim.jurisdiction_geom where isolabel_ext='BR'),952019) )
+	UNION ALL
+	SELECT 76::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits,
+	code as b16_label,
+	'0'||lower(code)  as prefix, geom
+	FROM osmc.decode_scientific_absolute_geoms('fT,fY,fP,fN','BR',18)
+	order by 1
+) t3
+;
+</syntaxhighlight>
+===Recorte L2 scientifica ===
+Com os geradores abaixo obtemos a cobetrura de hexadecimais de 2 dígitos (células de ~262 km de lado).
+<syntaxhighlight lang="sql">
+-----
+-- Geração da cobertura de dois dígitos hexa:
+-- fe,fa,f6,f4,ff,fb,f7,f5  obtidos de:
+SELECT array_agg(natcod.vbit_to_str(substring(cbits||x,15),'16h')) as b16_labels
+FROM (
+	SELECT (18+(row_number() over()))*16 as gid, x,
+::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits
+	FROM osmc.decode_scientific_absolute_geoms('fT,fY,fP,fN', 'BR', 18),
+	     LATERAL unnest(array[b'0',b'1']) t1(x)
+) t2
+;
+-- geração da sequencia completa das demais coberturas candidatas:
+select array_agg(i||j) from unnest('{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e}'::text[]) t1(i), unnest('{0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f}'::text[]) t2(j);
+-- Visualização no QGIS:
+drop table  if exists  tmp_br_cover_L2_scientific
+;
+create table tmp_br_cover_L2_scientific AS
+select row_number() over() as gid, *
+from (
+	SELECT 76::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits,
+	code as b16_label,
+	'0'||lower(code)  as prefix, geom
+	FROM osmc.decode_scientific_absolute_geoms(
+	'00,01,02,03,04,05,06,07,08,09,0a,0b,0c,0d,0e,0f,10,11,12,13,14,15,16,17,18,19,1a,1b,1c,1d,1e,1f,20,21,22,23,24,25,26,27,28,29,2a,2b,2c,2d,2e,2f,30,31,32,33,34,35,36,37,38,39,3a,3b,3c,3d,3e,3f,40,41,42,43,44,45,46,47,48,49,4a,4b,4c,4d,4e,4f,50,51,52,53,54,55,56,57,58,59,5a,5b,5c,5d,5e,5f,60,61,62,63,64,65,66,67,68,69,6a,6b,6c,6d,6e,6f,70,71,72,73,74,75,76,77,78,79,7a,7b,7c,7d,7e,7f,80,81,82,83,84,85,86,87,88,89,8a,8b,8c,8d,8e,8f,90,91,92,93,94,95,96,97,98,99,9a,9b,9c,9d,9e,9f,a0,a1,a2,a3,a4,a5,a6,a7,a8,a9,aa,ab,ac,ad,ae,af,b0,b1,b2,b3,b4,b5,b6,b7,b8,b9,ba,bb,bc,bd,be,bf,c0,c1,c2,c3,c4,c5,c6,c7,c8,c9,ca,cb,cc,cd,ce,cf,d0,d1,d2,d3,d4,d5,d6,d7,d8,d9,da,db,dc,dd,de,df,e0,e1,e2,e3,e4,e5,e6,e7,e8,e9,ea,eb,ec,ed,ee,ef'
+	,'BR',18)
+    where st_intersects(geom, st_transform((select geom from  optim.jurisdiction_geom where isolabel_ext='BR'),952019) )
+	UNION ALL
+	SELECT 76::bit(10) || (natcod.baseh_to_vbit('0'||lower(code),16)) as cbits,
+	code as b16_label,
+	'0'||lower(code)  as prefix, geom
+	FROM osmc.decode_scientific_absolute_geoms('fe,fa,f6,f4,ff,fb,f7,f5','BR',18)
+	order by 1
+) t3
 ;
 </syntaxhighlight>