Solution for a many to one spatial view or query layer

[AlexanderGray]

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.

[AlexanderGray]

Interesting so the sde tools are gone but there is no solution to this problem.  So what imperfect work-around there was is now gone.

[RichardFairhurst]

No official help is provided for your problem by Esri.  They don't support these relationships for any sizable datasets for all practical purposes.  Every user is on their own to come up with whatever they can after experimenting with their data for weeks.  All in memory configurations I have tried take hours for the tools to complete and are extremely slow at redrawing, so I have given up on them.

At 10.1 the ability to duplicate the geometry over and over again with a standard join and an export to a geodatabase is the only solution that works for me.  The export time is minimal and the redraw time is fantastic.  I can save my company far more money by not wasting my time and or my user's time with slow performance and using up a fraction of the unused Terabytes of disk space that is on our servers to duplicate the geometry over and over.

I doubt Esri has much else to offer, given that the geoprocessing paradigm supported by Esri practically always requires you to create a new derivative feature class with nearly every geoprocessing tool you use.  Using that paradigm I have created huge numbers of copies of the same geometry over and over to get anything done, like it or not.

[AlexanderGray]

Richard, it has been my experience that flattening your data is the only way to go, especially when versioning.  Geoprocessing creates a bunch of intermediate data that is redundant but it can be eliminated after the processing.

In my case I just want to create a view of the data that can be queried based on time slice.  For this particular data subset the geometry never changes and is really just for display purposes.

I find esri's focus seems to be drifting away from the desktop GIS analyst crunching the data on desktop as the specialist in his particular outfit but I suspect we still make up the core of the licenses out there, it just might not be the growth segment.