ArcGIS10 Query layers vs SDE Spatial view

[NiallCarter]

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

[VinceAngelo]

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

[DanMcCoy]

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

[VinceAngelo]

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

[DanMcCoy]

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

[VinceAngelo]

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

- V

[DanMcCoy]

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

[MarcoBoeringa]

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

[DanMcCoy]

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.

[MarcoBoeringa]

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