topic Re: Solution for a many to one spatial view or query layer in Data Management Questions

Solution for a many to one spatial view or query layer

AlexanderGray — Thu, 25 Sep 2014 13:35:45 GMT

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.

Re: Solution for a many to one spatial view or query layer

VinceAngelo — Fri, 26 Sep 2014 16:28:33 GMT

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

Re: Solution for a many to one spatial view or query layer

RichardFairhurst — Fri, 26 Sep 2014 17:57:53 GMT

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.

Re: Solution for a many to one spatial view or query layer

AlexanderGray — Fri, 26 Sep 2014 18:15:58 GMT

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.)

Re: Solution for a many to one spatial view or query layer

VinceAngelo — Fri, 26 Sep 2014 18:35:46 GMT

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

Re: Solution for a many to one spatial view or query layer

AlexanderGray — Fri, 26 Sep 2014 19:17:53 GMT

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.

Re: Solution for a many to one spatial view or query layer

VinceAngelo — Fri, 26 Sep 2014 21:09:28 GMT

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

Re: Solution for a many to one spatial view or query layer

AlexanderGray — Tue, 30 Sep 2014 19:43:31 GMT

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.

Re: Solution for a many to one spatial view or query layer

AlexanderGray — Tue, 30 Sep 2014 19:50:16 GMT

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.

Re: Solution for a many to one spatial view or query layer

NateArnold — Fri, 31 Oct 2014 14:00:28 GMT

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

Re: Solution for a many to one spatial view or query layer

VinceAngelo — Sat, 01 Nov 2014 14:40:34 GMT

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

Re: Solution for a many to one spatial view or query layer

JoshuaBixby — Sat, 01 Nov 2014 16:16:14 GMT

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.

Re: Solution for a many to one spatial view or query layer

AlexanderGray — Tue, 06 Jan 2015 20:39:03 GMT

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.