Clustering based on diverging and converging coordinates

Question

My dataset is in the format of:

point       timestamp           deviceid

I am trying to identify coordinates of cluster centers for the point column.

Each day, the points diverge in the morning (at an unknown time) AND converge in the evening (at an unknown time) for EACH cluster. The location of divergence and convergence is the same for each cluster, and this is what I have to calculate. For all days, this location remains constant.

I think DBSCAN can potentially be used, but the issue is that it will not help me in identifying diverging / converging coordinates.

Answer 1

This sounds like a timeseries+geospatial kind of question rather than just a pure spatial question. I'm guessing that this is GPS data and you're trying to locate the start/end points of repeated journeys. Kind of like finding the home/warehouse locations from multiple GPS tracks.

Seeing as how it's repetitive over each day I'd first create a CTE (Common table expression) that contains the track segments and then cluster those start/end locations for each track per day:

WITH tracks AS
(SELECT 
    device_id,
    date(timestamp) as date,
    ST_MakeLine(point ORDER BY timestamp) AS track 
FROM my_table
GROUP BY device_id, date(timestamp)
)
SELECT 
     device_id, 
     ST_ClusterDBSCAN(ST_StartPoint(track), eps := 0.5, minpoints := 5) over () AS start_cluster_id, 
     ST_StartPoint(track),
     ST_ClusterDBSCAN(ST_EndPoint(track), eps := 0.5, minpoints := 5) over () AS end_cluster_id, 
     ST_EndPoint(track)
FROM tracks

You could also split up the tracks CTE by speed, distance from start, gaps in time or something else. Will depend on your data. You might also want to combine start and end points into a single set for clustering. Whether or not this works for you would depend on what you're trying to do, and what dataset you're using. I'm just hoping that included some timeseries thinking here would help.