Not sure of the etiquette here, but I am reposting this question on the ArcGIS Pro feed, as I didn't get any responses under the ArcMap heading.
************
Hi-- I want to reduce the number of points in vessel tracklines based on a time interval from a datetimestamp value. I can reduce the number of points based on distance using the 'Delete Identical' tool, but I'd like to be able to reduce the number of points by selecting points based on a user defined interval, like a two or five minute interval, and culling the extraneous points. I have not found any obvious way to do this with the built in tools in ArcMap or Pro, but I'm hoping someone else has worked through this and can suggest a workflow or solution. I am still more comfortable working in ArcMap, but can work in Pro if the solution lies there.
Here is simplified version of what the tabular data look like. The highlighted rows show what records would be selected if I chose four minute interval. It makes sense that some rounding of the times may be needed to standardize the time breaks.
Here is what the desired output might look like.
Thanks!
Solved! Go to Solution.
I somehow stumbled on this post from late last year. I thought I should provide another solution to this problem for folks from the future 🙂
Let's take a look at the output first:
Black dots represents the original vessel's positions in the screenshot below. Red circles, off a different layer, show positions with some temporal gaps. Imagine you group the original locations (black dots) into bins/buckets of 5 min interval. Then pick up first ones (red circles) from each group. That is what I did here.
How to:
select objectid, datetimestamp, mmsi, latitude, longitude, shape from
(
select *, rank() over (partition by elapsed_time_group order by datetimestamp) as rnk
from
(
select *,
DATEDIFF(second, FIRST_VALUE(datetimestamp) over(order by datetimestamp), datetimestamp) / 300 as elapsed_time_group
from VESSELTRACK_MMSIPOINTS
) a
) b where b.rnk = 1
Problem with this SQL is that it produces a result where a few positions are still very close together (see the screenshots below). That is because positions are not recorded at a regular frequency.
To weed out those points too, I have added an additional filter in the SQL - see below.
select objectid, datetimestamp, mmsi, latitude, longitude, shape
from
(
select *, datediff(minute, lag(datetimestamp) over (order by datetimestamp), datetimestamp) as time_diff
from
(
select objectid, datetimestamp, mmsi, latitude, longitude, shape from
(
select *, rank() over (partition by elapsed_time_group order by datetimestamp) as rnk
from
(
select *,
DATEDIFF(second, FIRST_VALUE(datetimestamp) over(order by datetimestamp), datetimestamp) / 300 as elapsed_time_group
from VESSELTRACK_MMSIPOINTS
) a
) b where b.rnk = 1
) c
) d
where d.time_diff > 2 or d.time_diff is null
here is the output where green circles represents a cleaner version of the previous one.
Hope some folks might find this helpful in the future 🙂