Hi Thomas Colson,
That is an excellent idea!!!
If you think it makes sense for your use case, you can follow the steps provided in the blog post that I mentioned in my last comment to make it dynamic (i.e. showing the latest always) and get rid the nightly task to create a new table with last 7 days worth of day. Plus you can use time slider to see how they traveled as time passed.
In fact based on your idea (again thanks for that), I came up another solution to draw lines from those points.
Here are flight paths (when no time is provided)
... that are created from these points
The line layers has only two features instead of multiple features representing each segment of the polyline (which is btw something needed for some cases)
Now if you enable time slider, you can play back how these flights moved.
Here is the SQL (for SQL Server) that i used.
select
ROW_NUMBER() OVER (ORDER BY ident) as objectid,
ident,
geometry::STLineFromText
(
concat('LINESTRING (', coord, ')'),
4326
) as shape
from (
select ident,
string_agg
(
concat(shape.STX, ' ', shape.STY), ', '
) within group (order by clock)
as coord
from flightawarepositions
WHERE ::r:time
group by ident
) a
or a bit different version without subquery:
select ident,
objectid = ROW_NUMBER() OVER (ORDER BY ident),
shape = geometry::STLineFromText
(
concat
(
'LINESTRING (',
string_agg
(
concat(shape.STX, ' ', shape.STY),
', '
) within group (order by clock),
')'
),
4326
)
from flightawarepositions
WHERE ::r:time
group by ident
NB:
1. for ::r:time syntax, please ArcGIS Pro doc here.
2. this is a very quick solution that I tried. I haven't tested whether it will hit any limits when it comes to concatenating large string