Query Layer Spatial Reference

[MikeMacRae1]

I am building a query layer in ArcMap 10.2 via File-->Add Data-->Add Query Layer wizard. I am using a few tables from an Oracle database (11g, ST_Geometry) using ArcSDE 10.2.

There are 4 tables that i am using, 3 of which are regular database tables, the 4th is a spatial view. I am hoping to use the spatial view to populate the geometry of the results of the query layer.

I have built my query and validated it successfully. I have chosen to use the advanced options and when I get to that part of the wizard, supposedly I can choose a spatial reference so that i can project my results.

The problem that I am having is that this area is greyed out and I cannot set a spatial reference. Further to that, it is showing the spatial reference as "Unknown" which leads me to believe that the query layer is not picking up the reference from the spatial view. I have 'Finished' the query layer and all I get is a table with no geomtry attached to plot the shapes.

So, I have a few questions:

1. How do I plot the shapes of my query layer, given the version of Oracle and SDE I am running?
2. Can a query layer use a spatial view for the spatial aspect of the layer?
3. If not, how would I accomplish this?
4. Why is the spatial reference greyed out?

Thanks in advance for any help.

[ChrisDonohue__GISP]

Adding some ESRI enterprise geodatabase experts where in case they have some ideas:

Jake Skinner

Asrujit SenGupta

Vince Angelo

Chris Donohue, GISP

[MikeMacRae1]

Thanks Chris. One other piece of information. I tested another database we have. One that's running SDO_Geometry. Building a Query Layer in on a feature class from that database seems to work fine and populates the shapes in ArcMap. The database I want to use is running ESRI Binary spatial type. I am wondering if this might be part of the issue. I can't seem to find any reference to the spatial data types and query layers.

[MikeMacRae1]

Hey, I'm just wondering if any one has some suggestions for this. It's still a bit of a mystery to me.

Cheers,

Mike

[AsrujitSengupta]

Mike MacRae​ Sorry, we missed this post initially!

What you are observing is an expected behavior. This is documented in the Online WebHelp links:

Configure database and clients to use query layers—Help | ArcGIS for Desktop

The above link is for ArcGIS 10.3.x, however it stands true for 10.2.x as well.

Two spatial types are supported in Oracle that you can use in your query layers: ST_Geometry and SDO_Geometry.

[MikeMacRae1]

Thanks for respnding Asrujit SenGupta​.

I'm not sure that answers my question. Is the expected behavior for query layers not to work on ST_Geometry? That sounds counter intuitive to the quote in your repsonse. It seems to be telling me that I have the proper geometry (ST_Geometry) so it should work. Also, I have all the permission needed to do this type of query.

Maybe to add a bit of information to add to the mystery. We have 2 Oracle databases.

Database 1 is used to manage parcels of land for mining activities, so it is comprised of a number of stand alone tables, features classes and spatial views.

Database 2 is a larger corporate database and contains, amoung other things, copies of the feature classes and spatial views (but not the stand alone tables) that come from from the mining activities database.

Database specs:

DB1: SDE 10.2.2. Oracle v11 using ST Geometry Type

DB2: SDE 10.2.2. Oracle v12 using SDO Geometry Type.

So, in saying that, I tested the copy of one of the feature classes from both Database 1 and Database 2. Database 1 is the database where I have the issue. If I build a query layer using the feature class from Database 2 (the corporate database), it is successful. See images below.

Database 1. Notice the 'UNKNOWN' spatial reference:

Database 2. Notice that it populates spatial information in picture 2.

[AsrujitSengupta]

Is the data in DB1 using ST_geometry or SDELOB (Binary) ?

[MikeMacRae1]

ST_Geometry.

[AsrujitSengupta]

Mike MacRae wrote:

Thanks Chris. One other piece of information. I tested another database we have. One that's running SDO_Geometry. Building a Query Layer in on a feature class from that database seems to work fine and populates the shapes in ArcMap. The database I want to use is running ESRI Binary spatial type. I am wondering if this might be part of the issue. I can't seem to find any reference to the spatial data types and query layers.

This conflicts with the Statement that you had provided earlier, which made me assume that you were using Esri LOB.

If the data is in ST_Geometry, then it should not show up as a Table for sure.

[MikeMacRae1]

Thanks again Asrujit SenGupta​. Sorry for the confusion. I'm getting my information from the administrators of our Oracle Databases. (Blame the other guy, right? ) I was told yesterday, it was ST_Geometry, but I've been looking into this for weeks and have spoken to various administrators, so I'll double check with them to confirm.

So, my next question would be, if we are running ESRI Binary, it sounds like this would lead to the issue I am experiencing. Is there a fix for this? Is there a way to force Query Layers to pick up soatial on ESRI Binary geometry types?