LhvUxzhn

1CREATE OR REPLACE FUNCTION vehicletracks()
2      RETURNS void AS
3      $BODY$
4          DECLARE
5            vrstica1 RECORD;
6            vrstica2 RECORD;
7            track_number INT;
8          BEGIN
9        SET TIME ZONE 'UTC';
10
11        DROP TABLE IF EXISTS vehicletracks_tracks;
12        CREATE TABLE vehicletracks_tracks (
13          session character varying(32),
14          client_id character varying(32) NOT NULL,
15          vehicle_type smallint NOT NULL,
16          track_number SERIAL PRIMARY KEY,
17          track_distance DOUBLE PRECISION,
18          track_duration INTERVAL,
19          track_start TIMESTAMP WITH TIME ZONE,
20          track_stop TIMESTAMP WITH TIME ZONE
21        );
22
23        DROP VIEW IF EXISTS group_by_client_session_vehicle;
24        CREATE VIEW group_by_client_session_vehicle AS
25        SELECT DISTINCT client_id, session, vehicle_type
26        FROM roaduserspositions
27        GROUP BY client_id, session, vehicle_type
28        ORDER BY session, client_id, vehicle_type;
29
30        FOR vrstica1 IN (SELECT * FROM group_by_client_session_vehicle)
31          LOOP
32            DROP VIEW IF EXISTS velocity_difference_view;
33                DROP TABLE IF EXISTS difference_table;
34                CREATE TABLE difference_table AS
35                SELECT id, session, client_id, vehicle_type, geolocation, timestamp, time_difference, distance_difference
36                FROM (SELECT t.*, t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.session, t.client_id, t.vehicle_type, t.timestamp ASC) AS time_difference,
37              ST_Distance(geolocation, LAG(geolocation) OVER (ORDER BY t.session, t.client_id, t.vehicle_type, t.timestamp ASC)) AS distance_difference
38                      FROM (SELECT * 
39                            FROM timonwww_roaduserspositions
40                            WHERE client_id = vrstica1.client_id AND session = vrstica1.session AND vehicle_type = vrstica1.vehicle_type) AS t) AS tab
41                ORDER BY session, client_id, vehicle_type, timestamp;
42
43            CREATE VIEW velocity_difference_view AS (
44            SELECT *, distance_difference / EXTRACT(EPOCH FROM time_difference) AS velocity
45            FROM difference_table
46            WHERE EXTRACT(EPOCH FROM time_difference) > 0
47            UNION 
48            SELECT *, 0 AS velocity
49            FROM difference_table
50            WHERE EXTRACT(EPOCH FROM time_difference) = 0
51            ORDER BY session, client_id, vehicle_type, timestamp);
52
53            DROP TABLE IF EXISTS new_time_difference;
54            CREATE TABLE new_time_difference ( 
55                track_id INTEGER,
56                LIKE velocity_difference_view INCLUDING ALL);
57
58            track_number := 1;
59            INSERT INTO new_time_difference(track_id, id, session, client_id, vehicle_type, geolocation, timestamp, time_difference)
60                SELECT
61                CASE
62              WHEN EXTRACT(EPOCH FROM time_difference) < 300 AND velocity < 121 THEN track_number
63              ELSE track_number + 1
64            END AS track_id, id, session, client_id, vehicle_type, geolocation, timestamp, time_difference
65            FROM velocity_difference_view OFFSET 1;
66
67            track_number := 1;
68                FOR vrstica2 IN (SELECT * FROM velocity_difference_view OFFSET 1)
69              LOOP
70                IF EXTRACT(EPOCH FROM vrstica2.time_difference) < 300 AND vrstica2.velocity < 121 THEN
71                  INSERT INTO new_time_difference VALUES(track_number, vrstica2.id, vrstica2.session, vrstica2.client_id, vrstica2.vehicle_type, vrstica2.geolocation, vrstica2.timestamp, vrstica2.time_difference);
72                ELSE
73                  track_number := track_number + 1;
74                END IF;
75              END LOOP;
76
77                DROP TABLE IF EXISTS geolocation_difference_table;
78                CREATE TABLE geolocation_difference_table AS
79                SELECT *
80                FROM (SELECT *, ST_Distance(geolocation, LAG(geolocation) over (ORDER BY timestamp ASC)) as geolocation_difference
81                      FROM new_time_difference
82                      WHERE track_id IN (SELECT * FROM generate_series((SELECT MIN(new_time_difference.track_id) FROM new_time_difference), (SELECT MAX(track_id) FROM new_time_difference)) num)) AS tab;
83
84            DROP TABLE IF EXISTS track_distance_table;
85            CREATE TABLE track_distance_table AS
86            SELECT track_id, SUM(geolocation_difference) AS track_distance, SUM(time_difference) AS track_duration, MIN(timestamp) AS track_start, MAX(timestamp) AS track_stop
87            FROM geolocation_difference_table
88            GROUP BY track_id
89            ORDER BY track_id ASC;  
90
91            INSERT INTO vehicletracks_tracks(session, client_id, vehicle_type, track_distance, track_duration, track_start, track_stop) 
92            SELECT vrstica1.session, vrstica1.client_id, vrstica1.vehicle_type, track_distance, track_duration, track_start, track_stop
93            FROM (SELECT * FROM track_distance_table WHERE track_distance > 0 AND track_distance IS NOT NULL) AS t;
94
95          END LOOP;
96
97          DROP TABLE IF EXISTS vehicletracks_binding;
98          CREATE TABLE vehicletracks_binding AS
99          SELECT id, track_id
100          FROM geolocation_difference_table
101          ORDER BY track_id ASC;
102
103          DROP VIEW IF EXISTS group_by_client_session_vehicle;
104          DROP VIEW IF EXISTS velocity_difference_view;
105          DROP TABLE IF EXISTS difference_table;
106          DROP TABLE IF EXISTS new_time_difference;
107          DROP TABLE IF EXISTS geolocation_difference_table;
108          DROP TABLE IF EXISTS track_distance_table;
109
110          END;  
111      $BODY$
112      LANGUAGE plpgsql VOLATILE
113      COST 100;