This blog is more of a hands-on tutorial. If you want to know more on the concept etc., please read this blog that I wrote a while back.

One important point before we begin, you must have an enterprise database such as PostgreSQL, SQL Server, Oracle etc. The function that I’m using below to generate line from points is specific to SQL Server. The SQL statements in general should work for other databases expect for that function. Please consult to your database help to find the equivalent one.

Let’s dive in…

Overview:

Let’s start by opening a layer package (https://arcg.is/1H9vT8) in ArcGIS Pro. When it is opened, you will see some points on the map.

First thing we want to do is to generate lines from all point features belong to each hurricane just like below.

Second, we will make the layer a time aware layer and generates lines using only points from any time range.

Finally, we will compute some statistics such as current wind speed, min/max wind speed during that period, and number of points used to generate each line.

Create lines from points on the fly:

1. Use Feature Class to Feature Class (or right-click the layer in ArcGIS Pro Contents pane and choose Data | Export Features) geoprocessing tool to export all features in a feature class inside an enterprise database.
   • name the output feature class Atlantic_Hurricanes_2000.
2. On the Map tab, in the Layer group, in the Add Data menu, click Query Layer.
3. Select your enterprise database connection from the Connection drop down box.
4. Provide a name.
5. Copy/paste the SQL from below in the Query text box.
   

   SELECT objectid, eventid, shape, vertex_count as nos_points, shape.STLength() AS shape_length
   FROM
   (select EVENTID,
   convert(int, ROW_NUMBER() OVER (ORDER BY EVENTID)) as objectid,
   iif(count(*) < 2, null, geometry::STLineFromText(
   concat(‘LINESTRING (‘,
   string_agg(concat(shape.STX, ‘ ‘, shape.STY), ‘, ‘) within group (order by Date_Time asc),
   ‘)’), 4326)) as shape,
   count(*) as vertex_count
   from Atlantic_Hurricanes_2000
   group by EVENTID) x

6. Click Validate.
7. Click Next.
8. Have the checkbox next to objectid checked.
9. Make sure all the information below is correct.
10. Click Finish.
11. You will see a line feature layer added in your map. Each line feature represents track for a single hurricane.
12. If you want you can symbolize with them unique value renderer using EVENTID field.

Make the layer time aware

1. Open the layer Properties page.
2. Switch to the Source tab.
3. Click the pencil icon on the right side in the Query row.
4. It will open the Query Layer editor.
5. Add the following line right before the very last line i.e “group by EVENTID) x”.
   

   WHERE ::r:time

6. You will see a pencil icon show up. Click it.
7. Enter date_time in the Field or Expression text box.
8. Pick Date from the Data Type drop down.
9. Uncheck Default value.
10. Expand the Advanced section
11. Enter Atlantic_Hurricanes_2000 the text box next to ‘Name of the table the field belongs to’ label
12. Click Done.
13. Click X to close this inline editor.
14. This adds a range parameter of date type.
15. Follow step#6 – #10 from above.
16. Once finished, you will see the the time slider showed up on your map
17. If you check the Time tab on the layer’s Properties page, you will see that the layer is made time aware automatically just because you added a range parameter.
18. You can use the time sliders setting to navigate to any time window and see hurricane paths generated for that time window.

Add other summary statistics results to each line

1. Open the Query Layer editor from the layer Properties page as we did in the section above.
2. Delete everything from the Query text box.
3. Copy/paste the SQL from below.
   

   SELECT
   objectid, x.eventid, shape, vertex_count as nos_points, shape.STLength() AS shape_length,
   max_windspeed, MIN_windspeed, y.latest_windspeed
   FROM
   (
   select EVENTID,
   convert(int, ROW_NUMBER() OVER (ORDER BY EVENTID)) as objectid,
   iif(COUNT(*) < 2, NULL,
   geometry::STLineFromText(
   concat(‘LINESTRING (‘,
   string_agg(concat(shape.STX, ‘ ‘, shape.STY), ‘, ‘) within group (order by Date_Time asc),
   ‘)’), 4326)) as shape,
   count(*) as vertex_count,
   MAX(windspeed) as max_windspeed, MIN(windspeed) AS MIN_windspeed
   from ATLANTIC_HURRICANES_2000
   where ::r:datetime
   group by EVENTID
   ) x
   inner join
   (
   select eventid, latest_windspeed from
   (
   select eventid, windspeed as latest_windspeed,
   ROW_NUMBER() over (partition by eventid order by date_time desc) as row_id
   from ATLANTIC_HURRICANES_2000
   where ::r:datetime
   ) y2
   where y2.row_id = 1
   ) y
   on x.eventid = y.eventid

4. Follow step#6 – #15 from above.
5. Once finish updating the layer, you can open its attribute table and you will see some summary statistics such as # of points, max_windspeed, min_windspeed and lastest_windspeed.
   
6. Modify the time slider time range and you will see these values get updated. And you might even see more or less # of records depending on how many hurricanes were there during that time.
   

I hope you have found this helpful. And if you need to share this out, publish/share it as a map service/map image layer on your standalone server or enterprise portal respectively.

About the author

Tanu Hoque

Tanu is a product engineer on Esri Mapping Team focusing on map service, print service, and ArcGIS Pro. He also works on spatio-temporal analysis, spatial aggregation and real-time data. Tanu’s background includes a masters in Urban Planning from University of Akron, Ohio, USA and a bachelor degree from Khulna University, Bangladesh, and worked as GIS Coordinator in City of West Springfield, and GIS Specialist in a hydrology modeling center in Bangladesh before joining Esri.

Connect: