Solution for a many to one spatial view or query layer

6219
12
Jump to solution
09-25-2014 06:35 AM
AlexanderGray
Occasional Contributor III

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.

0 Kudos
1 Solution

Accepted Solutions
VinceAngelo
Esri Esteemed Contributor

Registering with the geodatabase (right click) is very different than registering with ArcSDE.  You can't register views with the geodatabase because they require an SDE-set registered rowid.  Using 'sdelayer -o register' is your only option at this point -- ArcGIS 10.3 will have a better solution.

If the view doesn't work as a Query Layer, then you may have a problem with your join rules.  Try running database queries, to see if your rowid is unique.

- V

View solution in original post

0 Kudos
12 Replies
VinceAngelo
Esri Esteemed Contributor

What version of ArcGIS are you using?

What exact version of Oracle are you using?

What is the geometry storage type of the base polygon table (ST_GEOMETRY. SDELOB, SDO_GEOMETRY)?

What, exactly, did you do to construct a spatial view?

How is the objectid of the management table defined?

What do you mean by "ArcMap doesn't recognize the objectid as a proper id"?

If you had posted this over in Managing Data you might have gotten more responses.

- V

0 Kudos
AlexanderGray
Occasional Contributor III

ArcGIS 10.2.2, SDE 10.0, Oracle 11g  11.2.0.4.0

Geometry is ST_GEOMETRY

Spatial view was constructed in the Geoprocessing toolbox tool.  (my understanding is the command line tools are being deprecated)  The sql is just a where clause join between the table and the featureclass

The table is registered with geodatabase (not versioned) and has a geodatabase managed objectid

ArcMap doesn't recognize in the sense that spatial select using the select tool and identify doesn't work, I haven't tested a whole of lot beyond that because I found that trying to use either of these tools can hang or crash arcmap.  (Both of which happened a lot as soon as I added this view to the map)

Sorry I didn't post this in the right place, I am still trying to get the hang of the new forum (again.)

0 Kudos
VinceAngelo
Esri Esteemed Contributor

The toolbox tool is documented as not registering the view with ArcSDE (third bullet in Usage), which is required for ArcGIS to be able to use it as part of a Database Connection (using it as a Query Layer should be possible without registration).  If you register the view with 'sdelayer -o register -C objectid,USER ...' you might have more success rendering.

You may still have issues with poor join performance (due to fragmentation -- spatial, attribute, or both).

You probably want to evaluate upgrading that geodatabase to use a modern ST_GEOMETRY implementation.

I only recently set up an explicit "geodatabase" tag query stream, or I would have missed your post.  Posting in your own Discussions folder is  possible, and can be found by indexing if it's tagged correctly, but not generally right away.

- V

0 Kudos
AlexanderGray
Occasional Contributor III

Yes I noticed the registration too.  I tried the register with geodatabase tool but it says it is not supported with views.  Geoprocessing has been a bit of a let down in this case.  Which brings us back to using command line tools, which I have to get my hands on now.  I am just worried that it is not a viable long term solution...

I tried the query layer too but I got the same problem with the select and identify not working.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

Registering with the geodatabase (right click) is very different than registering with ArcSDE.  You can't register views with the geodatabase because they require an SDE-set registered rowid.  Using 'sdelayer -o register' is your only option at this point -- ArcGIS 10.3 will have a better solution.

If the view doesn't work as a Query Layer, then you may have a problem with your join rules.  Try running database queries, to see if your rowid is unique.

- V

0 Kudos
AlexanderGray
Occasional Contributor III

ok, it took me a while to get this going.  It seems that the 10.2 sde command line tools are not compatible with a 10.0 database.  The 10.1 ArcGIS server will not install on a workstation with 10.2 desktop but the 10.0 ArcSDE will install along with desktop 10.2, just cancel the post install...

So...  I use sdetable -o createview (no registering) and it made the spatial view no problem, works great, draws much much faster than toolbox views and query layers.  Select, Identify works great.  No silly popup windows to ask for the unique field.

Query layers didn't work right.  The field is unique in the output.  I checked with SQL developer queries.  The value table objectids are geodatabase objectids and are unique to the table.  The join is a many to one, so each feature in the value table is kept and not duplicated but they join to the same polygons over and over again.  According to the documentation, both views and query layers need a column with a unique constraint in the spatial featureclass to be added to the view.  This precludes any many to one between a table and a featureclass.  This means even the basest normalization of data is still impossible in a geodatabase without the sde commands...

I will look out for what is new in 10.3, I hope it allows for this sort of view.

0 Kudos
NateArnold
Occasional Contributor

Vince Angelo‌ Now that 10.3 Pre-release is out, can you clarify what the better solution is?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

Not yet. As John indicated, the 10.3 work-around for the post-10.3 "better solution" is still being validated.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I read John Baleja‌'s comment as meaning there will be a post-10.3 solution.  If there is going to be a 10.3 stop-gap solution, I look forward to learning more about it at final release.

0 Kudos