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;