Creating a time-series web map that updates everyday

[SunbeamRahman1]

Hello all, this is my first question in GeoNet. 

I want to create a web map inside our ArcGIS Enterprise. This map will contain the following features, 

• the map has an administrative layer with about 5000 polygons, each with a unique ID (UnionCode)
• there is a 'pest status' table (see pic) for each administrative polygon with the same unique ID (UnionCode). These tables (ie. PND_Apr0118) are created every day and stored inside a geodatabase
• there are about 11 pest indices (pndIdx_1, pndIdx_2, ...) and their respective area coverage percentage (pndArea_1, pndArea_2, ...) stored inside each table. At the end, there is a column storing the date of the data and the date of the table creation. pndIdx_1 = Yellow Stem Borer, pndIdx_2 = Brown planthopper etc (this could also be 11 separate timeseries map, or one map with 11 toggle-able layers) 
• the web map will update a choropleth map using these table everyday. There will be a popup for each polygon describing numbers
• since this is a timeseries map, the 'timeseries widget' will also update according to the recent entry. The map symbols and popup descriptions will update based on the pointer of this widget

What are the best options for this. The ArcGIS Enterprise has both Server and Portal 10.6 in separate machines, federated and hosting server defined with limited resources. arcgisenterprise dynamic table

[SunbeamRahman1]

Any ideas? Anyone?

[TanuHoque]

Sunbeam Rahman‌

have few questions:

1. does 'pest status' table contain records for all 5000 admin polygons or there are 5000 separate tables one for each admin polygons?
2. when new data comes in everyday, do you update the existing table(s) (with that i mean daily data gets inserted as new rows) or you create a new table(s) and new data get inserted in those newly created tables.
3. on your webmap, do you want your end clients use time slider to go back to any time to visualize the map or your webmap always shows the latest pest-status?

[SunbeamRahman1]

@Tanu Hoque,

1. The 'pest status' table has 5000+ rows referencing the admin polygons using the key 'UnionCode'

2. the new data is a separate table with the same 'UnionCode'. This means all 5000+ rows have an update everyday (every administrative polygon gets a pest forecast that changes with weather), this means everyday a new table. If this changes are to be inserted in the same table the table will get huge

3. yes, the user has the freedom to roam in any time in the past

[TanuHoque · Accepted solution]

Thanks Sunbeam Rahman‌.

since your answer to #3 is yes, I'd say add new daily forecast data to 'pest status' table (instead of adding them in newly created tables). Depending on how far in the past you want your users to go back in time, you can periodically remove older records from that table to keep the size of the table manageable. If you have both 'UnionCode' and the date-time fields indexed, pulling 5000+ records from a table and joining them to another shouldn't be a big issue for an enterprise database these days.

pls read the following help topic:

Temporal data in separate tables—Help | ArcGIS for Desktop 

since your table gets updated daily, pls read this help as well: Calculating the time extent of your data—Help | ArcGIS for Desktop 

pls let me know if you have any question.

[SunbeamRahman1]

Thanks @Tanu Hoque. I have few more queries, 

1. I have just merged 29 tables of September (2 - 30/09/18) which creates roughly 150000 rows. If I do a similar task for 12 months the total number would increase to 12-times. How far can I go with this (using pgsql). Or, according to this post, aggregation could be a way around?
2. how am I joining the administrative polygons with 'pest status' table? is it with 'relationship class', 'table join/ relate' or 'query layer'? 
3. I'll also need popups which will update based on the current position of the time-slider

[TanuHoque]

Sunbeam Rahman‌

1. It all depends on your use case. If you were to store a year worth of daily forecast data, you'd end up having a table with close to 2 million records. If you index datetime and unioncode fields, then database should be able to pull out 5000+ records for a single day and join them admin boundaries pretty fast (of course it'd also depends on the capacity of the machine that is hosting your database). I think you need to do some trial outs to find out at what point database performance does not meet your expectation. Pls keep in mind that when you share that out as a map service, there may be more than one users hitting the database.

Regarding aggregation: if i'm not mistaken, you want to use the daily forecast data, therefore aggregation would not help much (in fact it might be relatively expensive). If your data were like hourly or bi-hourly forecast, and you'd want to visualize them using dynamically computed daily forecast, yes, then you could have gone with the aggregation approach I described in the blog post.

2. my understanding is that both admin boundary and pest-status tables are in the same database, right? if so, you can follow the same steps that described in the help topic I specified earlier to join.

you can use query layer too, there won't be any performance difference between query layer and doing joins in arcmap (as described in the help topic).

3. popup should work with map's current time.

hope this helps. pls let me know if you have any question.