Content Comparison

...

Info

Varje tema består av nio klumner, tre scenarier och tre perioder:

Utsläpps-scenarier

rpc2.6
rpc4.5
rpc8.5

Perioder

2011-2040
2041-2070
2071-2100

Teman:

Torra dygn dd
Frostdygn fd
Vegitationsdygn gsl
Nederbörd dygn
Nedbörd mmNederbord dygn per år pr
Kraftig nederbörd dygn r10mm
Extrem Nederbördnederbörd r20mm
TempraturDygnsmedeltempratur tas

Ny laddning av klimatdata med absoluta värden

...

Starta QGIS med kartföntret inställt i 3006. Markera alla de nio filerna för ett tema och dra dem in frpn filhanterare till kafrån filhanterare till lagerlistan. QGIS tar alla .zip filer och packar upp dem till lager från Shape-filer. Låt namnen vara som filerna.

Flytta lagren till Posgresdatabasen från QGIS

Se till att Postgres-databasen är öppen. Skapa ett temporärt schema t.ex. smhi

Markera alla de nio lagren i QGIS och dra dem upp till schema-namnet i Postgres. Det blir tyst en lång stund men processen pågår att flytta lagren. Efter en lång stund meddelas att det är klart.

Fortsätt med alla sju teman.

För att snabba upp arbetet med indata så skapar vi index för dessa

Code Block

language	sql

--index
drop INDEX if exists smhi.fd_r26_pe1_idx;  
drop INDEX if exists smhi.fd_r26_pe2_idx; 
drop INDEX if exists smhi.fd_r26_pe3_idx;  
drop INDEX if exists smhi.fd_r45_pe1_idx;  
drop INDEX if exists smhi.fd_r45_pe2_idx;  
drop INDEX if exists smhi.fd_r45_pe3_idx;  
drop INDEX if exists smhi.fd_r85_pe1_idx; 
drop INDEX if exists smhi.fd_r85_pe2_idx;  
drop INDEX if exists smhi.fd_r85_pe3_idx;
--tempratur
drop INDEX if exists smhi.pr_r26_pe1_idx;  
drop INDEX if exists smhi.pr_r26_pe2_idx; 
drop INDEX if exists smhi.pr_r26_pe3_idx;  
drop INDEX if exists smhi.pr_r45_pe1_idx;  
drop INDEX if exists smhi.pr_r45_pe2_idx;  
drop INDEX if exists smhi.pr_r45_pe3_idx;  
drop INDEX if exists smhi.pr_r85_pe1_idx; 
drop INDEX if exists smhi.pr_r85_pe2_idx;  
drop INDEX if exists smhi.pr_r85_pe3_idx;
--nederbörd
drop INDEX if exists smhi.r10mm_r26_pe1_idx;  
drop INDEX if exists smhi.r10mm_r26_pe2_idx; 
drop INDEX if exists smhi.r10mm_r26_pe3_idx;  
drop INDEX if exists smhi.r10mm_r45_pe1_idx;  
drop INDEX if exists smhi.r10mm_r45_pe2_idx;  
drop INDEX if exists smhi.r10mm_r45_pe3_idx;  
drop INDEX if exists smhi.r10mm_r85_pe1_idx; 
drop INDEX if exists smhi.r10mm_r85_pe2_idx;  
drop INDEX if exists smhi.r10mm_r85_pe3_idx;
--kfr
drop INDEX if exists smhi.r20mm_r26_pe1_idx;  
drop INDEX if exists smhi.r20mm_r26_pe2_idx; 
drop INDEX if exists smhi.r20mm_r26_pe3_idx;  
drop INDEX if exists smhi.r20mm_r45_pe1_idx;  
drop INDEX if exists smhi.r20mm_r45_pe2_idx;  
drop INDEX if exists smhi.r20mm_r45_pe3_idx;  
drop INDEX if exists smhi.r20mm_r85_pe1_idx; 
drop INDEX if exists smhi.r20mm_r85_pe2_idx;  
drop INDEX if exists smhi.r20mm_r85_pe3_idx;
--xtr
drop INDEX if exists smhi.xtr_r26_pe1_idx;  
drop INDEX if exists smhi.xtr_r26_pe2_idx; 
drop INDEX if exists smhi.xtr_r26_pe3_idx;  
drop INDEX if exists smhi.xtr_r45_pe1_idx;  
drop INDEX if exists smhi.xtr_r45_pe2_idx;  
drop INDEX if exists smhi.xtr_r45_pe3_idx;  
drop INDEX if exists smhi.xtr_r85_pe1_idx; 
drop INDEX if exists smhi.xtr_r85_pe2_idx;  
drop INDEX if exists smhi.xtr_r85_pe3_idx;
--dd
drop INDEX if exists smhi.dd_r26_pe1_idx;  
drop INDEX if exists smhi.dd_r26_pe2_idx; 
drop INDEX if exists smhi.dd_r26_pe3_idx;  
drop INDEX if exists smhi.dd_r45_pe1_idx;  
drop INDEX if exists smhi.dd_r45_pe2_idx;  
drop INDEX if exists smhi.dd_r45_pe3_idx;  
drop INDEX if exists smhi.dd_r85_pe1_idx; 
drop INDEX if exists smhi.dd_r85_pe2_idx;  
drop INDEX if exists smhi.dd_r85_pe3_idx;
--vegp
drop INDEX if exists smhi.gsl_r26_pe1_idx;  
drop INDEX if exists smhi.gsl_r26_pe2_idx; 
drop INDEX if exists smhi.gsl_r26_pe3_idx;  
drop INDEX if exists smhi.gsl_r45_pe1_idx;  
drop INDEX if exists smhi.gsl_r45_pe2_idx;  
drop INDEX if exists smhi.gsl_r45_pe3_idx;  
drop INDEX if exists smhi.gsl_r85_pe1_idx; 
drop INDEX if exists smhi.gsl_r85_pe2_idx;  
drop INDEX if exists smhi.gsl_r85_pe3_idx;
--vegp
drop INDEX if exists smhi.tas_r26_pe1_idx;  
drop INDEX if exists smhi.tas_r26_pe2_idx; 
drop INDEX if exists smhi.tas_r26_pe3_idx;  
drop INDEX if exists smhi.tas_r45_pe1_idx;  
drop INDEX if exists smhi.tas_r45_pe2_idx;  
drop INDEX if exists smhi.tas_r45_pe3_idx;  
drop INDEX if exists smhi.tas_r85_pe1_idx; 
drop INDEX if exists smhi.tas_r85_pe2_idx;  
drop INDEX if exists smhi.tas_r85_pe3_idx;

-- fd frostdygn
CREATE INDEX fd_r26_pe1_idx  ON smhi."fd_ensmean_rcp26_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX fd_r26_pe2_idx  ON smhi."fd_ensmean_rcp26_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX fd_r26_pe3_idx  ON smhi."fd_ensmean_rcp26_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX fd_r45_pe1_idx  ON smhi."fd_ensmean_rcp45_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX fd_r45_pe2_idx  ON smhi."fd_ensmean_rcp45_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX fd_r45_pe3_idx  ON smhi."fd_ensmean_rcp45_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX fd_r85_pe1_idx  ON smhi."fd_ensmean_rcp85_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX fd_r85_pe2_idx  ON smhi."fd_ensmean_rcp85_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX fd_r85_pe3_idx  ON smhi."fd_ensmean_rcp85_ANN_30y_2071_2100.shp"  USING GIST (geom);
-- pr nederbord dygn per år
CREATE INDEX pr_r26_pe1_idx  ON smhi."pr_ensmean_rcp26_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX pr_r26_pe2_idx  ON smhi."pr_ensmean_rcp26_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX pr_r26_pe3_idx  ON smhi."pr_ensmean_rcp26_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX pr_r45_pe1_idx  ON smhi."pr_ensmean_rcp45_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX pr_r45_pe2_idx  ON smhi."pr_ensmean_rcp45_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX pr_r45_pe3_idx  ON smhi."pr_ensmean_rcp45_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX pr_r85_pe1_idx  ON smhi."pr_ensmean_rcp85_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX pr_r85_pe2_idx  ON smhi."pr_ensmean_rcp85_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX pr_r85_pe3_idx  ON smhi."pr_ensmean_rcp85_ANN_30y_2071_2100.shp"  USING GIST (geom);
--  r10mm kraftig nederbörd dygn per år
CREATE INDEX r10mm_r26_pe1_idx  ON smhi."r10mm_ensmean_rcp26_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX r10mm_r26_pe2_idx  ON smhi."r10mm_ensmean_rcp26_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX r10mm_r26_pe3_idx  ON smhi."r10mm_ensmean_rcp26_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX r10mm_r45_pe1_idx  ON smhi."r10mm_ensmean_rcp45_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX r10mm_r45_pe2_idx  ON smhi."r10mm_ensmean_rcp45_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX r10mm_r45_pe3_idx  ON smhi."r10mm_ensmean_rcp45_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX r10mm_r85_pe1_idx  ON smhi."r10mm_ensmean_rcp85_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX r10mm_r85_pe2_idx  ON smhi."r10mm_ensmean_rcp85_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX r10mm_r85_pe3_idx  ON smhi."r10mm_ensmean_rcp85_ANN_30y_2071_2100.shp"  USING GIST (geom);
-- r20mm extremt kraftig nederbörd dygn per år
CREATE INDEX r20mm_r26_pe1_idx  ON smhi."r20mm_ensmean_rcp26_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX r20mm_r26_pe2_idx  ON smhi."r20mm_ensmean_rcp26_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX r20mm_r26_pe3_idx  ON smhi."r20mm_ensmean_rcp26_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX r20mm_r45_pe1_idx  ON smhi."r20mm_ensmean_rcp45_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX r20mm_r45_pe2_idx  ON smhi."r20mm_ensmean_rcp45_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX r20mm_r45_pe3_idx  ON smhi."r20mm_ensmean_rcp45_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX r20mm_r85_pe1_idx  ON smhi."r20mm_ensmean_rcp85_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX r20mm_r85_pe2_idx  ON smhi."r20mm_ensmean_rcp85_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX r20mm_r85_pe3_idx  ON smhi."r20mm_ensmean_rcp85_ANN_30y_2071_2100.shp"  USING GIST (geom);
-- dd
CREATE INDEX dd_r26_pe1_idx  ON smhi."dd_ensmean_rcp26_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX dd_r26_pe2_idx  ON smhi."dd_ensmean_rcp26_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX dd_r26_pe3_idx  ON smhi."dd_ensmean_rcp26_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX dd_r45_pe1_idx  ON smhi."dd_ensmean_rcp45_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX dd_r45_pe2_idx  ON smhi."dd_ensmean_rcp45_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX dd_r45_pe3_idx  ON smhi."dd_ensmean_rcp45_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX dd_r85_pe1_idx  ON smhi."dd_ensmean_rcp85_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX dd_r85_pe2_idx  ON smhi."dd_ensmean_rcp85_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX dd_r85_pe3_idx  ON smhi."dd_ensmean_rcp85_ANN_30y_2071_2100.shp"  USING GIST (geom);
-- gsl vegetationsperiodens längd dygn per år
CREATE INDEX gsl_r26_pe1_idx  ON smhi."gsl_ensmean_rcp26_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX gsl_r26_pe2_idx  ON smhi."gsl_ensmean_rcp26_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX gsl_r26_pe3_idx  ON smhi."gsl_ensmean_rcp26_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX gsl_r45_pe1_idx  ON smhi."gsl_ensmean_rcp45_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX gsl_r45_pe2_idx  ON smhi."gsl_ensmean_rcp45_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX gsl_r45_pe3_idx  ON smhi."gsl_ensmean_rcp45_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX gsl_r85_pe1_idx  ON smhi."gsl_ensmean_rcp85_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX gsl_r85_pe2_idx  ON smhi."gsl_ensmean_rcp85_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX gsl_r85_pe3_idx  ON smhi."gsl_ensmean_rcp85_ANN_30y_2071_2100.shp"  USING GIST (geom);
-- gsl vegetationsperiodens längd dygn per år
CREATE INDEX tas_r26_pe1_idx  ON smhi."tas_ensmean_rcp26_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX tas_r26_pe2_idx  ON smhi."tas_ensmean_rcp26_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX tas_r26_pe3_idx  ON smhi."tas_ensmean_rcp26_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX tas_r45_pe1_idx  ON smhi."tas_ensmean_rcp45_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX tas_r45_pe2_idx  ON smhi."tas_ensmean_rcp45_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX tas_r45_pe3_idx  ON smhi."tas_ensmean_rcp45_ANN_30y_2071_2100.shp"  USING GIST (geom);
CREATE INDEX tas_r85_pe1_idx  ON smhi."tas_ensmean_rcp85_ANN_30y_2011_2040.shp"  USING GIST (geom);
CREATE INDEX tas_r85_pe2_idx  ON smhi."tas_ensmean_rcp85_ANN_30y_2041_2070.shp"  USING GIST (geom);
CREATE INDEX tas_r85_pe3_idx  ON smhi."tas_ensmean_rcp85_ANN_30y_2071_2100.shp"  USING GIST (geom);

Skapa tabellen smhi_rutor_absolute

Skapa ett schema, för att lägga in den slutgiltliga tabellem med rutor, där varje ruta innehåller alla attribut.

Code Block
create schema skfab_climate_24;

Utångstabellen för alla lager och teman är FrostDygn, Scenaro2.6 Period 1 (fd_r26_pe1). Första kolumnen är Id och sista geom. Denna tabell är den enda med from (rad 12). Alla övriga är en spatial join på rutan.

När ett eller alla teman är klara så för frågan i ovanstående create table skfab_climate_24.smhi_rutnat_absolute

Code Block

language	sql

 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;

När tabellen smhi_rutor_absolute finns klar. 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

language	sql


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 fd_r26_pe1),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe2),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe3),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe1),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe2),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe3),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe1),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe2),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe3)) as g_fd,
 least (
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe1),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe2),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe3),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe1),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe2),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe3),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe1),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe2),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe3)) as l_fd,
 greatest (
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe1),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe2),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r26_pe3),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe1),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe2),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r45_pe3),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe1),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe2),
 PERCENTILE_CONT(0.99) WITHIN GROUP(ORDER BY fd_r85_pe3)) 
 - 
 least (
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe1),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe2),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r26_pe3),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe1),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe2),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r45_pe3),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe1),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe2),
 PERCENTILE_CONT(0.01) WITHIN GROUP(ORDER BY fd_r85_pe3)) as d_fd
 from skfab_climate_24.skfab_climate_24.smhi_rutnat_absolute

 ), pro as ( -- calculate the procentage (0-100) from values
 select r.id, 'Frost dygn per år'as name,
 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_pe3 as rpc85_3_abs,
 ((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'as name,
 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_pe3 as rpc85_3_abs,
 ((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' as name,
 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_pe3 as rpc85_3_abs,
 ((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' as name,
 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_pe3 as rpc85_3_abs,
 ((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' as name,
 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_pe3 as rpc85_3_abs,
 ((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' as name,
 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_pe3 as rpc85_3_abs,
 ((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' as name,
 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_pe3 as rpc85_3_abs,
  ((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 per månad' as name,
 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_pe3 as rpc85_3_abs,
  ((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
);

Version	Old Version 2	New Version Current
Changes made by	Sven-Ola Svensson	Sven-Ola Svensson
Saved on	2025-01-08	2025-01-29

Versions Compared

Key

Ny laddning av klimatdata med absoluta värden

Flytta lagren till Posgresdatabasen från QGIS

Skapa tabellen smhi_rutor_absolute