Code Block

language	sql

create schema matchning;
--drop schema matchning cascade;

Skadekategorier

Tabell för att lagra gemensamma kategorier (categories_generic). De finns på svenska , norska och engelska

Code Block

language	sql

/* create and update table matchning.categories_generic */
create table matchning.categoriestypes_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
);
-- drop table matchning.categoriestypes_generic

De vanligast förkommande categorizer hämtas in från SIV kategorier. De som förkommer mer än tre gånger. Översättning sker till norska och svenska. Norska orden används också vid konverteringen

...

Code Block

language	sql

--Uppdatera först de kategorier som matchar med de generiska på svenska och norska
update matchning.categories_crossref set generic_category_id = id from matchning.categories_generic 
where matchning.categories_crossref.name in (categories_generic.namn_sv, categories_generic.namn_no);

-- Fortsätt sedan att uppdatera de kategorier som inte är matchade
update matchning.categories_crossref set generic_category_id = 3 --egendom
where matchning.categories_crossref.name in ('Säkerhet','Byggfel, bristande underhåll', 'Byggfel/bristande underhåll', 'Byggfel/Bristande underhåll', 'Byggnadsfel' );

update matchning.categories_crossref set generic_category_id = 4 --Olyckshändelse
where matchning.categories_crossref.name in ('Skada/Olycka Utvändigt','Skada/Olycka Utemiljö','Olycka','Olycka (tillbud)' );

update matchning.categories_crossref set generic_category_id = 2 --uppsåt
where matchning.categories_crossref.name in ('Skadegörelse Byggnad Invändigt', 'Skada/Olycka Invändigt','Skadegörelse Byggnad Utvändigt','Skadegörelse Utemiljö','SD Utemiljö','SK Skadegörelse','Fordonsskador','Oaktsamhet' );

update matchning.categories_crossref set generic_category_id = 1 --ansvar
where matchning.categories_crossref.name in ('Sjukdom','.','-odefinierad-','Tillbud','Övrigt','Snöröjning','Väderpåverkan' );

För att testa att allt är matchat så används

...

Code Block

language	sql

/* create and update table matchning.categoriestypes_generic */
create table matchning.types_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
);
-- drop table matchning.types_generic

Code Block

language	sql

INSERT INTO matchning.types_generic (namn_sv)
select q.nametype
from
(
select distinct named.type,  count(1d."type" )
asFROM number
from skfab.damage_types cview_damages_all d  
group by 1
order by 2 desc
limit 10
) q
where number > 3 and name not in 
('-odefinierad-', 'Annet', 'Hærverk','Inventarier odyl','Ruteknusing',
'Tagging','Vann','Vattenskada','Övriga skador','Skadegörelse')

Code Block

language	sql

Code Block

language	sql

update matchning.types_generic set namn_no = 'Vandalism', namn_en ='Vandalism'
where namn_sv = 'Skadegörelse';
update matchning.types_generic set namn_no = 'AnsvarVann', namn_en ='LiabilityWater'
where namn_sv = 'AnsvarVatten';
update matchning.types_generic set namn_no = 'AvløpGlassknuser', namn_en ='DrainageGlass breaker'
where namn_sv = 'AvloppGlaskross';
update matchning.types_generic set namn_no = 'BrannDoodle', namn_en ='FireDoodle'
where namn_sv = 'BrandKlotter';
update matchning.types_generic set namn_no = 'EiendomInnbrudd', namn_en ='PropertyBurglary'
where namn_sv = 'EgendomInbrott';
update matchning.types_generic set namn_no = 'GlassknuserAnsvar', namn_en ='Glass breakerLiability'
where namn_sv = 'GlaskrossAnsvar';
update matchning.types_generic set namn_no = 'InnbruddBrann', namn_en ='BurglaryFire'
where namn_sv = 'InbrottBrand';
update matchning.types_generic set namn_no = 'DoodleTyveri', namn_en ='DoodleTheft'
where namn_sv = 'KlotterStöld';
update matchning.types_generic set namn_no = 'MaskinAnnet', namn_en ='MachineOther'
where namn_sv = 'MaskinÖvrigt';
update matchning.types_generic set namn_no = 'Skade', namn_en ='Damage'
where namn_sv = 'Skada';
update matchning.types_generic set namn_no = 'Tyveri', namn_en ='Theft'
where namn_sv = 'Stöld';
update matchning.types_generic set namn_no = 'Vann', namn_en ='Water'
where namn_sv = 'Vatten';
update matchning.types_generic set namn_no = 'Annen', namn_en ='Other'
where namn_sv = 'Övrigt';
update matchning.types_generic set namn_no = 'Personlig', namn_en ='Personal', namn_sv='Person'
where id = 8 ;
update matchning.types_generic set namn_no = 'Forbrytelse', namn_en ='Crime', namn_sv='Brottslihet'
where id = 5 ;

Tabellen ser ut så här

id	namn_sv	namn_no	namn_en
1	Övrigt	Annen	Other
2	Brand	Brann	Fire
3	Vatten	Vann	Water
4	Glaskross	Glassknuser	Glass breaker
5	Brottslihet	Forbrytelse	Crime
6	Skada	Skade	Damage
7	Maskin	Maskin	Machine
8	Person	Personlig	Personal
9	Ansvar	Ansvar	Liability
10	Klotter	Doodle	Doodle
11	Egendom	Eiendom	Property
12	Avlopp	Avløp	Drainage

Skapa en kors-referens-tabell med hjälp av befintlig typ-tabell

Uppdatera de vanligaste och komplettera sen uppdateringen med de udda

Code Block

language	sql

/* create and update table matchning.categories_types
 * drop table matchning.types_crossref */
create table matchning.types_crossref
as
(
select damage_type_id, name from skfab.damage_types dc 
);
alter table matchning.types_crossref add column generic_type_id integer;
--Uppdatera de vanligaste typerna

Tabellen ser ut så här

id	namn_sv	namn_no	namn_en
1	Skadegörelse	Vandalism	Vandalism
2	Vatten	Vann	Water
3	Glaskross	Glassknuser	Glass breaker
4	Klotter	Doodle	Doodle
5	Inbrott	Innbrudd	Burglary
6	Ansvar	Ansvar	Liability
7	Brand	Brann	Fire
8	Stöld	Tyveri	Theft
9	Övrigt	Annet	Other
10	Skada	Skade	Damage

Skapa en kors-referens-tabell med hjälp av befintlig typ-tabell

Uppdatera de vanligaste och komplettera sen uppdateringen med de udda

Code Block

language	sql

/* create and update table matchning.categories_types
 * drop table matchning.types_crossref */

create table matchning.types_crossref
as
(
select damage_type_id, name from skfab.damage_types dc 
);
alter table matchning.types_crossref add column generic_type_id integer;
--Uppdatera de vanligaste typerna
update matchning.types_crossref  set generic_type_id = id from matchning.types_generic  
where matchning.types_crossref.name in (types_generic.namn_sv, types_generic.namn_no);
--Uppdatera de udda typerna
update matchning.types_crossref set generic_type_id = 1 --Skadegörelse
where matchning.types_crossref.name in ('Gatu-/Parkbelysning', 'Mekanisk/elektrisk','Hærverk','Skadegörelse','Annan skadegörelse','Sakskada' );
update matchning.types_crossref set generic_type_id = 2 --Vatten
where matchning.types_crossref.name in ('Vatten/Avlopp','Avlopp','Kärl','VA-skada','Miljöskada','Nedebördsskada','Olja','Naturskade','VA-skada','VA','Miljöutredning','Vattenskada','Frysskada','Översvämning','Vattenskada (i egna fastigheter)' );
update matchning.types_crossref set generic_type_id = 3 --Glaskross
where matchning.types_crossref.name in ('Glas','Ruteknusing' );
update matchning.types_crossref set generic_type_id = 4 --Klotter
where matchning.types_crossref.name in ('Tagging');
update matchning.types_crossref  set generic_type_id = id from types_generic  
5 --Inbrott
where matchning.types_crossref.name in (types_generic.namn_sv, types_generic.namn_no);
--Uppdarera de udda typerna
'Inbrottsskada','Inbrottsskada','Innbrudd','Inbrott' );
update matchning.types_crossref set generic_type_id = 16 --Ansvar
where matchning.types_crossref.name in ('.Skadeståndsansvar övrigt','Annan ansvarsskada'Övriga skador, 'LOU-skada','Praktik','AnnetMiljö/byggherreansvar', 'Generellt ansvar'Annan ansvarsskada','-odefinierad-','Annan','Storm','Tillbud' );
update matchning.types_crossref set generic_type_id = 2
where matchning.types_crossref.name in ('Brandtillbud');
update matchning.types_crossref set generic_type_id = 3 --Vatten
where matchning.types_crossref.name in ('Vattenskada','Fukt/mögel','Frysskada','Översvämning','Vattenskada (i egna fastigheter)' );
update matchning.types_crossref set generic_type_id = 4 --Glaskross
where matchning.types_crossref.name in ('Glas','Ruteknusing, 'Oaktsamhet','Diskriminering','Förmögenhetsskada','Myndighetsskada','Skadeståndsansvar', 'Tandskada', 'Generellt ansvar','Kroppskada','Patient','Nackskada','Person','Halkolycka','Personskada','Patientskada','Sårskada','Fallskada','Personskade','Sekundär drunkning','Olyckshändelse','Stroke' );
update matchning.types_crossref set generic_type_id = 57 --brottBrand
where matchning.types_crossref.name in ('RånBrandlarm/Inbrottslarm','Inbrottsskada','Ofredande','Hot','Inbrottsskada','Sabotage','Stöld','Innbrudd','Tyveri','Inbrott'Brandtillbud' );
update matchning.types_crossref set generic_type_id = 68 --SkadaStöld
where matchning.types_crossref.name in ('HærverkStöld','Skadegörelse','Annan skadegörelse','Sakskada'Tyveri' );
update matchning.types_crossref set generic_type_id = 79 --MaskinÖvrigt
where matchning.types_crossref.name in ('Motor','Fordon','Privata fordone'.','Maskiner.','Tunga fordonUtrymning','MaskinskadaSabotage', 'Privata fordonOfredande','FordonsskadaHot', 'Kommunens tjänstebilarRån', 'Motor-odefinierad-','Transports fordon',KÖV'Transport','PlogskadaProdukt','VägskadaOkänd','Plogskada','Transports fordonIT-incident' );
update matchning.types_crossref set generic_type_id = 810 --PersonSkada
where matchning.types_crossref.name in ('Tandskada 'Fukt/mögel','Dörrar','KroppskadaMögel/Fukt','PatientFasader','NackskadaDörrar/Entreér','PersonVärmesystem','HalkolyckaVentilation','PersonskadaSanitetsutrustning','PatientskadaHiss','SårskadaVäggar','FallskadaInredning','PersonskadeInnertak ','Sekundär drunkningGolv','OlyckshändelseElkontakter/Eluttag','Stroke' );
update matchning.types_crossref set generic_type_id = 9 --Ansvar
where matchning.types_crossref.name in ('MyndighetsskadaSnöröjning','Passersystem','FörmögenhetsskadaBrandutrustning','DiskrimineringIdrottsutrustning','KÖVUtebelysning','OaktsamhetNedskräpning','ProduktMotorvärmare','KärlLekutrustning',
'SkadeståndsansvarGräsytor/Träd/Buskar','PåkörningBelysning','SlarvTak','OkändMaskin','Generellt ansvarEgendom','ExplosionTillbud', 'AllriskStorm', 'IT-incidentAnnan' );
update matchning.types_crossref set generic_type_id = 10 --klotter
where matchning.types_crossref.name in ('Tagging');
update matchning.types_crossref set generic_type_id = 11 --egendom
where matchning.types_crossref.name in ('Inventarier odyl','Mekanisk/elektrisk','Mögel/Fukt','Gatu-/Parkbelysning','Bristande underhåll',
'Byggfel','Entreprenad','Mögel, fukt','Skadad egendom','Snölast','Miljö/byggherreansvar','El','Praktik','LOU-skada','Mögel/fukt');
update matchning.types_crossref set generic_type_id = 12 --Avlopp / miljö
where matchning.types_crossref.name in ('VA-skada','Miljöskada','Nedebördsskada','Olja','Naturskade','VA-skada','VA','Miljöutredning');

select damage_type_id,name, g.namn_sv 
from matchning.types_crossref
join types_generic g on generic_type_id = g.id, 'Annet', 'Övriga skador', 'El', 'Snölast', 'Skadad egendom', 'Mögel, fukt', 'Bristande underhåll','Mögel/fukt','Byggfel','Entreprenad', 'Inventarier odyl','Tunga fordon','Privata fordone','Fordon','Maskinskada','Privata fordon','Fordonsskada','Kommunens tjänstebilar','Motor','Transports fordon','Transport','Plogskada','Vägskada','Plogskada','Transports fordon','Maskiner','Allrisk','Explosion','Slarv','Påkörning','Sabotage');

-- Uppdatera skfab.damage_types från den tillfälliga matchning.categories_crossref
alter table skfab.damage_types add column generic integer;
-- alter table skfab.damage_types drop column generic;
update skfab.damage_types set generic=generic_type_id from  matchning.types_crossref
where  skfab.damage_types.damage_type_id = matchning.types_crossref.damage_type_id

Verksamheter

Generiska verksamheter har tagits fram genom att titta på kommunernas hemsidor och använda samma och ganska grova indelning.

...

Code Block

language	sql

create table matchning.operation_types_generic
(
id serial,
namn_sv text,
namn_no text,
namn_en text
);
-- drop table matchning.operation_types_generic
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Skola/förskola','Skole/førskole','School/preschool') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Omsorg/stöd','Omsorg/støtte','Care/support') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Bygga/bo/miljö','Bygg/bo/miljø','Building/living/environment') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Trafik/gator','Trafikk/gater','Traffic/streets') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Uppleva/göra','Oppleve/gjøre','Experience/do') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Kommun/politik','Kommune/politikk','Municipality/politics') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Jobb/företagande','Jobb/entreprenørskap','Job/entrepreneurship') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Trygghet','Sikkerhet','Security') ;
insert into matchning.operation_types_generic (namn_sv,namn_no,namn_en) values ('Vatten/energi','Vann/energi','Water/energy') ;
--select * from operation_types_generic

En referenstabell skapas och kommunerna befintliga verksamheter matchas med de generiska. Detta är ett tolkningsarbete

Code Block

language	sql



create table matchning.operation_types_crossref
as
(
select operation_type_id, name from skfab.operation_types dc 
);
alter table matchning.operation_types_crossref add column generic_operation_types_id integer;
--select * from matchning.operation_types_crossref

update matchning.operation_types_crossref set generic_operation_types_id = 1 --Skola/förskola
where matchning.operation_types_crossref.name in (förskola
where matchning.operation_types_crossref.name in ('Fritid', 'Friluftsliv- o kulturarv','Vård- och omsorgsboende, korttidsvistelse','Kultur- o ungdomsenheten','Grundskola/särskola','Gymnasieskola','Grundskola','Gymnasium','Grundskola','Högalid rektorsområde','Berga rektorsområde','Luossa rektorsområde',
'Triangel rektorsområde','Bolag rektorsområde','Raket rektorsområde','Lombolo rektorsområde','Tuolla/Jukkas rektorsområde','Vittangi rektorsområde','Karesuando rektorsområde','Elevhälsan',
'Förskola/barnomsorg','Gymnasieskola','Förskola','Särskola','Vuxenskola','Gymnasieskolan','Barnomsorg/förskola','Grundskola/särskola','Gymnasieskola/Komvux','Lokaler','Vuxenutbildningen',
'Skoler','Barnehager','Själevad skolområde','Grundskola/särskola 7-9','Grundskola/särskola F-6','Gymnasieskola/-särskola','Vuxenutbildning');

update matchning.operation_types_crossref set generic_operation_types_id = 2 --Omsorg/stöd
where matchning.operation_types_crossref.name in ('Särskilt boende', 'Vård- och omsorgsboende, korttidsvistelse','Omsorg Administrativ verksamhet m.m.','Måltid','Handikappomsorg','Måltidsverksamhet','Omsorg/äldreomsorg','Socialtjänst',
'Familjehem','Hemvård','LSS-boende','Socialt boende','Vårdboende','Äldreboende','Trygdeboliger','Socialförvaltningen','Äldreomsorg','Placeringsenheten','Stöd och omsorg','Social utredning',
'Socialt stöd','Individ- och familjeomsorg','Ungdomsavdelningen','Handikappomsorg','Äldreomsorg Hemtjänst','LSS verksamhet','IFO boenden','Ordinärt boende FO','Vård- och omsorgsboende,
 korttidsvistelse','LSS boende');

update matchning.operation_types_crossref set generic_operation_types_id = 3 --Bygga/bo/miljö
where matchning.operation_types_crossref.name in ('Lokal','Magasin','Kaj','Pitebo','Pireva','Bygg- och miljö','Ordinärt boende','Fysisk planering','Övikshem AB','Fastighetsförvaltning/kommunal byggnad',
'Fastighetsförvaltning','Kultur- och fritidsverksamhet','Drift och underhåll','Projektenhet','Stadsmiljö','Bostadsrätt','Fastighet','Garage','Hyreslägenhet','Lantmäteri','Saneringsfastigheter',
'Parkeringar','Parkeringshus','Hyreshus','Boendemoduler ','Fastighetsförvaltning','Hamnverksamhet','Renhållning/materialåtervinning','Sotning','Stadsplanering','Materialåtervinning/Deponi',
'Renhållning/Underhåll','Anläggningar','Mark och Exploatering','Boliger','Helsebygg','Administrasjonsbygg','Kulturbygg','Idrettsanlegg','Markaeiendommer','Avfall & Återvinning','MEX','Fastigheter');
update matchning.operation_types_crossref set generic_operation_types_id = 4 --Trafik/gator
where matchning.operation_types_crossref.name in ('Järnväg','Vägar','Farled','Öviks Hamn & Logistik','Renhållning','Tekniska verksamheter','Teknik och gator','Park och grönområden','Flygplats','Fordonsverkstad'
,'Gata/Park','Övik Airport AB','Trafik och Park','Trafik och Parker','Gata/park','Mark- och trafik avdelningen' );

update matchning.operation_types_crossref set generic_operation_types_id = 5 --Uppleva/göra
where matchning.operation_types_crossref.name in ('Fritid','Friluftsliv- o kulturarv','Serveringshytter','Kultur och Fritid','Kulturskolecentrum','Paradiset','kiruna kommunpartner','Fritidsavdelningen','Fritid och sport','Kultur','Bibliotek',
'Bad','Idrott och motion','Kulturliv','Skärgården','Kultur- o ungdomsenheten','Kultur och fritidsverksamhet','Djur- och nöjespark','Kulturskola' );

update matchning.operation_types_crossref set generic_operation_types_id = 6 --Kommun/politik
where matchning.operation_types_crossref.name in ('Ansvarsskade','IT','-odefinierad-','IT-avdelningen','Städ','IFO','Ekonomi och upphandling','Service','Kommunledningsförvaltningen','Operativ','Personal',
'Administration','HR','Kommunikation','Myndighetsutövning','Upphandling' ,'Utställning','Annan/övrig','Verksamhet','Storkök','Utställningsverksamhet','Vaktmästeri och städ',
'Förvaltare','Administrativa stödtjänster','Annan/övrig','Offentlig utsmyckning','Daglig verksamhet','Övrigt','Ekonomi');

update matchning.operation_types_crossref set generic_operation_types_id = 7 --Jobb/företagande
where matchning.operation_types_crossref.name in ('Næringsbygg','Rodret i Önsköldsvik AB','Tjänstemannacentrum i Örnsköldsvik AB','Piteå Hamn','Pnf','Tillväxt','Kompetensförsörjning','Näringsliv och samhälle','Arbetsmarknad',
'Arbetsmarknadsutbildning/ åtgärd','Näringsliv','Arbetsmarknadsutbildning /åtgärd','Arbetsmarknad och integration','Kostenheten','Administration och innovation' );

update matchning.operation_types_crossref set generic_operation_types_id = 8 --Trygghet
where matchning.operation_types_crossref.name in ('Säkerhet','Förebyggande','Räddningstjänsten','Brandförsvaret','Räddningstjänst' );

update matchning.operation_types_crossref set generic_operation_types_id = 9 --Vatten/energi
where matchning.operation_types_crossref.name in ('Övik Energi AB','PiteEnergi','Pumpstation','VA-bolag','Energiförsörjning/Kraftvärme','Vatten- och avloppsverk','Energiförsörjning','Vatten och avlopp',
'Vatten- och avloppsnät','VA','Anläggningsavdelning' );
-- select * from matchning.operation_types_crossref

`Uppdatera SKFAB schemat`

Tabellerna skfab.damage_categories, skfab.damage_types och skfab.operation_types uppdateras alla med en ny kolumn generic. Värden från den tillfälliga tabellerna matchning.categories_crossref och matchning.types_crossref uppdaras till skfab.damage_categories och skfab.damage_types

Info
Redigerings- och sökformulär måste också uppdateras

Code Block

language	sql

-- Uppdatera skfab.damage_categories från den tillfälliga matchning.categories_crossref
alter table skfab.damage_categories add column generic integer; 
-- alter table skfab.damage_categories drop column generic; 
update skfab.damage_categories set generic=generic_category_id from  matchning.categories_crossref
where  skfab.damage_categories.damage_category_id = matchning.categories_crossref.damage_category_id;

-- Uppdatera skfab.damage_categories från den tillfälliga matchning.categories_crossref
alter table skfab.damage_types add column generic integer;
-- alter table skfab.damage_types drop column generic;
update skfab.damage_types set generic=generic_type_id from  matchning.types_crossref
where  skfab.damage_types.damage_type_id = matchning.types_crossref.damage_type_id ;

-- Uppdatera skfab.operation_types
alter table skfab.operation_types add column generic integer;
-- alter table skfab.operation_types drop column generic;
update skfab.operation_types set generic=matchning.operation_types_crossref.generic_operation_typetypes_id from  matchning.operation_types_crossref
where  skfab.operation_types.operation_type_id = matchning.operation_types_crossref.operation_type_id

...

Nu testas att skriva ut skador med generiska kategorier och typer

Code Block

language	sql

----testa att relationerelationer till generiska kategorier och typer finns
--Använd norska då kunenkundens nummer är 100108
select d.customer_link,damage_id, 
damage_date,
category_link, 
dc."name", 
case when d.customer_link = 100108 then cg.namn_no else cg.namn_sv end ,
d.type_link ,
dt."name" ,
case when d.customer_link = 100108 then tg.namn_no else tg.namn_sv end ,
description
from skfab.damages d
join skfab.damage_categories dc on dc.damage_category_id = d.category_link 
join skfab.categories_generic cg on cg.id = dc.generic 
join skfab.damage_types dt on dt.damage_type_id = d.type_link
join skfab.types_generic tg on tg.id = dt.generic ;

Grupper på kategorier och typer med antal och total skadekostnad

Code Block

language	sql

--Gruppera på generiska
--Generiska kategorier
select cg.namn_sv, count(*) as antal, sum(d.cost_def) as kostnad
from skfab.damages d
join skfab.damage_categories dc on dc.damage_category_id = d.category_link 
join skfab.categories_generic cg on cg.id = dc.generic 
group by 1;

--Generiska typer
select tg.namn_sv, count(*) as antal, sum(d.cost_def) as kostnad
from skfab.damages d
join skfab.damage_types dt on dt.damage_type_id = d.type_link
join skfab.types_generic tg on tg.id = dt.generic 
group by 1;

Stäng och ta bort tillfälligt schema

...

Version	Old Version 7	New Version Current
Changes made by	Sven-Ola Svensson	Anders Johnsson
Saved on	2024-01-15	2024-05-20

Versions Compared

Key

Skadekategorier

Verksamheter

`Uppdatera SKFAB schemat`

Stäng och ta bort tillfälligt schema

Content Comparison

Versions Compared

Key

Skadekategorier

Verksamheter

Uppdatera SKFAB schemat

Stäng och ta bort tillfälligt schema

`Uppdatera SKFAB schemat`