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

354 bytes adicionados , 5 junho

report.v004_osmc_coverage_distrib

Peter

2 384

edições

@@ Linha 85: / Linha 85: @@
 ;
 CREATE VIEW report.v003_osmc_coverage_export AS
-  SELECT isolabel_ext, status,
+  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,
@@ Linha 92: / Linha 92: @@
   FROM (
     SELECT *, natcod.vbit_to_baseh(substring(cbits,11),16) as b16,
-           substring(cbits,11) as suffix
+           substring(cbits,11) suffix,
+          cardinality(string_to_array(isolabel_ext,'-')) isolevel
     FROM osmc.coverage
   ) 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, count(*) n_municipios from (select isolabel_ext, cover_cells_level, cover from report.v003_osmc_coverage_export WHERE isolabel_ext LIKE 'CM-%-%') tt group by 1 order by 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;