osmc:CM/SQL: mudanças entre as edições

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

Edição das 19h30min de 5 de junho de 2024

634 bytes adicionados , 5 junho

m

v003_osmc_coverage_export

Peter

2 384

edições

@@ Linha 82: / Linha 82: @@
 <syntaxhighlight lang="sql">
-SELECT isolabel_ext, status,
+DROP VIEW IF EXISTS report.v003_osmc_coverage_export CASCADE
+;
+CREATE VIEW report.v003_osmc_coverage_export AS
+ SELECT isolabel_ext, status,
         array_agg(DISTINCT b16 ORDER BY b16) FILTER(WHERE not(is_overlay)) as cover,
-        array_agg(DISTINCT b16 ORDER BY b16) FILTER(WHERE is_overlay) as overlay
+        array_agg(DISTINCT b16 ORDER BY b16) FILTER(WHERE is_overlay) as overlay,
-FROM (
+       max(round(length(suffix)/2.0 - 2, 1)) FILTER(WHERE not(is_overlay)) as cover_cells_level,
-    SELECT *, natcod.vbit_to_baseh(substring(cbits,11),16) as b16
+       max(round(length(suffix)/2.0 - 2, 1)) FILTER(WHERE is_overlay) as overlay_cells_level
+ FROM (
+    SELECT *, natcod.vbit_to_baseh(substring(cbits,11),16) as b16,
+          substring(cbits,11) as suffix
     FROM osmc.coverage
-) t WHERE isolabel_ext LIKE 'CM-%-%'
+ ) t  -- WHERE status=1?
-group by 1,2
+ GROUP BY 1,2
-order by 1,2;
+ ORDER BY 1,2
+;
+-- select isolabel_ext, cover_cells_level,overlay_cells_level from report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%' order by 2, 1;
+-- COPY (SELECT isolabel_ext, status, cover, overlay FROM report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%') TO 'out.csv' CSV HEADER;
 </syntaxhighlight>
 A seguir apenas dois exemplos, [https://afa.codes/CM-CE-Yoko CM-CE-Yoko] (com ''overlay'' no centro urbano)  e [https://afa.codes/CM-NO-Dembo Dembo no Norte] (ex. "46B4" cai na área de ''overlay'').