-
Notifications
You must be signed in to change notification settings - Fork 0
/
functions.sql
executable file
·3241 lines (2610 loc) · 94.8 KB
/
functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-----------------------------------------------------------------------------------
--
-- Copyright "2015" "Wuppertal Institut"
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-----------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- FUNKTIONEN
----------------------------------------------------------------------------------------
-- UMWANDLUNG DATENTYPEN (ALLGEMEIN)
-- ISREAL UND ISINT
-- Die beiden Funktionen bekommen String als Input und untersuchen, ob der Wert ein Real (isreal) oder Integer ist (isint)
-- ... und returnen Boolean
CREATE OR REPLACE FUNCTION otg_isreal (TEXT) RETURNS BOOLEAN
AS $$
DECLARE
x REAL;
BEGIN
x = $1::REAL;
RETURN TRUE;
EXCEPTION WHEN others THEN -- Exception ist eine Erweiterung des Begin-Blocks und wird ausgeführt, wenn ein Error vorkommt
RETURN FALSE; -- others steht für alle Arten von Fehlern (sammelt alles auf)
END;
$$
LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION otg_isint (TEXT) RETURNS BOOLEAN
AS $$
DECLARE
x INT;
BEGIN
x = $1::INT;
RETURN TRUE;
EXCEPTION WHEN others THEN -- Exception ist eine Erweiterung des Begin-Blocks und wird ausgeführt, wenn ein Error vorkommt
RETURN FALSE; -- others steht für alle Arten von Fehlern (sammelt alles auf)
END;
$$
LANGUAGE plpgsql IMMUTABLE;
-- Function that gets max ID of specified table and specified column
CREATE OR REPLACE FUNCTION otg_get_max_id(v_table TEXT, v_column TEXT) RETURNS BIGINT
AS $$
DECLARE
v_max_id BIGINT;
BEGIN
EXECUTE 'SELECT max('|| v_column ||') FROM ' || v_table INTO v_max_id;
IF v_max_id IS NULL THEN v_max_id := 0; END IF;
RETURN v_max_id;
END;
$$
LANGUAGE plpgsql;
-- CREATE OR REPLACE FUNCTION otg_update_osm (v_date date) RETURNS void
-- AS $$
-- DECLARE
-- v_object RECORD;
--
-- BEGIN
-- FOR v_object IN
-- SELECT * FROM nodes
-- LOOP
-- IF NOT v_object.id IN (SELECT id FROM osm_data.nodes)
-- THEN INSERT INTO osm_data.nodes (id, version, user_id, tstamp, changeset_id, tags, db_id)
-- VALUES (v_object.id,
-- v_object.version,
-- v_object.user_id,
-- v_object.tstamp,
-- v_object.changeset_id,
-- v_object.tags,
-- otg_get_max_id('osm_data.nodes', 'db_id') + 1);
-- END IF;
-- END LOOP;
--
--
-- END;
-- $$
-- LANGUAGE plpgsql;
-- NUMBER_OF_PART_CHAR
-- Anzahl eines bestimmten Characters in einem String
CREATE OR REPLACE FUNCTION otg_numb_of_cert_char (inp_text text, part_char text) RETURNS int
AS $$
SELECT length(inp_text)-length(replace(inp_text, part_char, ''))
$$
LANGUAGE SQL
RETURNS null on null INPUT;
-- GET_INT_FROM_SEMIC_STRING
-- Gibt für das Textfeld v_my_string die Spannung / CaBLE des (in v_position) angegeben Levels (der Leitung) zurück.
-- Wenn Spannungen nicht als Integer angegeben sind, oder leer sind, wird NULL zurückgegeben.
-- 0 als WERT bleibt 0.
-- Funktion könnte mit otg_isint() deutlich vereinfacht werden
CREATE OR REPLACE FUNCTION otg_get_int_from_semic_string (v_my_string TEXT, v_position INT) RETURNS INT
AS $$
DECLARE
v_volt_text TEXT;
v_volt_int INT;
BEGIN
v_volt_text := split_part(v_my_string,';',v_position);
v_volt_int := CASE WHEN
v_volt_text NOT SIMILAR TO '%[^0-9]%' --doppelt negativ (NOT und ^)
AND
v_volt_text != ''
THEN CAST (v_volt_text AS int)
ELSE NULL END;
RETURN v_volt_int;
END;
$$ LANGUAGE plpgsql;
-- GET_REAL_FROM_SEMIC_STRING
-- Gibt für das Textfeld v_my_string den Eintrag des (in v_position) angegeben Levels (der Leitung) als REAL zurück.
-- RETURNT NULL wenn kein REAL
-- 0 als WERT bleibt 0.
CREATE OR REPLACE FUNCTION otg_get_real_from_semic_string (v_my_string TEXT, v_position INT) RETURNS REAL
AS $$
DECLARE
v_volt_text TEXT;
v_volt_real REAL;
BEGIN
v_volt_text := split_part(v_my_string,';',v_position);
IF otg_isreal(v_volt_text) THEN
v_volt_real := v_volt_text :: REAL; END IF;
RETURN v_volt_real;
END;
$$ LANGUAGE plpgsql;
-- GET_REAL_ARRAY_FROM_SEMIC_STRING
-- Bekommt Semicolon-String als Input und Returnt diesen als Real-Array
-- Returnt NULL, wenn String ist NULL oder leer ('')
CREATE OR REPLACE FUNCTION otg_get_real_array_from_semic_string (v_semic TEXT) RETURNS REAL []
AS $$
DECLARE
v_array REAL [];
BEGIN
IF NOT v_semic IS NULL AND NOT v_semic = '' THEN
FOR i IN 1..(otg_numb_of_cert_char (v_semic, ';') + 1)
LOOP
v_array [i] := otg_get_real_from_semic_string (v_semic, i);
END LOOP;
RETURN v_array;
ELSE
RETURN NULL;
END IF;
END;
$$ LANGUAGE plpgsql;
-- GET_INT_FROM_WIRES_STRING
-- Zerteilt den Wires String (v_string) an der in v_position agegebene Stelle und gibt einen Integer mit der Wire-Anzahl zurück
-- Nur quad, triple, double oder single möglich!!
CREATE OR REPLACE FUNCTION otg_get_int_from_wires_string (v_string text, v_postition INT) RETURNS INT
AS $$
DECLARE
v_string_part text := split_part(v_string,';',v_postition);
BEGIN
RETURN CASE WHEN v_string_part = 'quad' THEN 4
WHEN v_string_part = 'triple' THEN 3
WHEN v_string_part = 'double' THEN 2
WHEN v_string_part = 'single' THEN 1
ELSE NULL END;
END;
$$ LANGUAGE plpgsql;
-- CALCULATE_SEMIC_SUM_FROM_STRING
-- Gibt die Summe der einzelnen zwischen den Semicolon stehenden Zahlen zurück
-- Für leere Strings ('') und NULL gibt die Funktion NULL zurück
-- für leere, von Semicolon getrennte "felder" wird Zahl 0 angenommen und die Summe wird nicht berechnet
CREATE OR REPLACE FUNCTION otg_calc_sum_from_semic_string (v_my_string TEXT) RETURNS INT
AS $$
DECLARE
v_sum INT;
v_semic_numb INT;
BEGIN
IF v_my_string = '' OR v_my_string IS NULL THEN v_sum := NULL; --Weil leere Daten nicht stimmen können, werden auch diesen NULL zugeordnet
ELSE
v_sum := 0;
v_semic_numb := otg_numb_of_cert_char(v_my_string, ';');
FOR i IN 1..(v_semic_numb + 1) LOOP
v_sum := v_sum + otg_get_int_from_semic_string(v_my_string, i);
END LOOP;
END IF;
RETURN v_sum;
END;
$$ LANGUAGE plpgsql;
-- POWER_LINE_READ_WIRES
-- Füllt die Spalte wires_array
-- Das Array wird nur gefüllt, es nur eine Wires-Information gibt.
CREATE OR REPLACE FUNCTION otg_read_wires () RETURNS VOID
AS $$
DECLARE
v_line RECORD;
BEGIN
FOR v_line IN
SELECT id, voltage, cables, wires, numb_volt_lev FROM power_line WHERE power = 'line'
LOOP
CASE -- Falls wires nicht NULL ist und nicht durch Semikolon getrennte Werte besitzt (Auflistung)
WHEN NOT v_line.wires IS NULL AND
otg_numb_of_cert_char (v_line.wires, ';') = 0 THEN
FOR i IN 1..v_line.numb_volt_lev
LOOP
UPDATE power_line SET wires_array [i] = otg_get_int_from_wires_string (wires, 1)
WHERE id = v_line.id;
END LOOP;
ELSE END CASE;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- POWER_LINE_READ_CIRCUITS
-- Falls Cables unbekannt sind und der Circuit-Eintrag bestimmte kriterien erfüllt (siehe Kommentare)
-- Kann die anzahl Leiterstränge berechnet werden, mit der später weitergerechnet wird.
CREATE OR REPLACE FUNCTION otg_read_circuits () RETURNS VOID
AS $$
DECLARE
v_line RECORD;
v_cables_per_circuit INT;
BEGIN
FOR v_line IN
-- Die Circuit-Abfrage muss nur für power = cable gemacht werden
-- ...da sie für Freileitungen nur dann sinnvoll ist, falls z.B. 6 Leiderseile eigentlich nur 1 Stromkreise sind.
-- Dies wird im Modell aber entweder durch Relations abgedeckt, oder überbleibende Leitungen verbinden sich ohnehin.
SELECT id, voltage, numb_volt_lev, cables, circuits, frequency_array FROM power_line WHERE power = 'cable'
LOOP
IF -- Wenn es noch keinen Cables-Eintrag gibt
-- (Generell wird über den Cables-Eintrag gearbeitet)
v_line.cables IS NULL
-- ... und es einen Circuit-Eintrag gibt
AND NOT v_line.circuits IS NULL
-- ... und dieser eindeutig ist (Keine Auflistung möglich)
AND otg_numb_of_cert_char (v_line.circuits, ';') = 0
-- Wenn es nur eine Spannungsebene gibt ODER
-- Die Anzahl der Spannungsebenen gleich der Anzahl der Circuits ist und
-- auch die Frequenz jeder Spannungsebene der Leitung bekannt ist
AND (v_line.numb_volt_lev = 1
OR (v_line.numb_volt_lev = otg_get_int_from_semic_string (v_line.circuits, 1)
AND array_length(v_line.frequency_array, 1) = v_line.numb_volt_lev))
THEN
-- Setzt Anzahl cable pro Stromkreis.
-- Nur, wenn die Frequenz auf jeder Spannungsebene gleich ist
-- (sonst können die Stromkreise nicht zugeordnet werden)
CASE WHEN 50 = ALL (v_line.frequency_array)
THEN v_cables_per_circuit := 3;
WHEN 0 = ALL (v_line.frequency_array)
OR 16.7 = ALL (v_line.frequency_array)
THEN v_cables_per_circuit := 2;
-- Kann die Anzahl cables pro Stromkreis nicht gesetzt werden,
-- ... wird unten (wieder) NULL in cables geschrieben.
ELSE v_cables_per_circuit := NULL;
END CASE;
CASE WHEN v_line.numb_volt_lev = 1
THEN
UPDATE power_line
SET cables_array [1] = v_cables_per_circuit * otg_get_int_from_semic_string (v_line.circuits, 1)
WHERE id = v_line.id;
-- Falls mehrere eindeutig zuzuordnende Circuits (anzahl Circuits = Anzahl Spannungsebenen)
-- bekannt sind. Können Cables verteilt werden.
WHEN v_line.numb_volt_lev > 1
AND v_line.numb_volt_lev = otg_get_int_from_semic_string (v_line.circuits, 1)
THEN
FOR i IN 1..v_line.numb_volt_lev
LOOP
UPDATE power_line
SET cables_array [i] = v_cables_per_circuit
WHERE id = v_line.id;
END LOOP;
ELSE END CASE;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- POWER_LINE_READ_FREQUENCY
-- Füllt die Spalte frequency_array
CREATE OR REPLACE FUNCTION otg_read_frequency () RETURNS VOID
AS $$
DECLARE
v_line RECORD;
BEGIN
FOR v_line IN
SELECT id, voltage, cables, frequency, numb_volt_lev FROM power_line
LOOP
-- Wenn frequency nicht NULL ist und nur aus einem Wert besteht (keine Semicolons etc.)
-- ... dann wird dieser Wert für alle Spannungsebenen übernommen
-- (häufig bei 50 der FAll)
CASE WHEN NOT v_line.frequency IS NULL AND
otg_isreal(v_line.frequency) THEN
FOR i IN 1..v_line.numb_volt_lev
LOOP
UPDATE power_line SET frequency_array [i] = frequency :: REAL
WHERE id = v_line.id;
END LOOP;
-- Dieser Teil kann so nicht verwendet werden, da Frequency (ähnlich Wires) nur eine Auflistung der auf der...
-- ...Freileitung vorhandenen Frequenzen darstellt.
-- Bei mehreren Einträgen könne diese nur verhältnismäßig aufwendig zugeordnet werden
-- Falls die Informationen wirklich konsisten sind (Anzahl Spannungsebenen, Cables Einträge und Frequenzen)
-- WHEN -- otg_numb_of_cert_char (v_line.voltage, ';') = otg_numb_of_cert_char (v_line.cables, ';') AND
-- otg_numb_of_cert_char (v_line.voltage, ';') = otg_numb_of_cert_char (v_line.frequency, ';') AND
-- NOT otg_isreal(v_line.frequency) THEN
--
-- UPDATE power_line SET frequency_array = otg_get_real_array_from_semic_string (v_line.frequency)
-- WHERE id = v_line.id;
ELSE END CASE;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- INT_SUM
-- Berechnet die Summe von 4 Integern
-- NULL wird wir Zahl 0 berechnet!!!!
CREATE OR REPLACE FUNCTION otg_int_sum (int_1 INT, int_2 INT, int_3 INT, int_4 INT) RETURNS INT
AS $$
DECLARE
v_sum INT := 0;
BEGIN
IF NOT int_1 IS NULL THEN v_sum := v_sum + int_1; END IF;
IF NOT int_2 IS NULL THEN v_sum := v_sum + int_2; END IF;
IF NOT int_3 IS NULL THEN v_sum := v_sum + int_3; END IF;
IF NOT int_4 IS NULL THEN v_sum := v_sum + int_4; END IF;
RETURN v_sum;
END;
$$ LANGUAGE plpgsql;
-- ARRAY_SEARCH
-- Sucht wo ein Element in einem ARRAY zu finden ist.
CREATE OR REPLACE FUNCTION otg_array_search (v_element ANYELEMENT, v_array ANYARRAY) RETURNS INT
AS $$
SELECT i
FROM generate_subscripts ($2, 1) AS i
WHERE $2 [i] = $1
ORDER BY i
$$ LANGUAGE sql STABLE;
-- otg_array_search_2
-- Returnt ein INT [] mit den Indizes, wo das Element im Array zu finden ist (Element kann also mehrfach gefunden werden)
CREATE OR REPLACE FUNCTION otg_array_search_2 (v_element ANYELEMENT, v_array ANYARRAY) RETURNS INT []
AS $$
DECLARE
v_sub INT [] := NULL::int[];
v_count INT;
BEGIN
v_count := 0;
FOR i IN 1..array_length(v_array, 1)
LOOP
IF v_array [i] = v_element THEN
v_count := v_count + 1;
v_sub [v_count] := i;
END IF;
END LOOP;
RETURN v_sub;
END;
$$ LANGUAGE plpgsql;
-- ARRAY_REORDER_BY_MATCH
-- Ordnet ein Array neu. Und zwar so, dass die Werte, die auch im 2. Array auftauchen als erstes im Array stehen.
CREATE OR REPLACE FUNCTION otg_array_reorder_by_match (v_first INT [], v_sec INT []) RETURNS INT []
AS $$
DECLARE
v_in INT [];
v_out INT [];
n INT;
BEGIN
IF true = ALL (SELECT unnest (v_sec) IS NULL) --Alle einträge NULL
OR v_sec IS NULL
THEN RETURN v_first; END IF;
FOREACH n IN ARRAY v_first
LOOP
CASE
WHEN n = ANY (v_sec)
THEN v_in := array_append(v_in, n);
WHEN n != ANY (v_sec)
THEN v_out := array_append(v_out, n);
ELSE END CASE;
END LOOP;
RETURN v_in || v_out;
END
$$
LANGUAGE plpgsql;
-- ENTNORMALISIERUNG TABELLEN
-- SEPERATE_POWER_RELATION_MEMBERS ()
-- Trennt die Members aus power_relations wieder aus dem Array auf...
CREATE OR REPLACE FUNCTION otg_seperate_power_relation_members () RETURNS void
AS $$
DECLARE
v_members RECORD;
BEGIN
FOR v_members IN
SELECT id, members FROM power_circuits -- also alle
LOOP
FOR i IN 1..array_length(v_members.members, 1)
LOOP
INSERT INTO power_circ_members (relation_id, member_id)
VALUES (v_members.id,
v_members.members [i]);
END LOOP;
END LOOP;
END;
$$
LANGUAGE plpgsql;
-- DATEN-ÜBERPRÜFUNG CABLES (POWER_LINE)
-- POWER_LINE_ALL-CABLE_COMPLETE
-- Untersucht, ob alle Cable-Informationen vollständig sind!
-- Wenn es zu einer vorhandenen Spannung keine Cable-Angabe gibt, dann RETURN false!
CREATE OR REPLACE FUNCTION otg_check_all_cables_complete (v_id bigint) RETURNS boolean
AS $$
DECLARE
ok BOOLEAN;
BEGIN
ok := (SELECT NOT( NOT voltage_array [1] IS NULL AND cables_array [1] IS NULL
OR
NOT voltage_array [2] IS NULL AND cables_array [2] IS NULL
OR
NOT voltage_array [3] IS NULL AND cables_array [3] IS NULL
OR
NOT voltage_array [4] IS NULL AND cables_array [4] IS NULL)
FROM power_line --OB dies Summe aufgeht wird in cable-conflic Funktion untersucht!
WHERE id = v_id);
RETURN ok;
END;
$$ LANGUAGE plpgsql;
-- POWER_LINE_CABLE_COMPLETE
-- Untersucht, ob die Cable-Information am angegeben Level (v_lev) der Leitung (v_id) vollständig ist
-- Ist das Leitungslevel nicht vorhanden (und daher auch kein Cable-Eintrag vorgesehen) wird true zurückgegeben.
CREATE OR REPLACE FUNCTION otg_check_cable_complete (v_id bigint, v_lev INT) RETURNS boolean
AS $$
BEGIN
RETURN (SELECT NOT (NOT voltage_array [v_lev] IS NULL AND cables_array [v_lev] IS NULL) FROM power_line WHERE id = v_id);
END;
$$ LANGUAGE plpgsql;
-- KNOWN_CABLES_SUM
-- Berechnet die Summe der bereits bekannten cable
-- nicht zu verwechseln mit der cables_sum Spalte in der TAbelle!!!!! Diese wird direkt aus OSM-cables-string gelesen!!!!!
CREATE OR REPLACE FUNCTION otg_known_cables_sum (v_id BIGINT) RETURNS INT
AS $$
BEGIN
RETURN (SELECT otg_int_sum (cables_array [1], cables_array [2], cables_array [3], cables_array [4]) from power_line WHERE id= v_id);
END;
$$ LANGUAGE plpgsql;
-- POWER_LINE_NUMB_UNKNOWN_CABLE_LEV
-- Gibt die Anzahl der noch unbekannten cable-levels zurück
CREATE OR REPLACE FUNCTION otg_numb_unknown_cables_lev (v_id BIGINT) RETURNS INT
AS $$
DECLARE
v_numb_known_cable_lev INT;
BEGIN
v_numb_known_cable_lev := (SELECT (NOT cables_array [1] IS NULL)::int
+ (NOT cables_array [2] IS NULL)::int
+ (NOT cables_array [3] IS NULL)::int
+ (NOT cables_array [4] IS NULL)::int
FROM power_line
WHERE id = v_id);
RETURN (SELECT (numb_volt_lev - v_numb_known_cable_lev) FROM power_line WHERE id = v_id); --Die Anzahl der Spannungsebenen minus Anzahl der bekannten Cable-Level sind die noch unbekannten...
END;
$$ LANGUAGE plpgsql;
-- POWER_LINE_NUMB_UNKNOWN_FREQ_LEV
-- Gibt die Anzahl der noch unbekannten frequency-levels zurück
CREATE OR REPLACE FUNCTION otg_numb_unknown_freq_lev (v_id BIGINT) RETURNS INT
AS $$
DECLARE
v_numb_known_freq_lev INT;
BEGIN
v_numb_known_freq_lev := (SELECT (NOT frequency_array [1] IS NULL)::int
+ (NOT frequency_array [2] IS NULL)::int
+ (NOT frequency_array [3] IS NULL)::int
+ (NOT frequency_array [4] IS NULL)::int
FROM power_line
WHERE id = v_id);
--Die Anzahl der Spannungsebenen minus Anzahl der bekannten Frequency-Level sind die noch unbekannten...
RETURN (SELECT (numb_volt_lev - v_numb_known_freq_lev) FROM power_line WHERE id = v_id);
END;
$$ LANGUAGE plpgsql;
-- ALL_FREQ_LIKE
-- Bekommt voltage_array und frequency_array und gewünschte Frequenz und untersucht,
--...ob alle Frequenzen vergeben sind und = gewünschte Frequenz sind.
-- Dann wird true returnt. sonst false.
CREATE OR REPLACE FUNCTION otg_all_freq_like ( v_voltage_array INT [],
v_frequency_array REAL [],
v_freq REAL) RETURNS BOOLEAN
AS $$
BEGIN
IF v_voltage_array IS NULL THEN RETURN FALSE; END IF;
FOR i IN 1..array_length(v_voltage_array, 1)
LOOP
-- Falls Spannungs-level vorhanden UND frequenz NULL oder, nicht gleich v_freq, dann Return FALSE
IF NOT v_voltage_array [i] IS NULL AND
(v_frequency_array [i] IS NULL OR
v_frequency_array [i] != v_freq) THEN
RETURN FALSE; END IF;
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;
-- POWER_LINE_CHECK_CABLE_CONFLICT
--Guckt, ob es an einer Leitung zu einem CAble-Informationen-Konflikt kommt.
-- REturnt true, wenn entweder geeignete Nachbarn unterschiedliche cable-Informationen haben, oder...
--... falls alle spannungsebenen einen Cables-Eintrag haben dann true, wenn sie cable-Summen nicht stimmen
-- Zusammengefasst: Falls Summe nicht stimmt, oder Nachbarn verschiedene Infos!!
-- Nachbarinfo wird nicht untersucht, falls ein Ende innerhalb einer Substation liegt.
CREATE OR REPLACE FUNCTION otg_check_cable_conflict (v_id BIGINT) RETURNS BOOLEAN
AS $$
DECLARE
v_id_line RECORD;
v_cables_neighbour INT [2];
v_volt INT;
v_id_neighbour BIGINT;
v_lev_neighbour INT;
BEGIN
SELECT id, all_neighbours, point_substation_id, cables_sum, voltage_array, power INTO v_id_line FROM power_line WHERE id = v_id;
IF v_id_line.power = 'line' AND
otg_check_all_cables_complete (v_id) AND
v_id_line.cables_sum != otg_known_cables_sum (v_id)
THEN RETURN true; END IF; -- Zuerst wird untersucht, ob die Cable-Summe stimmt.
--IF (NOT v_id_line.point_substation_id [1] IS NULL) OR (NOT v_id_line.point_substation_id [2] IS NULL) THEN RETURN NULL; END IF; -- Wenn einer der Punke in einer Substation liegt, wird die Nachbarschafts-Cable-Abfrage nicht gestellt.
FOR i in 1..4 LOOP -- i ist Level!
v_volt := v_id_line.voltage_array [i]; --Spannung der Leitung an dem Leitungslevel
v_cables_neighbour := '{NULL, NULL}'; -- Array wird auf NULL gesetzt, damit keine werte von der alten Interation übernommen werden.
CONTINUE WHEN v_volt IS NULL; --Wenn es Spannungsebenen nicht gibt, dann nächsten Iterationsschritt
FOR j in 1..2 LOOP -- j ist loc (1 = start)
v_id_neighbour := v_id_line.all_neighbours [i][j][1][1];
CONTINUE WHEN (v_id_neighbour IS NULL) OR (NOT v_id_line.all_neighbours [i][j][2][1] IS NULL); -- In den nächsten Iterationsschritt gehen, wenn garkein Nachbar, oder mehr als einer
v_lev_neighbour := v_id_line.all_neighbours [i][j][1][2]; --Leitungs-Level auf der die Spannung zu finden ist.
v_cables_neighbour [j]:= (SELECT cables_array [v_lev_neighbour] FROM power_line
WHERE id = v_id_neighbour);
END LOOP;
IF v_cables_neighbour [1] != v_cables_neighbour [2]
THEN RETURN true; -- Wenn ein Fehler auf irgendeiner Spannungseben auftritt, dann wird true zurückgegeben.
END IF;
END LOOP;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- NACHBARSCHAFTSUNTERSUCHUNG
-- POWER_LINE_GET_ALL_NEIGHBOURS
-- Funktion sucht für die Angegeben Leitung (v_id) alle Nachbarn (start und Ende) pro Spannungsebene.
-- Diese wird als ID (bigint) ARRAY zurückgegeben
CREATE OR REPLACE FUNCTION otg_get_all_neighbours (v_id BIGINT) RETURNS BIGINT [][][][]
AS $$
DECLARE
v_all_neighbours BIGINT [4][2][10][2] := '{{{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}},
{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}}},
{{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}},
{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}}},
{{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}},
{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}}},
{{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}},
{{NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}, {NULL,NULL}}}}';
v_id_line RECORD;
v_volt INT ;
v_count INT := 0;
v_current_line RECORD;
v_lev INT [];
n INT; -- Spannungsebenen Variable der untersuchten Nachbarleitung
--v_no_frequency BOOLEAN;
BEGIN
SELECT id, startpoint, endpoint, voltage_array, frequency_array INTO v_id_line FROM power_line WHERE id = v_id;
FOR i IN 1..4 LOOP -- geht die Spannungsebenen durch
-- IF v_id_line.frequency_array [i] IS NULL THEN
-- v_no_frequency := true;
-- ELSE
-- v_no_frequency := false;
-- END IF;
v_volt := v_id_line.voltage_array [i]; -- Falls Spannungsebene i nicht vorhanden, brauch für diese kein Nachbar gesucht werden
CONTINUE WHEN v_volt IS NULL;
v_count := 0;
FOR v_current_line IN
SELECT id, power, voltage_array, frequency_array FROM power_line
WHERE
(st_equals (power_line.startpoint, v_id_line.startpoint) OR
st_equals (power_line.endpoint, v_id_line.startpoint)) --Start oder Endpunkt der gessuchten Leitung muss mit Startpunkt meiner Leitung überenstimmen
AND id != v_id_line.id -- Soll nicht sich selbst auswählen
AND NOT otg_array_search (v_volt, voltage_array) IS NULL -- Spannung muss in gesuchter Leitung (mindestens einmal) vorhanden sein vorhanden sein!
LOOP
v_lev := otg_array_search_2 (v_volt, v_current_line.voltage_array); -- Alle möglichen spannugns-Nachbarn der benachbarten Leitung (Array)
-- v_lev kann hier nicht NULL sein, da bereits als Bedingung für diese Schleife nicht NULL ist
FOREACH n IN ARRAY v_lev -- Soll durch die Werte des Arrays gehen
LOOP
-- Frequenz soll an dieser stelle (doch) nicht untersucht werden. Diese Abfrage muss bei
-- der konkreten Nachbarschaftsabfrage ohnehin noch gestellt werden und ist daher doppelt
-- IF v_current_line.frequency_array [n] IS NULL OR
-- v_id_line.frequency_array [i] IS NULL OR
-- v_current_line.frequency_array [n] = v_id_line.frequency_array [i]
-- Dann als Nachbarn anerkennen, wenn entweder eigene Frequenz oder Frequenz des Nachbarn noch unbekannt sind
-- ... oder beide Frequenzen gleich sind.
-- THEN
v_count := v_count + 1;
v_all_neighbours [i][1][v_count][1]:= v_current_line.id;
v_all_neighbours [i][1][v_count][2]:= n; -- Trägt im zweiten Feld ein, wo die Spannung zu finden ist.
-- END IF;
END LOOP;
END LOOP;
v_count := 0;
FOR v_current_line IN
SELECT id, power, voltage_array, frequency_array FROM power_line
WHERE
(st_equals (power_line.startpoint, v_id_line.endpoint) OR
st_equals (power_line.endpoint, v_id_line.endpoint)) --Start oder Endpunkt der gessuchten Leitung muss mit Startpunkt meiner Leitung überenstimmen
AND id != v_id_line.id -- Soll nicht sich selbst auswählen
AND NOT otg_array_search (v_volt, voltage_array) IS NULL -- Spannung muss in gesuchter Leitung vorhanden sein vorhanden sein!
LOOP
v_lev := otg_array_search_2 (v_volt, v_current_line.voltage_array); -- Alle möglichen spannugns-Nachbarn der benachbarten Leitung (Array)
-- v_lev kann hier nicht NULL sein, da bereits als Bedingung für diese Schleife nicht NULL ist
FOREACH n IN ARRAY v_lev -- Soll durch die Werte des Arrays gehen
LOOP
-- IF v_current_line.frequency_array [n] IS NULL OR
-- v_id_line.frequency_array [i] IS NULL OR
-- v_current_line.frequency_array [n] = v_id_line.frequency_array [i]
-- Dann als Nachbarn anerkennen, wenn entweder eigene Frequenz oder Frequenz des Nachbarn noch unbekannt sind
-- ... oder beide Frequenzen gleich sind.
--THEN
v_count := v_count + 1;
v_all_neighbours [i][2][v_count][1]:= v_current_line.id;
v_all_neighbours [i][2][v_count][2]:= n; -- Trägt im zweiten Feld ein, wo die Spannung zu finden ist.
--END IF;
END LOOP;
END LOOP;
END LOOP;
RETURN v_all_neighbours;
END;
$$
LANGUAGE plpgsql;
-- CABLE-ANNAHMEN
-- UNKNOWN_VALUE_HEURISTIC
--Führt alle Cable-Annahme Funktionen gemeinsam aus
CREATE OR REPLACE FUNCTION otg_unknown_value_heuristic () RETURNS void
AS $$
DECLARE
v_count_start INT;
v_count_end INT;
BEGIN
v_count_end:= (SELECT sum (otg_numb_unknown_cables_lev (id)) + sum (otg_numb_unknown_freq_lev (id))
FROM power_line);
LOOP
v_count_start := v_count_end;
PERFORM otg_3_cables_heuristic ();
PERFORM otg_neighbour_heuristic ();
PERFORM otg_sum_heuristic ();
-- Zählt alle insgesamt vorhandenen unknown cables
-- (sollte noch Frequenz abfragen)
v_count_end:= (SELECT sum (otg_numb_unknown_cables_lev (id)) + sum (otg_numb_unknown_freq_lev (id))
FROM power_line);
EXIT WHEN v_count_start - v_count_end = 0;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- otg_3_cables_heuristic
-- "3 cables rule" (Frequenzabhängig gültig)
-- Schreibt alle Cables = 3, wo noch nicht vergebene Cables aus Calbes_sum durch Anzahl noch nicht berücksichtigter Spannugnsebenen 3 ergibt!
-- (Wenn alle Frequenzen = 50hz sind)
CREATE OR REPLACE FUNCTION otg_3_cables_heuristic () RETURNS void
AS $$
DECLARE
v_line RECORD;
BEGIN
FOR v_line IN
SELECT id, voltage_array, cables_sum, frequency_array
FROM power_line
-- Annahme wird benötigt, wo cables noch nicht komplett sind und...
-- ...gilt nur für Freileitungen
WHERE otg_check_all_cables_complete (id) = false AND
power = 'line'
LOOP
-- Falls alle frequenzen bekannt sind und = 50 UND
-- die 3 calbes-rule erfüllt ist, dann cables = 3 schreiben
IF otg_all_freq_like (v_line.voltage_array, v_line.frequency_array, 50) AND
(v_line.cables_sum - otg_known_cables_sum (v_line.id))/otg_numb_unknown_cables_lev (v_line.id) = 3
THEN
FOR i IN 1..4
LOOP
-- Über all dort, wo cables noch unbekannt ist wird dann cables=3 geschrieben.
IF NOT otg_check_cable_complete (v_line.id, i) THEN
UPDATE power_line
SET cables_array [i] = 3 WHERE id = v_line.id;
END IF;
END LOOP;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- otg_neighbour_heuristic
-- Guckt alle Nachbarn (die in der Tabelle stehen) durch, ob geeignete Informationen enthalten.
-- Wenn ja, dann schreibe Cable-Informationen und Frequenz
-- Frequenz kann auch dann übernommen werden, wenn alle Nachbarn dieselbe Frequenz haben.
CREATE OR REPLACE FUNCTION otg_neighbour_heuristic () RETURNS void
AS $$
DECLARE
v_id_line RECORD;
v_cables_neighbour INT;
-- v_volt INT;
v_id_neighbour BIGINT;
--v_lev_neighbour INT;
BEGIN
-- In die Tabelle all_neighbours sollen pro Leitung und Spannungsebene alle Nachbarn gespeichert werden...
-- ...die anschließend untersucht werden.
CREATE TABLE all_neighbours (
id BIGINT,
cables INT,
frequency INT);
-- Geht alle Leitungen durch, die noch nicht komplett sind
FOR v_id_line IN
SELECT id, all_neighbours, voltage_array, cables_array, frequency_array
FROM power_line
-- An dieser Stelle reicht es nicht aus zu untersuchen, ob alle Cables vorhanden sind.
-- Auch ob alle Frequencys vorhanden sind müsste untersucht werden
-- Dies geschieht aber bereits am Anfang der 1. Schleife und sollte daher schnell genug sein.
LOOP
-- i geht alle Spannungs-levels durch
FOR i in 1..4
LOOP
-- Überspringen, falls Spannungs-Level nicht vorhanden...
--... Und/oder cables und Frequenz schon bekannt
CONTINUE WHEN v_id_line.voltage_array [i] IS NULL OR
(NOT v_id_line.cables_array [i] IS NULL AND
NOT v_id_line.frequency_array [i] IS NULL);
FOR j in 1..2 LOOP -- j stehen für Anfang und Ende der Leitung (1 = start)
-- Falls kein Nachbar vorhanden (erster Eintrag leer) dann diesen Schritt überspringen
CONTINUE WHEN v_id_line.all_neighbours [i][j][1][1] IS NULL;
-- Die folgende Schleife füllt die Tabelle all_neighbours
-- (Tabelle wird vorher geleert)
DELETE FROM all_neighbours;
FOR k in 1..10 LOOP --k ist "count" des Nachbarn (Auflistung)
-- soll mit dem Füllen der Tabelle aufhören, falls ein Eintrag NULL ist
-- (dann gibt es auch keine Nachfolgenden)
EXIT WHEN v_id_line.all_neighbours [i][j][k][1] IS NULL;
-- Füllt mit jedem k eine Zeile der Nachbartabelle
INSERT INTO all_neighbours ( id,
cables,
frequency)
SELECT id,
cables_array [v_id_line.all_neighbours [i][j][k][2]],
frequency_array [v_id_line.all_neighbours [i][j][k][2]]
FROM power_line
WHERE id = v_id_line.all_neighbours [i][j][k][1];
END LOOP;
-- Wenn die Frequenz der eigenen Leitung NULL ist ...
-- ... und die Frequenz aller Nachbarn gleich ist (count der Distincts = 1)
-- kann diese übernommen werden
IF v_id_line.frequency_array [i] IS NULL AND
(SELECT count (*) = 1
FROM (SELECT distinct on (frequency) frequency from all_neighbours) as freq)
THEN
UPDATE power_line
SET frequency_array [i] = (SELECT frequency FROM all_neighbours LIMIT 1)
WHERE id = v_id_line.id;
END IF;
IF NOT v_id_line.frequency_array [i] IS NULL
THEN
-- Alle Nachbarn mit nicht passender Frequenz werden gelöscht.
-- (frequency IS NULL bleibt dabei noch erhalten)
DELETE FROM all_neighbours
WHERE frequency != v_id_line.frequency_array [i];
END IF;
-- Alle überbleibenden könnten passende Nachbarn darstellen
-- Nur wenn die Tabelle an dieser Stelle genau eine Zeile hat...
-- ... kann eine Nachbarschaftsinformation abgerufen werden
CONTINUE WHEN (SELECT COUNT (*) FROM all_neighbours) != 1;
-- Aus Schleife ausbrechen, falls der (eine) Nachbar keinen Frequenzeintrag hat
-- (in diesem Fall können auch keine cables übernommen werden
CONTINUE WHEN (SELECT frequency FROM all_neighbours) IS NULL;
-- Wenn die Cables der eigenen Leitung NULL ist UND Calbes der einen Nacharleitung nicht NULL ist,
--... dann cables vom (einen) Nacharn übernehmen
IF v_id_line.cables_array [i] IS NULL AND
NOT (SELECT cables FROM all_neighbours) IS NULL
THEN
UPDATE power_line
SET cables_array [i] = (SELECT cables FROM all_neighbours)
WHERE id = v_id_line.id;
UPDATE power_line
SET cables_from_neighbour = true WHERE id = v_id_line.id;
END IF;
END LOOP;
END LOOP;
END LOOP;
DROP TABLE IF EXISTS all_neighbours;
END;
$$ LANGUAGE plpgsql;
-- otg_sum_heuristic
-- Überall, wo nur noch ein Cable-Level unbekannt ist und "cables_sum" nicht Null ist, wir über die Summe der letzte fehlende Cables-Eintrag vervollständigt.
-- Gilt Frequenzunabhängig
CREATE OR REPLACE FUNCTION otg_sum_heuristic () RETURNS void
AS $$
DECLARE
v_line RECORD;
v_cables_left INT;
BEGIN
FOR v_line IN
SELECT id, cables_sum
FROM power_line
WHERE otg_check_all_cables_complete (id) = false
AND power = 'line' -- Annahme gilt nur für Freileitungen und nicht vollständige cables
LOOP
-- Falls nur noch 1 Level unbekannt und cables_sum existiert
IF (otg_numb_unknown_cables_lev (v_line.id) = 1) AND (NOT v_line.cables_sum IS NULL)
THEN
-- Berechnet noch "übrige" cables
v_cables_left := v_line.cables_sum - otg_known_cables_sum (v_line.id);
FOR i IN 1..4
-- Geht alle level durch und findet so das noch unbekannte
LOOP
IF NOT otg_check_cable_complete (v_line.id, i) THEN
UPDATE power_line
SET cables_array [i] = v_cables_left WHERE id = v_line.id;