3

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.

enter image description here

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.

Improve this question
1
  • 2
    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.
    – Vince
    Apr 22 '20 at 13:47
3

As has beeen noted in comments, this is a hairy issue for SQL.

Your best bet is probably to use the line-based context annotation approach for trajectories as described in Westermeier (2018) page 38

Improve this answer
1
  • I just can't thank you enough for providing this link!!
    – akamuza
    Sep 2 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.