...
| Code Block | ||
|---|---|---|
| ||
drop table if exists skfab_climate_24.smhi_rutnat_absolute; CREATE TABLE skfab_climate_24.smhi_rutnat_absolute ( id serial primary key, geom public.geometry(multipolygon, 3006) NULL, dd_r26_pe1 float8 NULL, dd_r26_pe2 float8 NULL, dd_r26_pe3 float8 NULL, dd_r45_pe1 float8 NULL, dd_r45_pe2 float8 NULL, dd_r45_pe3 float8 NULL, dd_r85_pe1 float8 NULL, dd_r85_pe2 float8 NULL, dd_r85_pe3 float8 NULL, fd_r26_pe1 float8 NULL, fd_r26_pe2 float8 NULL, fd_r26_pe3 float8 NULL, fd_r45_pe1 float8 NULL, fd_r45_pe2 float8 NULL, fd_r45_pe3 float8 NULL, fd_r85_pe1 float8 NULL, fd_r85_pe2 float8 NULL, fd_r85_pe3 float8 NULL, gsl_r26_pe1 float8 NULL, gsl_r26_pe2 float8 NULL, gsl_r26_pe3 float8 NULL, gsl_r45_pe1 float8 NULL, gsl_r45_pe2 float8 NULL, gsl_r45_pe3 float8 NULL, gsl_r85_pe1 float8 NULL, gsl_r85_pe2 float8 NULL, gsl_r85_pe3 float8 NULL, pr_r26_pe1 float8 NULL, pr_r26_pe2 float8 NULL, pr_r26_pe3 float8 NULL, pr_r45_pe1 float8 NULL, pr_r45_pe2 float8 NULL, pr_r45_pe3 float8 NULL, pr_r85_pe1 float8 NULL, pr_r85_pe2 float8 NULL, pr_r85_pe3 float8 NULL, r10mm_r26_pe1 float8 NULL, r10mm_r26_pe2 float8 NULL, r10mm_r26_pe3 float8 NULL, r10mm_r45_pe1 float8 NULL, r10mm_r45_pe2 float8 NULL, r10mm_r45_pe3 float8 NULL, r10mm_r85_pe1 float8 NULL, r10mm_r85_pe2 float8 NULL, r10mm_r85_pe3 float8 NULL, r20mm_r26_pe1 float8 NULL, r20mm_r26_pe2 float8 NULL, r20mm_r26_pe3 float8 NULL, r20mm_r45_pe1 float8 NULL, r20mm_r45_pe2 float8 NULL, r20mm_r45_pe3 float8 NULL, r20mm_r85_pe1 float8 NULL, r20mm_r85_pe2 float8 NULL, r20mm_r85_pe3 float8 NULL, tas_r26_pe1 float8 NULL, tas_r26_pe2 float8 NULL, tas_r26_pe3 float8 NULL, tas_r45_pe1 float8 NULL, tas_r45_pe2 float8 NULL, tas_r45_pe3 float8 NULL, tas_r85_pe1 float8 NULL, tas_r85_pe2 float8 NULL, tas_r85_pe3 float8 NULL ); drop index if exists skfab_climate_24.smhi_rutnat_absolute_id_idx; CREATE INDEX smhi_rutnat_absolute_idx ON skfab_climate_24.smhi_rutnat_absolute USING GIST (geom); -- let frost dasy fd be the crateinon of the grid INSERT INTO skfab_climate_24.smhi_rutnat_absolute (id, fd_r26_pe1, fd_r26_pe2, fd_r26_pe3, fd_r45_pe1, fd_r45_pe2, fd_r45_pe3, fd_r85_pe1, fd_r85_pe2, fd_r85_pe3, geom) (select fd_r26_pe1.id as id, fd_r26_pe1.fd as fd_r26_pe1, fd_r26_pe2.fd as fd_r26_pe2, fd_r26_pe3.fd as fd_r26_pe3, fd_r45_pe1.fd as fd_r45_pe1, fd_r45_pe2.fd as fd_r45_pe2, fd_r45_pe3.fd as fd_r45_pe3, fd_r85_pe1.fd as fd_r85_pe1, fd_r85_pe2.fd as fd_r85_pe2, fd_r85_pe3.fd as fd_r85_pe3, fd_r26_pe1.geom from smhi."fd_ensmean_rcp26_ANN_30y_2011_2040.shp" fd_r26_pe1 join smhi."fd_ensmean_rcp26_ANN_30y_2041_2070.shp" fd_r26_pe2 on ST_Contains( fd_r26_pe1.geom, fd_r26_pe2.geom) join smhi."fd_ensmean_rcp26_ANN_30y_2071_2100.shp" fd_r26_pe3 on ST_Contains( fd_r26_pe1.geom, fd_r26_pe3.geom) join smhi."fd_ensmean_rcp45_ANN_30y_2011_2040.shp" fd_r45_pe1 on ST_Contains( fd_r26_pe1.geom, fd_r45_pe1.geom) join smhi."fd_ensmean_rcp45_ANN_30y_2041_2070.shp" fd_r45_pe2 on ST_Contains( fd_r26_pe1.geom, fd_r45_pe2.geom) join smhi."fd_ensmean_rcp45_ANN_30y_2071_2100.shp" fd_r45_pe3 on ST_Contains( fd_r26_pe1.geom, fd_r45_pe3.geom) join smhi."fd_ensmean_rcp85_ANN_30y_2011_2040.shp" fd_r85_pe1 on ST_Contains( fd_r26_pe1.geom, fd_r85_pe1.geom) join smhi."fd_ensmean_rcp85_ANN_30y_2041_2070.shp" fd_r85_pe2 on ST_Contains( fd_r26_pe1.geom, fd_r85_pe2.geom) join smhi."fd_ensmean_rcp85_ANN_30y_2071_2100.shp" fd_r85_pe3 on ST_Contains( fd_r26_pe1.geom, fd_r85_pe3.geom) ); --update tas dygnsmedeltemperatur per år UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r26_pe1=b.tas from smhi."tas_ensmean_rcp26_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r26_pe2=b.tas from smhi."tas_ensmean_rcp26_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r26_pe3=b.tas from smhi."tas_ensmean_rcp26_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r45_pe1=b.tas from smhi."tas_ensmean_rcp45_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r45_pe2=b.tas from smhi."tas_ensmean_rcp45_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r45_pe3=b.tas from smhi."tas_ensmean_rcp45_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r85_pe1=b.tas from smhi."tas_ensmean_rcp85_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r85_pe2=b.tas from smhi."tas_ensmean_rcp85_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET tas_r85_pe3=b.tas from smhi."tas_ensmean_rcp85_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); --pr nederbord dygn per år UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r26_pe1=b.pr from smhi."pr_ensmean_rcp26_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r26_pe2=b.pr from smhi."pr_ensmean_rcp26_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r26_pe3=b.pr from smhi."pr_ensmean_rcp26_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r45_pe1=b.pr from smhi."pr_ensmean_rcp45_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r45_pe2=b.pr from smhi."pr_ensmean_rcp45_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r45_pe3=b.pr from smhi."pr_ensmean_rcp45_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r85_pe1=b.pr from smhi."pr_ensmean_rcp85_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r85_pe2=b.pr from smhi."pr_ensmean_rcp85_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET pr_r85_pe3=b.pr from smhi."pr_ensmean_rcp85_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); --r10mm kraftig nederbörd dygn per år UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r26_pe1=b.r10mm from smhi."r10mm_ensmean_rcp26_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r26_pe2=b.r10mm from smhi."r10mm_ensmean_rcp26_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r26_pe3=b.r10mm from smhi."r10mm_ensmean_rcp26_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r45_pe1=b.r10mm from smhi."r10mm_ensmean_rcp45_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r45_pe2=b.r10mm from smhi."r10mm_ensmean_rcp45_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r45_pe3=b.r10mm from smhi."r10mm_ensmean_rcp45_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r85_pe1=b.r10mm from smhi."r10mm_ensmean_rcp85_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r85_pe2=b.r10mm from smhi."r10mm_ensmean_rcp85_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r10mm_r85_pe3=b.r10mm from smhi."r10mm_ensmean_rcp85_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); --r20mm xtremt kraftig nederbörd dygn per år UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r26_pe1=b.r20mm from smhi."r20mm_ensmean_rcp26_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r26_pe2=b.r20mm from smhi."r20mm_ensmean_rcp26_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r26_pe3=b.r20mm from smhi."r20mm_ensmean_rcp26_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r45_pe1=b.r20mm from smhi."r20mm_ensmean_rcp45_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r45_pe2=b.r20mm from smhi."r20mm_ensmean_rcp45_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r45_pe3=b.r20mm from smhi."r20mm_ensmean_rcp45_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r85_pe1=b.r20mm from smhi."r20mm_ensmean_rcp85_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r85_pe2=b.r20mm from smhi."r20mm_ensmean_rcp85_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET r20mm_r85_pe3=b.r20mm from smhi."r20mm_ensmean_rcp85_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); --gsl vegitation dygn per år UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r26_pe1=b.gsl from smhi."gsl_ensmean_rcp26_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r26_pe2=b.gsl from smhi."gsl_ensmean_rcp26_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r26_pe3=b.gsl from smhi."gsl_ensmean_rcp26_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r45_pe1=b.gsl from smhi."gsl_ensmean_rcp45_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r45_pe2=b.gsl from smhi."gsl_ensmean_rcp45_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r45_pe3=b.gsl from smhi."gsl_ensmean_rcp45_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r85_pe1=b.gsl from smhi."gsl_ensmean_rcp85_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r85_pe2=b.gsl from smhi."gsl_ensmean_rcp85_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET gsl_r85_pe3=b.gsl from smhi."gsl_ensmean_rcp85_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); --dd torra dygn per år UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r26_pe1=b.dd from smhi."dd_ensmean_rcp26_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r26_pe2=b.dd from smhi."dd_ensmean_rcp26_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r26_pe3=b.dd from smhi."dd_ensmean_rcp26_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r45_pe1=b.dd from smhi."dd_ensmean_rcp45_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r45_pe2=b.dd from smhi."dd_ensmean_rcp45_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r45_pe3=b.dd from smhi."dd_ensmean_rcp45_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r85_pe1=b.dd from smhi."dd_ensmean_rcp85_ANN_30y_2011_2040.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r85_pe2=b.dd from smhi."dd_ensmean_rcp85_ANN_30y_2041_2070.shp" b where ST_Contains( a.geom, b.geom); UPDATE skfab_climate_24.smhi_rutnat_absolute a SET dd_r85_pe3=b.dd from smhi."dd_ensmean_rcp85_ANN_30y_2071_2100.shp" b where ST_Contains( a.geom, b.geom); VACUUM FULL analyze skfab_climate_24.smhi_rutnat_absolute; |
Innan frågan körs är det lämpligt att indexera de ingående tabellerna och även att ta bort index. Även här används kolumnnamnet så att det ska gå enkelt att göra systematisk omdöpning för övriga teman.
När tabellen smhi_rutor_absolute finns klar kan en ny fråga skapas för att användas i Geoserver för att ska kartografi. För att förenkla konfigurationen i Weave och kartografin i Geoserver så skapar vi en vy för varje tema. Vyn innehåller både absoluta och relativa (procen) värden.
Därmed är allt klart för Weave.
| Code Block | ||
|---|---|---|
| ||
ITHI create or replace view skfab_climate_24.view_smhi_rutnat_absolute_fd as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r85_pe3)) as g_frostdfd, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r85_pe3)) as l_frostdfd, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY frostdfd_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY frostdfd_r85_pe3)) as d_frostdfd from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, ((frostd from values select r.id, 'Frost dygn per år', fd_r26_pe1 as rpc26_1_abs, fd_r26_pe2 as rpc26_2_abs, fd_r26_pe3 as rpc26_3_abs, fd_r45_pe1 as rpc45_1_abs, fd_r45_pe2 as rpc45_2_abs, fd_r45_pe3 as rpc45_3_abs, fd_r85_pe1 as rpc85_1_abs, fd_r85_pe2 as rpc85_2_abs, fd_r85_pe2 as rpc85_3_abs, fd_r85_pe3, ((fd_r26_pe1-mms.l_fd)*100/mms.d_fd)::integer as rpc26_1_rel, ((fd_r26_pe2-mms.l_fd)*100/mms.d_fd)::integer as rpc26_2_rel, ((fd_r26_pe3-mms.l_fd)*100/mms.d_fd)::integer as rpc26_3_rel, ((fd_r45_pe1-mms.l_fd)*100/mms.d_fd)::integer as rpc45_1_rel, ((fd_r45_pe2-mms.l_fd)*100/mms.d_fd)::integer as rpc45_2_rel, ((fd_r45_pe3-mms.l_fd)*100/mms.d_fd)::integer as rpc45_3_rel, ((fd_r85_pe1-mms.l_fd)*100/mms.d_fd)::integer as rpc85_1_rel, ((fd_r85_pe2-mms.l_fd)*100/mms.d_fd)::integer as rpc85_2_rel, ((fd_r85_pe3-mms.l_fd)*100/mms.d_fd)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_r20mm as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) as g_r20mm, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) as l_r20mm, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r20mm_r85_pe3)) as d_r20mm from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Extrem nederbord dygn per år', r20mm_r26_pe1 as rpc26_1_abs, r20mm_r26_pe2 as rpc26_2_abs, r20mm_r26_pe3 as rpc26_3_abs, r20mm_r45_pe1 as rpc45_1_abs, r20mm_r45_pe2 as rpc45_2_abs, r20mm_r45_pe3 as rpc45_3_abs, r20mm_r85_pe1 as rpc85_1_abs, r20mm_r85_pe2 as rpc85_2_abs, r20mm_r85_pe2 as rpc85_3_abs, r20mm_r85_pe3, ((r20mm_r26_pe1-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc26_1_rel, ((r20mm_r26_pe2-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc26_2_rel, ((r20mm_r26_pe3-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc26_3_rel, ((r20mm_r45_pe1-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc45_1_rel, ((r20mm_r45_pe2-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc45_2_rel, ((r20mm_r45_pe3-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc45_3_rel, ((r20mm_r85_pe1-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc85_1_rel, ((r20mm_r85_pe2-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc85_2_rel, ((r20mm_r85_pe3-mms.l_r20mm)*100/mms.d_r20mm)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_r10mm as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) as g_r10mm, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) as l_r10mm, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY r10mm_r85_pe3)) as d_r10mm from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Kraftig nederbörd dygn per år', r10mm_r26_pe1 as rpc26_1_abs, r10mm_r26_pe2 as rpc26_2_abs, r10mm_r26_pe3 as rpc26_3_abs, r10mm_r45_pe1 as rpc45_1_abs, r10mm_r45_pe2 as rpc45_2_abs, r10mm_r45_pe3 as rpc45_3_abs, r10mm_r85_pe1 as rpc85_1_abs, r10mm_r85_pe2 as rpc85_2_abs, r10mm_r85_pe2 as rpc85_3_abs, r10mm_r85_pe3, ((r10mm_r26_pe1-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc26_1_rel, ((r10mm_r26_pe2-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc26_2_rel, ((r10mm_r26_pe3-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc26_3_rel, ((r10mm_r45_pe1-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc45_1_rel, ((r10mm_r45_pe2-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc45_2_rel, ((r10mm_r45_pe3-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc45_3_rel, ((r10mm_r85_pe1-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc85_1_rel, ((r10mm_r85_pe2-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc85_2_rel, ((r10mm_r85_pe3-mms.l_r10mm)*100/mms.d_r10mm)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_pr as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) as g_pr, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as l_pr, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as d_pr from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Nederbord dygn per år', pr_r26_pe1 as rpc26_1_abs, pr_r26_pe2 as rpc26_2_abs, pr_r26_pe3 as rpc26_3_abs, pr_r45_pe1 as rpc45_1_abs, pr_r45_pe2 as rpc45_2_abs, pr_r45_pe3 as rpc45_3_abs, pr_r85_pe1 as rpc85_1_abs, pr_r85_pe2 as rpc85_2_abs, pr_r85_pe2 as rpc85_3_abs, pr_r85_pe3, ((pr_r26_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc26_1_rel, ((pr_r26_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc26_2_rel, ((pr_r26_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc26_3_rel, ((pr_r45_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc45_1_rel, ((pr_r45_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc45_2_rel, ((pr_r45_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc45_3_rel, ((pr_r85_pe1-mms.l_pr)*100/mms.d_pr)::integer as rpc85_1_rel, ((pr_r85_pe2-mms.l_pr)*100/mms.d_pr)::integer as rpc85_2_rel, ((pr_r85_pe3-mms.l_pr)*100/mms.d_pr)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_tas as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe3)) as g_tas, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe3)) as l_tas, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY tas_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY tas_r85_pe3)) as d_tas from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Dygnsmedeltemperatur per år', tas_r26_pe1 as rpc26_1_abs, tas_r26_pe2 as rpc26_2_abs, tas_r26_pe3 as rpc26_3_abs, tas_r45_pe1 as rpc45_1_abs, tas_r45_pe2 as rpc45_2_abs, tas_r45_pe3 as rpc45_3_abs, tas_r85_pe1 as rpc85_1_abs, tas_r85_pe2 as rpc85_2_abs, tas_r85_pe2 as rpc85_3_abs, tas_r85_pe3, ((tas_r26_pe1-mms.l_tas)*100/mms.d_tas)::integer as rpc26_1_rel, ((tas_r26_pe2-mms.l_tas)*100/mms.d_tas)::integer as rpc26_2_rel, ((tas_r26_pe3-mms.l_tas)*100/mms.d_tas)::integer as rpc26_3_rel, ((tas_r45_pe1-mms.l_tas)*100/mms.d_tas)::integer as rpc45_1_rel, ((tas_r45_pe2-mms.l_tas)*100/mms.d_tas)::integer as rpc45_2_rel, ((tas_r45_pe3-mms.l_tas)*100/mms.d_tas)::integer as rpc45_3_rel, ((tas_r85_pe1-mms.l_tas)*100/mms.d_tas)::integer as rpc85_1_rel, ((tas_r85_pe2-mms.l_tas)*100/mms.d_tas)::integer as rpc85_2_rel, ((tas_r85_pe3-mms.l_tas)*100/mms.d_tas)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_dd as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe3)) as g_dd, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe3)) as l_dd, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY dd_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY dd_r85_pe3)) as d_dd from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Torra dygn per år', dd_r26_pe1 as rpc26_1_abs, dd_r26_pe2 as rpc26_2_abs, dd_r26_pe3 as rpc26_3_abs, dd_r45_pe1 as rpc45_1_abs, dd_r45_pe2 as rpc45_2_abs, dd_r45_pe3 as rpc45_3_abs, dd_r85_pe1 as rpc85_1_abs, dd_r85_pe2 as rpc85_2_abs, dd_r85_pe2 as rpc85_3_abs, dd_r85_pe3, ((dd_r26_pe1-mms.l_dd)*100/mms.d_dd)::integer as rpc26_1_rel, ((dd_r26_pe2-mms.l_dd)*100/mms.d_dd)::integer as rpc26_2_rel, ((dd_r26_pe3-mms.l_dd)*100/mms.d_dd)::integer as rpc26_3_rel, ((dd_r45_pe1-mms.l_dd)*100/mms.d_dd)::integer as rpc45_1_rel, ((dd_r45_pe2-mms.l_dd)*100/mms.d_dd)::integer as rpc45_2_rel, ((dd_r45_pe3-mms.l_dd)*100/mms.d_dd)::integer as rpc45_3_rel, ((dd_r85_pe1-mms.l_dd)*100/mms.d_dd)::integer as rpc85_1_rel, ((dd_r85_pe2-mms.l_dd)*100/mms.d_dd)::integer as rpc85_2_rel, ((dd_r85_pe3-mms.l_dd)*100/mms.d_dd)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_gsl as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe3)) as g_gsl, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe3)) as l_gsl, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY gsl_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY gsl_r85_pe3)) as d_gsl from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Vegetationsperioden dygn per år', gsl_r26_pe1 as rpc26_1_abs, gsl_r26_pe2 as rpc26_2_abs, gsl_r26_pe3 as rpc26_3_abs, gsl_r45_pe1 as rpc45_1_abs, gsl_r45_pe2 as rpc45_2_abs, gsl_r45_pe3 as rpc45_3_abs, gsl_r85_pe1 as rpc85_1_abs, gsl_r85_pe2 as rpc85_2_abs, gsl_r85_pe2 as rpc85_3_abs, gsl_r85_pe3, ((gsl_r26_pe1-mms.l_gsl)*100/mms.d_gsl)::integer as rpc26_1_rel, ((gsl_r26_pe2-mms.l_gsl)*100/mms.d_gsl)::integer as rpc26_2_rel, ((gsl_r26_pe3-mms.l_gsl)*100/mms.d_gsl)::integer as rpc26_3_rel, ((gsl_r45_pe1-mms.l_gsl)*100/mms.d_gsl)::integer as rpc45_1_rel, ((gsl_r45_pe2-mms.l_gsl)*100/mms.d_gsl)::integer as rpc45_2_rel, ((gsl_r45_pe3-mms.l_gsl)*100/mms.d_gsl)::integer as rpc45_3_rel, ((gsl_r85_pe1-mms.l_gsl)*100/mms.d_gsl)::integer as rpc85_1_rel, ((gsl_r85_pe2-mms.l_gsl)*100/mms.d_gsl)::integer as rpc85_2_rel, ((gsl_r85_pe3-mms.l_gsl)*100/mms.d_gsl)::integer as rpc85_3_rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); create or replace view skfab_climate_24.view_smhi_rutnat_absolute_pr as ( WITH mms AS ( select greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) as g_pr, least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as l_pr, greatest ( PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY pr_r85_pe3)) - least ( PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r26_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r45_pe3), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe1), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe2), PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY pr_r85_pe3)) as d_pr from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute ), pro as ( -- calculate the procentage (0-100) from values select r.id, 'Medelnederbörd nedbord_ per månad', pr_r26_pe1 as rpc26_1_abs, pr_r26_pe2 as rpc26_2_abs, pr_r26_pe3 as rpc26_3_abs, pr_r45_pe1 as rpc45_1_abs, pr_r45_pe2 as rpc45_2_abs, pr_r45_pe3 as rpc45_3_abs, pr_r85_pe1 as rpc85_1_abs, pr_r85_pe2 as rpc85_2_abs, pr_r85_pe2 as rpc85_3_abs, pr_r85_pe3, ((pr_r26_pe1-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc26_r261_pe1rel, ((frostdpr_r26_pe2-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc26_r262_pe2rel, ((frostdpr_r26_pe3-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc26_r263_pe3rel, ((frostdpr_r45_pe1-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc45_r451_pe1rel, ((frostdpr_r45_pe2-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc45_r452_pe2rel, ((frostdpr_r45_pe3-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc45_r453_pe3rel, ((frostdpr_r85_pe1-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc85_r851_pe1rel, ((frostdpr_r85_pe2-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc85_r852_pe2rel, ((frostdpr_r85_pe3-mms.l_frostdpr)*100/mms.d_frostdpr)::integer as frostdrpc85_r853_pe3rel from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r , mms ) -- view the result select h.*, r.geom from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute r join pro h on h.id=r.id ); |