I am new to PostGIS, and I am trying to estimate time spent in a location based off of interpolating time between GPS points (I am using PostgreSQL 12.2 and PostGIS 3.0).
I have GPS points stored with timestamps (With data columns: Latitude, Longitude, Date, and Time) of a path taken. The time intervals in between each point are not the same and they vary. I also have a polygon of interest through which the GPS path cuts (multiple times entering and leaving)- see image below for a conceptual example of the data with which I am working, with blue dots being the points and the red line being the hypothetical path entering and exiting the black polygon.
I'm interested in estimating the time spent in the polygon based off of the time represented by the segments inside the polygon (when a segment is partially in/partially out, I am aiming to calculate the time represented by the fraction that is in- I am accepting the assumption that a fraction of a segment corresponds to an equal fraction of time in between the points).
I know that ST_Intersection would simply return the section of the overall line within the polygon, but I don't believe I'd be able to just take the proportion of the length inside compared to the total length, because different segments represent different lengths of time (Please correct me if I would actually be able to use ST_Intersection in this case). It is straightforward when both the beginning and end points in a segment are within the polygon but less so when one is inside and the other is outside. My ideal output would be a sum of the lengths of time represented by segment/segment fractions within the polygon.
I have looked at:
- Converting GPS points with timestamp to polylines in PostgreSQL?
- How to interpolate GPS Positions in PostGIS
- Storing GPS tracks with timestamps in PostGIS
- Store GPS trace with timestamps, then interpolate location by time
but most of these seem to look at estimating location based off of time (e.g. predicting where location will be in between existing data points), whereas I sort of want to do the opposite.
Would it better to do calculations for individual pairs of points given the uneven time between each pair (i.e. compare fractions of segments via ST_LineLocatePoint and/or ST_LineSubstring) or would it be better to do as one linestring/trajectory?
Ideally, I am hoping to do this all within the database/PostgreSQL/PostGIS.