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

← Edição anterior

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

Edição das 23h00min de 5 de junho de 2024

1 191 bytes adicionados , 5 junho

report.v004_osmc_coverage_distrib

Peter

2 391

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 isolevel, 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) suffix,
+          cardinality(string_to_array(isolabel_ext,'-')) isolevel
     FROM osmc.coverage
-) t WHERE isolabel_ext LIKE 'CM-%-%'
+ ) t  -- WHERE status=1?
-group by 1,2
+ GROUP BY 1,2,3
-order by 1,2;
+ ORDER BY 1,2,3
+;
+DROP VIEW IF EXISTS report.v004_osmc_coverage_distrib CASCADE
+;
+CREATE VIEW report.v004_osmc_coverage_distrib AS
+ SELECT substring(isolabel_ext,1,2) as country, isolevel, cover_cells_level,
+      count(*) n_municipalities,
+      round(avg(cardinality(cover))) avg_n_cells
+ FROM report.v003_osmc_coverage_export
+ GROUP BY 1,2,3
+ ORDER BY 1,2,3
+;
+-- select isolabel_ext, cover_cells_level,overlay_cells_level from report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%' order by 2, 1;
+-- select cover_cells_level, n_municipalities, avg_n_cells from report.v004_osmc_coverage_distrib where country='CM' and isolevel=3;
+-- 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'').