# Obtain the time when a linestring track leaves a defined radius around a point

I have a table of linestring tracks as:

``````tracks AS
(SELECT
imei,
st_setsrid(ST_MakeLine(points_geom ORDER BY time_created),4326) AS track from
subset_cr
GROUP BY imei
)
``````

I also have the following table with a couple of rows of data:

``````loc_of_interest
columns (loc_id          point_geom)
``````

For each location of interest, given tracks data of one day, how do I calculate the time when tracks within a defined radius of 50m LEAVE that radius and venture out.

Would I need to implement trajectory functions or is there another way to do this?

I do not have write access to the db, nor have I used these functions in the past.

If you have access to the `subset_cr` table you don't need to bother with tracks, so it would be a "find the first point more than 50m away from a loc_of_interest".

You want meters as units, so you can do some tricks with the Haversine formula or transform to a suitable UTM coordinate system first (my preferred option).

Pseudocode:

``````select time_created, points_geometry
from subset_cr, loc_of_interest
where st_distance(
st_transform(subset_cr.points_geom, <appropriate UTM EPSG>),
st_transform(loc_of_interest.point_geom, <appropriate UTM EPSG>)
) > 50
``````

The UTM approach works only if you can predict a suitable UTM CS for all your points (e.g. if you know all your locations are in Germany choose EPSG:25832).

edit: this needs tweaking to only get one result per IMEI, but the approach should be clear

• Thanks! surely I just have to update the first line of your code to "select imei, time_created, points_geometry " - and that will make it so that I get one result per IMEI right? Commented Oct 4, 2022 at 8:21
• Probably, you'll have to play around with the queries and the actual data. Commented Oct 4, 2022 at 12:13