topic Re: ArcGIS10 Query layers vs SDE Spatial view in Data Management Questions

ArcGIS10 Query layers vs SDE Spatial view

NiallCarter — Wed, 25 May 2011 06:51:24 GMT

Hi all

Which is quicker, and what are the benefits/pitfalls of using the new Query layers at ArcGIS 10 versus a spatial view created by using the SDETABLE -o Create_view command?

It seems that both are able to display data using SQL from the database (I am using Oracle SDO geom). As far as I can see the difference is that the query layer will execute the SQL statement at every map redraw whilst the SDE spatial view will create a database view and then display off that; surely this will mean that the view will perform quicker?

Thanks,

Niall

Re: ArcGIS10 Query layers vs SDE Spatial view

VinceAngelo — Wed, 25 May 2011 10:18:56 GMT

There may be a few more milliseconds in the first prepare of a SQL query over that of a view,
but the subsequent queries are likely to be cached by the optimizer. Each draw creates a
query in each representation. You might find that numeric column return results are slightly
different (more columns will map to 64-bit float). In the end, spatial views exist to join SDELOB
and SDEBINARY storage layers to other tables, while SDO_GEOMETRY and ST_GEOMETRY
views should be exposed by registration or Query Layers.

- V

Re: ArcGIS10 Query layers vs SDE Spatial view

DanMcCoy — Mon, 26 Aug 2013 15:49:25 GMT

An SDE spatial view appears as a feature class in the geodatabase. Is there a way to "register" a Query Layer in the gdb to make it available to 3rd-party apps or other tools that require a feature class?

Thanks,

Dan

Re: ArcGIS10 Query layers vs SDE Spatial view

VinceAngelo — Mon, 26 Aug 2013 18:22:47 GMT

By definition, Query Layers are not ArcSDE Feature classes -- they use a different connection protocol.
While the underlying SQL is similar, the methodology to get there is different.

Are you really looking for 3rd party apps that acknowledge Query Layers? That's really quite difficult,
since the developers would need to reverse engineer Esri project or service files to extract the query.

Once you define a view (on a table with a SQL spatial type) with SQL, then you can access it as a
Query Layer or register it and access it as an ArcSDE feature class, You may also dynamically query
a table with a spatial type using SQL, or via Query Layers, or via the definition query on a registered
table. If neither of these options meet your requirements, you'll need to share those requirements
before we can answer the question.

- V

Re: ArcGIS10 Query layers vs SDE Spatial view

DanMcCoy — Mon, 26 Aug 2013 20:08:12 GMT

Vince,

Thanks for the quick follow-up.

I'm not exactly sure what the requirements might be for third party apps.... I was generally speaking (for the purposes of comparing query layers vs spatial views) of cases where some system or process is expecting a feature class in geodatabase rather than a layer in a map service, etc.

I just wasn't sure if there was a way of creating a query layer and persisting it in the GDB as a FC....

Thanks,

Dan

Re: ArcGIS10 Query layers vs SDE Spatial view

VinceAngelo — Mon, 26 Aug 2013 21:11:17 GMT

Either can be either, so I still don't understand your goal in asking the question.

- V

Re: ArcGIS10 Query layers vs SDE Spatial view

DanMcCoy — Mon, 26 Aug 2013 22:05:49 GMT

Sorry, if I'm not expressing myself very well.... My goal was just to continue the discussion of Query layers vs SDE Spatial view... comparing & contrasting, pros & cons, etc...

I had seen the following post earlier and it made me wonder about which scenarios you might need a spatial view over a q layer:
http://gdbgeek.wordpress.com/2011/03/14/the-spatial-view-is-dead-long-live-query-layers/

Can you explain what you meant by "either can be either"?

Thanks,

Dan

Re: ArcGIS10 Query layers vs SDE Spatial view

MarcoBoeringa — Tue, 27 Aug 2013 19:04:18 GMT

I just wasn't sure if there was a way of creating a query layer and persisting it in the GDB as a FC...

Dan, selections (or queries in database terms) on tables in databases are defined using the SQL language. You define a SQL statement using a number of keywords the database recognizes in one human readable text sentence. E.g.

SELECT fieldA FROM tableB

This text string or SQL statement can be either dynamically generated, e.g. each time you pan or zoom in ArcMap, ArcMap generates these queries and sends them off to the database to collect the features in the current data frame's extent, or persisted in some way.

Persisting can be done in several ways:

- At the database level. The database stores the SQL statement / query string as a "database view". This option does not involve any ESRI features, you use the standard database tools to define or create them.
- At the ArcMap application level. ArcMap stores statements in the MXD document.

If the statement is persisted at the application level, there are basically two options:

- Queries that link to ArcSDE / Geodatabase Feature Classes. All of these queries are fully processed via ArcSDE's Direct Connect DLL's on your local PC and "connect / involve" the ArcSDE Repository with it's ArcSDE System Tables and Geodatabase System Tables in the database.
- Queries that link to non-ArcSDE spatial type tables (new feature from 10.0 onwards). *** THESE ARE CALLED "QUERY LAYERS" BY ESRI *** All of these are more or less directed straight through ODBC drivers, and don't involve "ArcSDE" in the classic sense (no ArcSDE Repository used, although some ArcSDE components on your local PC are being used in query processing).

One thing to note, is that a Query Layer can reference two database sources:

- Plain (spatial) database tables
- Database views of (spatial) tables.

So, while the database view persists a SQL query on the database level, a Query Layer can persist a SQL query referencing a database view (queries can reference queries).

Re: ArcGIS10 Query layers vs SDE Spatial view

DanMcCoy — Tue, 27 Aug 2013 19:36:27 GMT

Thanks Marco. My question was specifically whether an ESRI "Query Layer" can be persisted on the database side to appear as a feature class in a geodatabase. Sounds like the answer is "no".

My feeling is that this would be a case where if the client needs a feature class with all of the joins, filters, aliases, etc. already applied, then you would still need an SDE spatial view.

Re: ArcGIS10 Query layers vs SDE Spatial view

MarcoBoeringa — Tue, 27 Aug 2013 20:15:37 GMT

Thanks Marco. My question was specifically whether an ESRI "Query Layer" can be persisted on the database side to appear as a feature class in a geodatabase. Sounds like the answer is "no".

Dan, Query Layers just use any valid SQL syntax for your database (Oracle, SQL Server etc.). This means that to "persist" the Query Layer, you would simply copy the Query Layer's SQL statement from the ArcMap dialog, and copy it straight into the database dialog involved in database view definition.

This database view can subsequently be used in another Query Layer using a simpler SQL statement to add / view it straight in ArcMap, or view it being displayed in ArcCatalog. PLEASE NOTE ARCCATALOG WILL DISPLAY THIS DATABASE VIEW IMMEDIATELY WITHOUT DEFINING A NEW QUERY LAYER (EVEN SPATIALLY, you can view the geometries)!

But it will NOT be part of a Geodatabase, unless you register it with the geodatabase. You can however use this database view in any other application (e.g. AutoCAD Map or something) that supports reading database views.

E.g. If I had a Query Layer "1" with a SQL statement

SELECT fieldA FROM tableB WHERE fieldA = 'something'

I can copy this definition in the dialogs for database view definition, e.g. in SQL Server Management Studio, and give it a name, e.g. "MySpatialView", to persist it at database level. This database view is now usable by any application.

And subsequently create a second spatial Query Layer "2" by dragging and dropping it from ArcCatalog to ArcMap, or defining a simpler query:

SELECT * FROM MySpatialView

This Query Layer will now embody the original selection of 'something'.

So the answer is basically "Yes"!, unless you mean to suggest the layer should show up in Feature Datasets, and be able to be exhibit advanced Geodatabase behaviour (become part of a Parcel Fabric, Geometric Network, Topology), in which case the answer is "No".

Re: ArcGIS10 Query layers vs SDE Spatial view

DanMcCoy — Tue, 27 Aug 2013 23:47:02 GMT

So, just using the same SQL from the Query Layer to create a DB view, then registering it with the GDB would essentially get you the same thing as using sdetable -o create_view?

Re: ArcGIS10 Query layers vs SDE Spatial view

MarcoBoeringa — Wed, 28 Aug 2013 11:39:30 GMT

So, just using the same SQL from the Query Layer to create a DB view, then registering it with the GDB would essentially get you the same thing as using sdetable -o create_view?

I think I need to correct myself here a bit. This is all a bit to the limit of what I tested, but if I understand the Help right, you can only register tables, not views, with the geodatabase.

This essentially means the spatial view that you create, will remain a stand-alone table / layer, not registered and read-only. So you can't edit it or have it participate in geodatabase behaviour. ArcCatalog and ArcMap are able to make good use of the data though.

As an example, as to what is possible and to what I tested, in the images below you see a database view created that joins an ArcSDE Versioned View with a normal database table based on the fields "name" and "morph_type" that have the same content. You preferably need to include an ObjectID as well in the view definition if already available, otherwise ArcCatalog or ArcMap will prompt you for a unique column (combination).

The SQL statement for this is shown both in SQL Server Management Studio, and in ArcCatalog. In ArcCatalog you see the resultant layer (just one record actually, a multi-part polygon in this test).

The example screenshot here is from ArcGIS 10.2, but Query Layer functionality, and the ability to directly see and use non-geodatabase registered spatial tables in ArcCatalog, was introduced at 10.0.

Re: ArcGIS10 Query layers vs SDE Spatial view

JeriLedbetter — Thu, 12 Dec 2013 23:55:07 GMT

I was under the impression that I could publish a service using a View. I'm getting a high unresolved error, Code 00021, "Feature selections are not supported." This error code supposedly indicates that I have features selected, which I do not. I'm using ArcMap 10.1 Standard and Server Standard. I've created a view using a large geodatabase and a table. I don't want to join and export them, because they are derived from separate sources, and each will be updated individually.

I would appreciate any advice.

- Jeri