{"id":261312,"date":"2018-07-01T14:20:46","date_gmt":"2018-07-01T21:20:46","guid":{"rendered":"http:\/\/www.esri.com\/arcgis-blog\/?post_type=blog&p=261312"},"modified":"2021-11-11T15:47:17","modified_gmt":"2021-11-11T23:47:17","slug":"play-back-flight-data-using-time-slider-and-query-layer-in-arcgis-pro","status":"publish","type":"blog","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/arcgis-pro\/mapping\/play-back-flight-data-using-time-slider-and-query-layer-in-arcgis-pro","title":{"rendered":"Play back flight data using time slider and query layer in ArcGIS Pro"},"author":5971,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","format":"standard","meta":{"_acf_changed":false,"_searchwp_excluded":""},"categories":[22941],"tags":[146902,145432,28041,25511,27591],"industry":[],"product":[36561],"class_list":["post-261312","blog","type-blog","status-publish","format-standard","hentry","category-mapping","tag-archived","tag-query-layer","tag-time","tag-time-slider","tag-time-aware","product-arcgis-pro"],"acf":{"short_description":"This article shows how a query layer with database functions can used to replay archived location data collected at irregular intervals.","flexible_content":[{"acf_fc_layout":"content","content":"

The Problem<\/strong><\/h2>\n

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.<\/p>\n

\"\"<\/p>\n

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.\u00a0In 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.<\/p>\n"},{"acf_fc_layout":"image","image":{"ID":261702,"id":261702,"title":"FlightTrack_Animation_Problem_with_timeslider","filename":"FlightTrack_Animation_Problem_with_timeslider.gif","filesize":30030,"url":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/arcgis-pro\/mapping\/play-back-flight-data-using-time-slider-and-query-layer-in-arcgis-pro\/flighttrack_animation_problem_with_timeslider","alt":"","author":"5971","description":"","caption":"","name":"flighttrack_animation_problem_with_timeslider","status":"inherit","uploaded_to":261312,"date":"2018-06-30 04:25:54","modified":"2018-06-30 04:25:54","menu_order":0,"mime_type":"image\/gif","type":"image","subtype":"gif","icon":"https:\/\/www.esri.com\/arcgis-blog\/wp-includes\/images\/media\/default.png","width":772,"height":475,"sizes":{"thumbnail":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","thumbnail-width":213,"thumbnail-height":131,"medium":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","medium-width":424,"medium-height":261,"medium_large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","medium_large-width":768,"medium_large-height":473,"large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","large-width":772,"large-height":475,"1536x1536":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","1536x1536-width":772,"1536x1536-height":475,"2048x2048":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","2048x2048-width":772,"2048x2048-height":475,"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","card_image-width":756,"card_image-height":465,"wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2018\/06\/FlightTrack_Animation_Problem_with_timeslider.gif","wide_image-width":772,"wide_image-height":475}},"image_position":"center","orientation":"horizontal","hyperlink":""},{"acf_fc_layout":"content","content":"

Why?<\/strong><\/h2>\n

To understand why this happens, you need to understand two things:<\/p>\n

    \n
  1. 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<\/strong> for the duration of a play back, and they are set by users.<\/li>\n
  2. Nature of this type of time aware data: data like flight log are usually transmitted and recorded at irregular<\/strong> 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.<\/li>\n<\/ol>\n

    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.<\/p>\n

    For example, if I set the Start time of the time slider to 6\/27\/2018 2:29:30 PM\u00a0and Time Span to 1 minute and then play it, you will see that the plane is disappeared on the map the next minute.<\/p>\n

    \"\"<\/p>\n

    \"\"<\/p>\n

    This problem can be resolved by increasing the time span.<\/p>\n

    Let’s\u00a0 set the Time Span to 1.5 minute and you will see it works until 6\/27\/2018 2:31:30 PM when\u00a02 locations appear on the map.<\/p>\n

    \"\"<\/p>\n

    So, increasing the time span resolves my first plane-not-showing problem, but presents me with another.<\/p>\n

    If there was a way to show only the latest plane location,<\/span> 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:<\/p>\n

      \n
    1. Make the time span large enough to avoid any gap (already done; checked<\/em>)<\/li>\n
    2. In case of multiple locations, pick the latest one. (let’s find out how in the next section<\/em>)<\/li>\n<\/ol>\n

       <\/p>\n"},{"acf_fc_layout":"content","content":"

      Solutions<\/strong><\/h2>\n

      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.<\/p>\n

      \"\"<\/p>\n

      How can this be achieved without any manual intervention?<\/p>\n

      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.<\/p>\n

      But how can I create a layer based on a SQL query in ArcGIS Pro? The answer is query layer<\/a>.<\/p>\n

      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<\/code>\u00a0or very complex with multiple joins and may even contain spatial operations.<\/p>\n

      Note<\/strong>: Query layers only work against enterprise databases, does not work with FileGDB or any file based datasources such as shapefile, etc.\u00a0For 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.<\/em><\/p>\n"},{"acf_fc_layout":"content","content":"

      Solution #1: Use SQL Server TOP *<\/a>\u00a0for single flight path:<\/h3>\n

      If I want to replay only a single flight, it works fine and it is the simplest solution.<\/p>\n

      A sql like the following will do the job just fine.<\/p>\n

      SELECT TOP 1 * FROM FlightLog WHERE ident = ‘ENY4095’ and (clock >= ‘2018-06-27 14:31:30’ and clock <= ‘2018-06-27 14:33:00’)\u00a0ORDER BY clock DESC<\/p><\/blockquote>\n

      So, I created a query layer following steps from ‘Create a query layer<\/a>‘ help topic and used the above sql as the source. The map shows the correct result now, no duplicate planes in the map.<\/p>\n

      \"\"<\/p>\n

      As you noticed that the time query, i.e. (clock >= ‘2018-06-27 14:31:30’ and clock <= ‘2018-06-27 14:33:00’)<\/em>, 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<\/span> will be updated based on the Time Slider’s current time window.<\/p>\n

      ArcGIS Pro 1.4 introduced\u00a0range parameters<\/a> that can be used exactly for this purpose. Following instructions provided in the help<\/a>, I modified my query where the time query is replaced by a range variable – see ::r:time<\/strong> in the following sql statement.<\/p>\n

      SELECT TOP 1 * FROM FlightLog WHERE ident = ‘ENY4095’ and ::r:time<\/strong> ORDER BY clock DESC<\/p><\/blockquote>\n

      Here is what the query layer definition looks like:
      \n\"\"<\/p>\n

      Once I updated my query layer with a range parameter of Date<\/strong> 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.<\/p>\n

      \"\"<\/p>\n

      Note<\/strong>: TOP * may not be supported by other databases such as Oracle, PostgreSQL – instead you can use row limiting clauses<\/em> supported by those databases.<\/p>\n"},{"acf_fc_layout":"content","content":"

      Solution #2: Use RANK()<\/a> function for multiple flight paths:<\/h3>\n

      As far as I know (not being a database expert<\/em>)\u00a0TOP * (or other row limiting clauses) only works with one flight path.<\/p>\n

      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<\/span>.<\/p>\n

      In database term, it is like:<\/em><\/p>\n

      (a) find all rows matching the time query,
      \n<\/em>(b) group them based on unique values from a field,
      \n(c) sort them in descending order by their timestamp and
      \n(d) finally return the first\/top row for each group<\/em>.<\/p>\n

      Here is how the SQL statement looks (albeit a bit complicated<\/em>) like to accomplish this.<\/p>\n

      SELECT * FROM (SELECT *, RANK() over (PARTITION BY ident ORDER BY clock DESC) AS r<\/strong> FROM\u00a0FlightLog\u00a0WHERE ::r:time ) a WHERE (r = 1)<\/p><\/blockquote>\n

      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.<\/p>\n

      \"\"<\/p>\n

       <\/p>\n"},{"acf_fc_layout":"content","content":"

      Going beyond and showing previous locations<\/strong><\/h2>\n

      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.<\/p>\n

      What I want to do now is to draw:<\/p>\n