Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- *,
- (speed - lag(speed) OVER (ORDER BY TIMESTAMP)) / greatest (time_delta, 0.5) AS acceleration
- FROM (
- SELECT
- *,
- distance / greatest (time_delta, 0.5) AS speed
- FROM (
- SELECT
- id,
- st_astext (LOCATION),
- TIMESTAMP,
- extract(epoch FROM (TIMESTAMP - lag(TIMESTAMP) OVER (ORDER BY TIMESTAMP))) AS time_delta,
- st_distance (LOCATION, lag(LOCATION, 1) OVER (ORDER BY TIMESTAMP ASC)) AS distance
- FROM
- trip_locations
- WHERE
- "tripId" = '917c12921202cf4d882b') s) s2;
- id | st_astext | timestamp | time_delta | distance | speed | acceleration
- -------+------------------------------------------+------------------------+------------+---------------+-------------------+-------------------
- 59861 | POINT(77.346113 28.5678738) | 2019-05-31 14:01:06+00 | | | |
- 59811 | POINT(77.346113 28.5678738) | 2019-05-31 14:01:06+00 | 0 | 0 | 0 |
- 59862 | POINT(77.346113 28.5678738) | 2019-05-31 14:01:10+00 | 4 | 0 | 0 | 0
- 59812 | POINT(77.346113 28.5678738) | 2019-05-31 14:01:10+00 | 0 | 0 | 0 | 0
- 59813 | POINT(77.346145 28.5678915) | 2019-05-31 14:01:16+00 | 6 | 3.69470263 | 0.615783771666667 | 0.102630628611111
- 59863 | POINT(77.346145 28.5678915) | 2019-05-31 14:01:16+00 | 0 | 0 | 0 | -1.23156754333333
- 59814 | POINT(77.3461537 28.5678963) | 2019-05-31 14:01:26+00 | 10 | 1.0037807 | 0.10037807 | 0.010037807
- 59864 | POINT(77.3461537 28.5678963) | 2019-05-31 14:01:26+00 | 0 | 0 | 0 | -0.20075614
- 59815 | POINT(77.3455433 28.571167) | 2019-05-31 14:01:36+00 | 10 | 367.37416806 | 36.737416806 | 3.6737416806
- 59865 | POINT(77.3455433 28.571167) | 2019-05-31 14:01:36+00 | 0 | 0 | 0 | -73.474833612
- 59866 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:24+00 | 48 | 1007.08923132 | 20.9810256525 | 0.43710470109375
- 59816 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:24+00 | 0 | 0 | 0 | -41.962051305
- 59817 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:34+00 | 10 | 0 | 0 | 0
- 59867 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:34+00 | 0 | 0 | 0 | 0
- 59818 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:45+00 | 11 | 0 | 0 | 0
- 59868 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:45+00 | 0 | 0 | 0 | 0
- 59869 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:56+00 | 11 | 0 | 0 | 0
- 59819 | POINT(77.3552335 28.5742323) | 2019-05-31 14:02:56+00 | 0 | 0 | 0 | 0
- 59870 | POINT(77.3552335 28.5742323) | 2019-05-31 14:03:07+00 | 11 | 0 | 0 | 0
- 59820 | POINT(77.3552335 28.5742323) | 2019-05-31 14:03:07+00 | 0 | 0 | 0 | 0
- 59821 | POINT(77.3552335 28.5742323) | 2019-05-31 14:03:09+00 | 2 | 0 | 0 | 0
- 59871 | POINT(77.3552335 28.5742323) | 2019-05-31 14:03:09+00 | 0 | 0 | 0 | 0
- 59822 | POINT(77.3552335 28.5742323) | 2019-05-31 14:03:16+00 | 7 | 0 | 0 | 0
- 59872 | POINT(77.3552335 28.5742323) | 2019-05-31 14:03:16+00 | 0 | 0 | 0 | 0
- 59873 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:29+00 | 13 | 194.27617952 | 14.9443215015385 | 1.14956319242604
- 59823 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:29+00 | 0 | 0 | 0 | -29.8886430030769
- 59874 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:38+00 | 9 | 0 | 0 | 0
- 59824 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:38+00 | 0 | 0 | 0 | 0
- 59875 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:48+00 | 10 | 0 | 0 | 0
- 59825 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:48+00 | 0 | 0 | 0 | 0
- 59826 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:52+00 | 4 | 0 | 0 | 0
- 59876 | POINT(77.356669 28.5754435) | 2019-05-31 14:03:52+00 | 0 | 0 | 0 | 0
- 59877 | POINT(77.356669 28.5754435) | 2019-05-31 14:04:07+00 | 15 | 0 | 0 | 0
- 59827 | POINT(77.356669 28.5754435) | 2019-05-31 14:04:07+00 | 0 | 0 | 0 | 0
- 59828 | POINT(77.3609754 28.584098) | 2019-05-31 14:04:31+00 | 24 | 1047.61396262 | 43.6505817758333 | 1.81877424065972
- 59878 | POINT(77.3609754 28.584098) | 2019-05-31 14:04:31+00 | 0 | 0 | 0 | -87.3011635516667
- 59879 | POINT(77.3609754 28.584098) | 2019-05-31 14:04:39+00 | 8 | 0 | 0 | 0
- 59829 | POINT(77.3609754 28.584098) | 2019-05-31 14:04:39+00 | 0 | 0 | 0 | 0
- 59830 | POINT(77.3609754 28.584098) | 2019-05-31 14:04:49+00 | 10 | 0 | 0 | 0
- 59880 | POINT(77.3609754 28.584098) | 2019-05-31 14:04:49+00 | 0 | 0 | 0 | 0
- 59881 | POINT(77.3609754 28.584098) | 2019-05-31 14:05:00+00 | 11 | 0 | 0 | 0
- 59831 | POINT(77.3609754 28.584098) | 2019-05-31 14:05:00+00 | 0 | 0 | 0 | 0
- 59832 | POINT(77.364564 28.5808496) | 2019-05-31 14:05:12+00 | 12 | 502.85059783 | 41.9042164858333 | 3.49201804048611
- 59882 | POINT(77.364564 28.5808496) | 2019-05-31 14:05:12+00 | 0 | 0 | 0 | -83.8084329716667
- 59833 | POINT(77.3714771686553 28.5864011726527) | 2019-05-31 14:05:45+00 | 33 | 914.29525511 | 27.7059168215151 | 0.839573237015611
- 59883 | POINT(77.3714771686553 28.5864011726527) | 2019-05-31 14:05:45+00 | 0 | 0 | 0 | -55.4118336430303
- 59884 | POINT(77.3729648362555 28.5874873972421) | 2019-05-31 14:06:09+00 | 24 | 188.86859014 | 7.86952458916667 | 0.327896857881944
- 59834 | POINT(77.3729648362555 28.5874873972421) | 2019-05-31 14:06:09+00 | 0 | 0 | 0 | -15.7390491783333
- 59885 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:19+00 | 10 | 158.79632343 | 15.879632343 | 1.5879632343
- 59835 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:19+00 | 0 | 0 | 0 | -31.759264686
- 59836 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:20+00 | 1 | 0 | 0 | 0
- 59886 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:20+00 | 0 | 0 | 0 | 0
- 59887 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:22+00 | 2 | 0 | 0 | 0
- 59837 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:22+00 | 0 | 0 | 0 | 0
- 59888 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:30+00 | 8 | 0 | 0 | 0
- 59838 | POINT(77.3717408 28.5865463) | 2019-05-31 14:06:30+00 | 0 | 0 | 0 | 0
- 59839 | POINT(77.3690829 28.5865521) | 2019-05-31 14:06:30+00 | 0 | 260.00752225 | 520.0150445 | 1040.030089
- 59889 | POINT(77.3690829 28.5865521) | 2019-05-31 14:06:30+00 | 0 | 0 | 0 | -1040.030089
- 59840 | POINT(77.3690829 28.5865521) | 2019-05-31 14:06:39+00 | 9 | 0 | 0 | 0
- 59890 | POINT(77.3690829 28.5865521) | 2019-05-31 14:06:39+00 | 0 | 0 | 0 | 0
- 59841 | POINT(77.3690829 28.5865521) | 2019-05-31 14:06:55+00 | 16 | 0 | 0 | 0
- 59892 | POINT(77.3796347 28.583344) | 2019-05-31 14:06:55+00 | 0 | 1091.75436154 | 2183.50872308 | 4367.01744616
- 59891 | POINT(77.3690829 28.5865521) | 2019-05-31 14:06:55+00 | 0 | 1091.75436154 | 2183.50872308 | 0
- 59842 | POINT(77.3796347 28.583344) | 2019-05-31 14:06:55+00 | 0 | 1091.75436154 | 2183.50872308 | 0
- 59893 | POINT(77.3733748 28.5873246) | 2019-05-31 14:07:04+00 | 9 | 754.73960575 | 83.8599561944445 | -233.294307431728
- 59843 | POINT(77.3733748 28.5873246) | 2019-05-31 14:07:04+00 | 0 | 0 | 0 | -167.719912388889
- 59844 | POINT(77.374563 28.5887776) | 2019-05-31 14:07:15+00 | 11 | 198.60066532 | 18.0546059381818 | 1.64132781256198
- 59894 | POINT(77.374563 28.5887776) | 2019-05-31 14:07:15+00 | 0 | 0 | 0 | -36.1092118763636
- 59895 | POINT(77.3760375 28.5908438) | 2019-05-31 14:07:21+00 | 6 | 270.63454105 | 45.1057568416667 | 7.51762614027778
- 59845 | POINT(77.3760375 28.5908438) | 2019-05-31 14:07:21+00 | 0 | 0 | 0 | -90.2115136833333
- 59896 | POINT(77.3753181 28.5915488) | 2019-05-31 14:07:32+00 | 11 | 105.15301194 | 9.55936472181818 | 0.869033156528926
- 59846 | POINT(77.3753181 28.5915488) | 2019-05-31 14:07:32+00 | 0 | 0 | 0 | -19.1187294436364
- 59847 | POINT(77.3751446 28.5916944) | 2019-05-31 14:07:41+00 | 9 | 23.41860417 | 2.60206713 | 0.28911857
- 59897 | POINT(77.3751446 28.5916944) | 2019-05-31 14:07:41+00 | 0 | 0 | 0 | -5.20413426
- 59848 | POINT(77.3742974 28.5925931) | 2019-05-31 14:07:52+00 | 11 | 129.56997072 | 11.7790882472727 | 1.07082620429752
- 59898 | POINT(77.3742974 28.5925931) | 2019-05-31 14:07:52+00 | 0 | 0 | 0 | -23.5581764945455
- 59849 | POINT(77.3742974 28.5925931) | 2019-05-31 14:08:19+00 | 27 | 0 | 0 | 0
- 59899 | POINT(77.3742974 28.5925931) | 2019-05-31 14:08:19+00 | 0 | 0 | 0 | 0
- 59900 | POINT(77.3720905 28.5973209) | 2019-05-31 14:08:33+00 | 14 | 566.70366797 | 40.4788334264286 | 2.8913452447449
- 59850 | POINT(77.3720905 28.5973209) | 2019-05-31 14:08:33+00 | 0 | 0 | 0 | -80.9576668528571
- 59851 | POINT(77.3719218 28.5972375) | 2019-05-31 14:08:44+00 | 11 | 18.91367767 | 1.71942524272727 | 0.156311385702479
- 59901 | POINT(77.3719218 28.5972375) | 2019-05-31 14:08:44+00 | 0 | 0 | 0 | -3.43885048545454
- 59902 | POINT(77.3719251 28.597237) | 2019-05-31 14:08:54+00 | 10 | 0.32750906 | 0.032750906 | 0.0032750906
- 59852 | POINT(77.3719251 28.597237) | 2019-05-31 14:08:54+00 | 0 | 0 | 0 | -0.065501812
- (84 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
