SQL : How to determine if object in same location for >8 hours

user_8747154 0
Viewed 53

I want to know if an object has been in the same location for >8 hours. Any ideas how to derive that from this data sample? Thx

ObjectID DateTime Lat Lon
23 5/2/2021 12:00 40.11 -30.34
23 5/2/2021 16:00 40.11 -30.34
23 5/2/2021 23:00 40.11 -30.34
24 5/2/2021 12:00 40.11 -30.34
24 5/2/2021 16:00 40.11 -30.34
24 5/2/2021 23:00 39.88 -29.00
25 5/2/2021 12:00 40.11 -30.34
25 5/2/2021 16:00 39.88 -29.00
25 5/2/2021 23:00 40.11 -30.34

ObjectID 23 should be returned because it was in the same location >8 hours

ObjectID 24 should not be returned. It may have been in the same location >8 hours, but based on our data we cannot be sure.

ObjectID 24 should not be returned. The 12:00 & 23:00 locations are the same, but the object was somewhere else in between (16:00).

Update: This is in Snowflake

snowflake-cloud-data-platform latitude-longitude location sql
1 Answers

You can treat this as a gaps-and-islands problem and then aggregate to find the time where the lat/lon is the same:

select objectid, lat, lon
from (select t.*,
             row_number() over (partition by objectid order by datetime) as seqnum, 
             row_number() over (partition by objectid, lat, lon order by datetime) as seqnum_2
      from t
     ) t
group by objectid, lat, lon, (seqnum - seqnum_2)
having max(datetime) > min(datetime) + interval '8 hour';
user_11440350
Related