I have table with points that are gps coordinates for multiple vehicles.
I have another table of multiple polygons that the vehicles travel through.
I’ve managed to write a python script that filters the vehicles by returning only the points inside the polygons. Now I need to figure out the duration of each vehicles inside of each polygon.
Unfortunately, each vehicle enters and exits each polygon multiple times so I can’t simply work out the difference between the max and min timestamps as that would also include time spent outside the polygons.
Is anyone able to point me in the right direction for how I might achieve this, either using SQL on its own or in combination with Python?
One way I’ve thought of is, instead of only returning the points in polygons is to add a Boolean column based on whether the row is an entry inside/outside. I’m not sure how to write this SQL query though.
I’d then sort the table on timestamp and iterate through it to look for when that value changes.
I’ve also thought of converting to lines and finding the intersects but then I’d lose the timestamp value, which is essential to the whole operation.
Any ideas gratefully received.