Combining time fields (temporal table relates)

Idea created by Wernazuma on Jun 18, 2020

    This is somehow related to: Web Map: time slider support for m-1 tabular data related to feature layer 



    It happens to me now in the second project in a row that I have entities with geometries that change over time and, independently, with tabular attributes that change over time.


    An example:

     Province A has a boundary change in 1950 - so there are 2 polygons(A1, A2), one with time fields 1940 and 1950, another one with 1951-1960 . Province A has Governor XYZ from  1932 to 1956 and Governor QWE from 1957 onwards.

    So, the "valid" combined chronology would be 1940-1950 (A1,XYZ); 1951-1956 (A2,XYZ); 1957-1960 (A2,QWE).


    My solution has so far been preprocessing: Do the SQL in a relational database, import it as query table to ArcGIS, query each single year, identify polygons identical between 2 consecutive years using cursor function with arcpy and store "stable time" in two fields and eliminate superfluous polygons.


    The problem (apart from complexity): It takes forever (it's thousands of entities over hundreds of years), it's rough to reproduce the procedure when the structure or to-be-processed fields change, and there is not just one such related table - in the example: the "province" doesn't only have changing governors, there's also economic and demographic data that changes over time, etc.

    And I produce dozens of huge datasets that share the same geometric basis.


    Now, if a table relate could just understand combining chronology between shape and table entry, everything would be fine. The SQL behind it is no rocket science...