The Problem
Just the other day, I was presented with this problem by a colleague of mine. He had a feature layer with 24 hours worth of archived flight data off an airport – where each feature represents a location of a flight at the time it was broadcast by the plane. In addition to location and timestamp, they have other attributes including an identifier – all features with the same identifier belongs to a single flight.
He was trying to replay a flight path using time slider in ArcGIS Pro, basically wanted to show the last known location of a flight at any given time. In that process, he ran into some issues where in some cases flight locations do not show up on the map and some other cases more than one locations are displayed for the same flight. The problem can be seen in the animation below.
Why?
To understand why this happens, you need to understand two things:
- Time slider’s behavior: when you play back a time slider, it moves the time window by a given time step. These two values are constant for the duration of a play back, and they are set by users.
- Nature of this type of time aware data: data like flight log are usually transmitted and recorded at irregular interval. See the attribute table above and you will notice that time differences between those features are not constant – they are about, but not exactly, 1 minute apart.
Since the time window is constant for the duration of the time slider play back but the time difference between those recorded features are not constant, you will run into these scenarios where there are more than one flight locations shown or nothing shown at all.
For example, if I set the Start time of the time slider to 6/27/2018 2:29:30 PM and Time Span to 1 minute and then play it, you will see that the plane is disappeared on the map the next minute.
This problem can be resolved by increasing the time span.
Let’s set the Time Span to 1.5 minute and you will see it works until 6/27/2018 2:31:30 PM when 2 locations appear on the map.
So, increasing the time span resolves my first plane-not-showing problem, but presents me with another.
If there was a way to show only the latest plane location, out of those two during that time duration, then that would solve my entire problem!! Therefore, in short, doing the following two thing should work as the solution:
- Make the time span large enough to avoid any gap (already done; checked)
- In case of multiple locations, pick the latest one. (let’s find out how in the next section)
Solutions
In theory, the solution to 2nd problem mention above should be simple, right? All I need to do is sort those locations using their timestamp in descending order and pick the top/first one.
How can this be achieved without any manual intervention?
As we know, sorting rows in specific order and picking the top one is something that can easily be done with SQL queries at any database.
But how can I create a layer based on a SQL query in ArcGIS Pro? The answer is query layer.
A query layer is a very powerful tool that allows you to use a sql statement as a source of a feature layer; as the map gets refreshed, the sql statement gets pushed to the database; it gets evaluated at the database level and results are returned to ArcGIS Pro. A SQL statement, that defines a query layer, could be as simple as SELECT * FROM aTable1 or very complex with multiple joins and may even contain spatial operations.
Note: Query layers only work against enterprise databases, does not work with FileGDB or any file based datasources such as shapefile, etc. For this blog, I have used SQL Server as my backend database and SQL statements contains some functions that are specific to SQL Server. Similar solution should be available for other databases as well to achieve the same goal.
Solution #1: Use SQL Server TOP * for single flight path:
If I want to replay only a single flight, it works fine and it is the simplest solution.
A sql like the following will do the job just fine.
SELECT TOP 1 * FROM FlightLog WHERE ident = ‘ENY4095’ and (clock >= ‘2018-06-27 14:31:30’ and clock <= ‘2018-06-27 14:33:00’) ORDER BY clock DESC
So, I created a query layer following steps from ‘Create a query layer‘ help topic and used the above sql as the source. The map shows the correct result now, no duplicate planes in the map.
As you noticed that the time query, i.e. (clock >= ‘2018-06-27 14:31:30’ and clock <= ‘2018-06-27 14:33:00’), is hard-coded in the layer source sql statement. Now, I need to make it work like a variable so that these timestamp values in the where clause will be updated based on the Time Slider’s current time window.
ArcGIS Pro 1.4 introduced range parameters that can be used exactly for this purpose. Following instructions provided in the help, I modified my query where the time query is replaced by a range variable – see ::r:time in the following sql statement.
SELECT TOP 1 * FROM FlightLog WHERE ident = ‘ENY4095’ and ::r:time ORDER BY clock DESC
Here is what the query layer definition looks like:
Once I updated my query layer with a range parameter of Date data type, the layer is made time aware automatically and time slider becomes enabled. Now I can step through the time and see it works as intended i.e. there is (a) no missing plane locations and (b) no multiple plane locations are showing up either.
Note: TOP * may not be supported by other databases such as Oracle, PostgreSQL – instead you can use row limiting clauses supported by those databases.
Solution #2: Use RANK() function for multiple flight paths:
As far as I know (not being a database expert) TOP * (or other row limiting clauses) only works with one flight path.
When it comes to replaying multiple flight paths, we need a different solution that will instruct the database to return the latest location for each flight.
In database term, it is like:
(a) find all rows matching the time query,
(b) group them based on unique values from a field,
(c) sort them in descending order by their timestamp and
(d) finally return the first/top row for each group.
Here is how the SQL statement looks (albeit a bit complicated) like to accomplish this.
SELECT * FROM (SELECT *, RANK() over (PARTITION BY ident ORDER BY clock DESC) AS r FROM FlightLog WHERE ::r:time ) a WHERE (r = 1)
After updating the query layer with this sql statement, the map now shows locations for both flights (#ENY4095 and #RPA3483) that I have stored in my FlightLog table.
Going beyond and showing previous locations
It is good that the problem is solved with SQL Server’s RANK() function. Even better news is that this RANK() function in fact can help solve another issue.
What I want to do now is to draw:
- previous locations along the flight path in addition to the latest location,
- and drawn previous locations using a different symbol like the map below
As you noticed what RANK() basically does is that it assigns an integer based ranking value to each row based on the sorting order – like it is showing in the Rank field in the picture below.
With that information all I need to do is to add a virtual field which will have ‘latest’ keyword for rows with Rank=1 and rest of them will be assigned with ‘previous’ keyword.
Doing this is in SQL is not a rocket science either – SQL Server IIF() function is our friend here. So, I (a) included IIF() function in the sql statement from the previous section and (b) removed (r = 1) from the where clause, and it looks like:
SELECT *, IIF( r = 1, ‘latest’, ‘previous’) as position FROM (SELECT *, RANK() over (PARTITION BY ident ORDER BY clock DESC) AS r FROM FlightLog WHERE ::r:time ) AS a
After updating the query layer with this modified sql statement, if you open the attribute table, you will see it now has an addition field named ‘Position’ containing either ‘latest’ or ‘previous’ keywords.
Next, all I have to do is use values from the ‘Position’ field to symbolize those locations differently. For that purpose I assigned a Unique Value symbology to this layer.
In addition to symbolizing them differently, I created a label class, with a sql query i.e. Position = ‘latest’, only to label the latest location with the flight number.
Also, I’d like to show all flight logged positions since these flights took off, for that I locked (or you can disable) the Start Time on the Time tab under Map.
Viola! I got a map that I can play back paths for two flights.
Share the map
Finally I can share this as a Map Image layer so that it can be consumed by others in a web map or a web application.
Note: I found the following video and site very helpful understanding the RANK function
- https://www.youtube.com/watch?v=5-La_uSNkKU
- http://codingsight.com/methods-to-rank-rows-in-sql-server-rownumber-rank-denserank-and-ntile/
Disclaimer: Data used in this article are downloaded from http://FlightAware.com. Flight locations were simply copied from flight aware flight log page, pasted in an Excel spreadsheet and after doing some manual formatting etc. on the data, they were imported into a SQL Server table.
Article Discussion: