3

I need some help setting up a query whith PostGIS.

I have a table where the tracking of a vehicle is recorded (points and timestamp).

How do I write a query to return the timestamp in which the vehicle first passed a certain point, and it may pass more than once?

It is not guaranteed that it passed exactly over the point by varying the accuracy of the GPS.

illustration

My records have these fields:

track_id, timestamp, latlng, speed, accuracy

When there was no possibility of 2 passes in the same place, I used something like this:

SELECT
    timestamp
FROM
    tracking
WHERE
   ST_DWITHIN(
        ST_Transform(ST_GeomFromText('POINT(16.920431 -19.662602)', 4326), 26986), 
        ST_Transform(latlng::geography::geometry, 26986), 
        100
   )
ORDER BY
    ST_Distance(
        latlng::geography::geometry,
        ST_GeomFromText('POINT(16.920431 -19.662602)', 4326)
    )
LIMIT 1;
  • Can you tell us more about the structure of your tracking table? – Jochen Schwarze Jul 24 at 5:20
  • Order by timestamp instead of distance – JGH Jul 24 at 12:23
  • @JGH If I simply sort by timestamp I will get the first record within the defined perimeter. This is usually not the nearest point. If I decrease the perimeter, I risk catching nothing. – TaoTao Jul 24 at 13:18
  • @JochenSchwarze fields in table: track_id, timestamp, latlng, speed, accuracy – TaoTao Jul 24 at 14:32
1

One would need to optimize a combination of parameters: distance and time.

  • finding only the closest point could lead to a 2nd pass point
  • finding only the earliest point could lead to a unnecessarily far await point

You would need to find a distance threshold under which any point are considered being at an equal distance (ex: a GPS point 1cm away from another could be assumed to be at the same distance from the reference point. Or 10 cm, or 1 km... it depends on your use-case!).

The query would then order by discrete distance, and in the nearest one, find the oldest point.

The simplest way is to round the found distance. Since computing/comparing distances in degrees is a bad idea (a degree of longitude has a different ground length than a degree of latitude), you can cast to geography that relies on meters or use an appropriate CRS.

The following query computes the distance between each point and the reference point, then group distances by 1 meter and find the oldest one in the closest group of point.

ORDER BY
    round(ST_Distance(
        latlng::geography,
        ST_GeogFromText('POINT(16.920431 -19.662602)', 4326)
    ),1), timestamp
LIMIT 1;
  • I don't know if I get it right, but in your suggestion, if in the 2nd pass there is a point closer than in the 1st, it will get caught.Right? – TaoTao Jul 25 at 1:57
  • Yes and no. You need to decide on the threshold distance: if you choose 10 meters, then if there is a point 11m away in the 1st pass, and 9 meters away in the 2nd, then the 2nd will be kept. // If the point is 9 meters away in the 1st pass and 1 meter away in the 2nd, since both are less than 10m away, the 1st one will be kept – JGH Jul 25 at 12:17
0

Try using ST_MakeLine to turn your points into line data. There is a good example of how to do this in the linked documentation.

Once you've got your lines, you can then run ST_Buffer with a distance equal to the spatial error in your GPS data.

Lastly, you can run ST_Intersection on the buffer to determine overlaps. This question gives a good outline on how to perform this on all the geometries within the same table. When you perform the intersection, set the timestamp field to take the lowest value.

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.