I have a dataset of polygons that are measured on a regular interval. The polygons are fairly large and complex and never change. Maybe some would get added but that hasn't happened in years. Every week, each polygon gets a measure. Measurements get stored in a table with the polygon id and the date and time of the measurement, the value and some other metadata. It amounts to a vertical table with a composite key of polygon id and date time.
I need to display the table as a feature class with the polygon geometry. I don't want to store the geometry over and over for each row in the table. Relates don't work because I can't filter on date, user are going to want to change the filter on date to look at a particular time slice.
So I have tried making a spatial view using joining the table and polygons based on polygon id and including the objectid of the measurement table but ArcMap doesn't recognize the objectid as a proper id and will not do selections or identifies, also it sometimes just crashes or hangs trying to do that.
I tried a query layer but that also failed to be able to do a selection and identify. The objectids in the measurement table would be unique in the join but not the objectids in the polygon featureclass.
I want a feature class with the polygons stacked for each valid date time (with the idea of setting definition queries or doing other queries based on time) but I don't want to stored the geometry over and over again... Someone suggested materialized views in the database (oracle) I am still looking into that but it kind of defeats the purpose of not storing the geometry since, well the view is materialized, so it would be stored.