· 10 years ago · Sep 02, 2016, 07:28 PM
1-- For this file to run there must be an existing database with the following extensions already enabled on the database
2--CREATE EXTENSION postgis;
3
4DROP TABLE if exists gps;
5CREATE TABLE gps
6(
7 device_id integer NOT NULL,
8 date_time timestamp,
9 lat double precision,
10 lng double precision,
11 speed integer
12);
13
14
15INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:49:42','31.21705','-85.38008','0');
16INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:49:42','31.21705','-85.38008','0');
17INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:49:47','31.21705','-85.38008','0');
18INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:49:53','31.21705','-85.38008','0');
19INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:49:58','31.21705','-85.38008','0');
20INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:03','31.21705','-85.38008','0');
21INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:08','31.21705','-85.38008','0');
22INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:13','31.21705','-85.38008','0');
23INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:18','31.21705','-85.38008','0');
24INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:23','31.21705','-85.38008','0');
25INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:28','31.21705','-85.38008','0');
26INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:33','31.21705','-85.38008','0');
27INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:38','31.21705','-85.38008','0');
28INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:43','31.21705','-85.38008','0');
29INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:48','31.21705','-85.38008','0');
30INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:53','31.21705','-85.38008','0');
31INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:50:58','31.21705','-85.38008','0');
32INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:04','31.21705','-85.38008','0');
33INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:09','31.21705','-85.38008','0');
34INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:14','31.21702','-85.37977','10');
35INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:19','31.21701','-85.37946','12');
36INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:24','31.21713','-85.37925','8');
37INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:29','31.2173', '-85.3792', '7');
38INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:34','31.21736','-85.37931','3');
39INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:39','31.21735','-85.3795', '11');
40INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:44','31.21743','-85.37973','11');
41INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:49','31.21776','-85.37972','16');
42INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:54','31.21804','-85.37959','14');
43INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:51:59','31.21809','-85.37922','14');
44INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:04','31.21804','-85.37894','9');
45INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:09','31.21792','-85.3788', '9');
46INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:14','31.21759','-85.37884','20');
47INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:19','31.21704','-85.37887','30');
48INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:24','31.21636','-85.37889','35');
49INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:29','31.21563','-85.37891','35');
50INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:34','31.2149', '-85.37891','34');
51INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:39','31.2143', '-85.37897','28');
52INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:44','31.2138', '-85.37942','32');
53INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:49','31.21324','-85.37975','29');
54INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:54','31.21245','-85.3797', '38');
55INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:52:59','31.21165','-85.3797', '38');
56INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:04','31.21083','-85.37966','39');
57INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:09','31.21006','-85.37965','33');
58INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:14','31.20958','-85.37963','16');
59INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:19','31.20951','-85.37994','17');
60INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:24','31.20955','-85.38052','30');
61INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:29','31.20959','-85.38135','37');
62INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:34','31.20964','-85.38228','7');
63INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:39','31.20967','-85.3832', '39');
64INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:44','31.20971','-85.38413','38');
65INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:49','31.20973','-85.38505','40');
66INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:54','31.20973','-85.38591','30');
67INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:53:59','31.20974','-85.38632','0');
68INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:04','31.20974','-85.38635','0');
69INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:10','31.20973','-85.38643','12');
70INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:15','31.20973','-85.38694','26');
71INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:20','31.20974','-85.38771','33');
72INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:26','31.20976','-85.38869','36');
73INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:31','31.20978','-85.38953','33');
74INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:36','31.20981','-85.39031','32');
75INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:41','31.20985','-85.39105','30');
76INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:46','31.20991','-85.39179','32');
77INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:51','31.20998','-85.39254','30');
78INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:54:56','31.21002','-85.39297','9');
79INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:01','31.21002','-85.39301','0');
80INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:06','31.21002','-85.39301','0');
81INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:11','31.21002','-85.39301','0');
82INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:16','31.21002','-85.39301','0');
83INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:21','31.21002','-85.39301','0');
84INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:26','31.21002','-85.39301','0');
85INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:31','31.21002','-85.39301','0');
86INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:36','31.21002','-85.39301','0');
87INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:41','31.21002','-85.39301','0');
88INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:46','31.21002','-85.39301','0');
89INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:51','31.21002','-85.39307','9');
90INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:55:56','31.21001','-85.39349','0');
91INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:01','31.21002','-85.39419','32');
92INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:06','31.21002','-85.39494','32');
93INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:11','31.21003','-85.39573','33');
94INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:16','31.21003','-85.39652','33');
95INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:21','31.21002','-85.39724','25');
96INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:26','31.21003','-85.39754','2');
97INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:31','31.21003','-85.39753','0');
98INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:36','31.21003','-85.39753','0');
99INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:41','31.21003','-85.39753','0');
100INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:46','31.21003','-85.39753','0');
101INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:51','31.21','-85.39763', '2');
102INSERT INTO gps(device_id, date_time, lat, lng, speed) VALUES(1, '2016-06-09 08:56:56','31.20955','-85.3977','25');
103
104CREATE TYPE vehicle_drive_stats_3 AS
105 (
106 total_distance_mi double precision);
107
108CREATE OR REPLACE FUNCTION calc()
109 RETURNS SETOF vehicle_drive_stats_3 AS
110$BODY$
111
112 ;WITH CTE AS
113 (
114 SELECT date_time,
115 device_id,
116 speed,
117 lng,
118 lat,
119 row_number() OVER ( PARTITION BY device_id ORDER BY date_time ASC) as rn
120 FROM gps
121 )
122
123 SELECT
124 (sum(distance) / 1000) * 0.621371 as total_distance_mi
125
126 FROM
127 (SELECT t1.device_id,
128 NULLIF(t1.speed, 0) as speed,
129
130 CASE when t1.lat = 0 and t1.lng = 0
131 THEN 0
132 when t2.lat = 0 and t2.lng = 0
133 THEN 0
134 ELSE
135 ST_distance_sphere( ST_GeomFromText( 'POINT(' || t1.lng || ' ' || t1.lat || ')', 4326 ), ST_GeomFromText( 'POINT(' || t2.lng || ' ' || t2.lat || ')', 4326 ) )
136 END as distance
137
138 FROM CTE t1
139 INNER JOIN CTE t2 ON t1.device_id = t2.device_id AND t1.rn = t2.rn - 1) stats
140
141 group by device_id
142$BODY$ LANGUAGE sql;
143
144
145select * from calc();