1

I have a attribute table that looks like this. I want to calculate the difference in time and distance between consecutive rows. 

fid  car_id   lng       lat         time
1    57       4.932413  52.372022   2018-03-04 06:21:12
2    57       4.932533  52.372323   2018-03-04 06:21:41
3    57       4.932553  52.372994   2018-03-04 06:22:22
4    57       4.932663  52.372312   2018-03-04 06:23:15
5    57       4.932333  52.372582   2018-03-04 06:24:17
6    57       4.932213  52.372693   2018-03-04 06:24:53
7    26       4.932223  52.372343   2018-03-04 06:25:11
8    26       4.932413  52.372372   2018-03-04 06:25:48
9    26       4.932103  52.372952   2018-03-04 06:26:16
10   26       4.932223  52.372912   2018-03-04 06:26:30

Eventually I would like to have something like this with distance difference in meters and difference in time in seconds. Any advice on how to do this in QGIS?

fid  car_id   lng       lat         time                   distance_diff    time_diff
1    57       4.932413  52.372022   2018-03-04 06:21:12    0                0
2    57       4.932533  52.372323   2018-03-04 06:21:41    47               20
3    57       4.932553  52.372994   2018-03-04 06:22:22    29               41
4    57       4.932663  52.372312   2018-03-04 06:23:15    58               53
5    57       4.932333  52.372582   2018-03-04 06:24:17    46               62
6    57       4.932213  52.372693   2018-03-04 06:24:53    56               36
7    26       4.932223  52.372343   2018-03-04 06:25:11    38               18
8    26       4.932413  52.372372   2018-03-04 06:25:48    60               37
9    26       4.932103  52.372952   2018-03-04 06:26:16    21               28
10   26       4.932223  52.372912   2018-03-04 06:26:30    31               14
Vince
  • 20,017
  • 15
  • 45
  • 64
winecity
  • 539
  • 2
  • 7

1 Answers1

2

(1) To calculate the time_diff field, try this expression:

second(age(attribute($currentfeature, 'time'), 
           attribute(get_feature_by_id('your_layername',$id-1), 'time')))

(Please change the 'your_layername' part to your actual layername).

enter image description here

If you are unhappy with NULL returned for the first row ('fid' = 1), wrap it in a conditional statement, like:

CASE WHEN fid=1 THEN 0
ELSE
second(age(attribute($currentfeature, 'time'), 
           attribute(get_feature_by_id('your_layername',$id-1), 'time')))
END

(2) To calculate the distance_diff, you may find another post How to calculate the distance between points (pointlayer) useful. However, you will need to find a best CRS for your area. As you are working in relatively high latitude, UTM may not give you an accurate distance.

Kazuhito
  • 30,746
  • 5
  • 69
  • 149
  • Thanks for sharing your code. Unfortunately the code generates a column with only NULL values. I did change 'your_layername' to my actual layername.. Any idea what the problem might me? – winecity Mar 04 '20 at 21:28
  • I think it had something to do with the fid column. I manually added a new $id column and it works now. Thanks! – winecity Mar 04 '20 at 21:54
  • Export layer to MS Excel and calculate difference. Then link layer with excel table with ID field, and import difference in time. – Frodo Mar 05 '20 at 06:20
  • So I got this perfectly working when using the field calculator outside the graphic modeler. However, when I want to calculate the time difference with the field calculator in a graphical model, it does not work.. It only generates NULL values. Any ideas what the problem is? – winecity Mar 26 '20 at 15:07