Getting point that vehicle passed in particular location for first time using PostGIS?

Question

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.

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;

Answer 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;

Answer 2

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.