# How to estimate time spent within a location using GPS/timestamp data in PostGIS?

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:

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.

• While possible, this might be a bit hairy for just SQL. The issue is prorating time by segment, assuming that each segment represents a differing time. Once you have time per segment, you can compute the sum of the fractions of each segment within the polygon sum((length within/segment length) * segment time). The key thing for GIS SE is that coding questions are expected to contain code, so you need to make an attempt to solve the SQL, and if it isn't working, we'll be able to give pointers. Apr 22 '20 at 13:47