AnsweredAssumed Answered

Solution for a many to one spatial view or query layer

Question asked by agray1 on Sep 25, 2014
Latest reply on Jan 6, 2015 by agray1

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.

Outcomes