I've run into a bit of a stumbling block when trying to use a join within the context of a dynamic layer. I've included some details about my test environment; I've also included a working dynamic layers rendering request without a join and a broken request with a join. I am hoping someone is able to spot my error.
The connection within the MapService is registered as the schema owner (VRIMSD_ADMIN); this user is not a DBA.
The VRIMSD_ADMIN.FOREST_COVER_OBJECT table is a spatially enabled SDO table; VRIMSD_ADMIN.FOREST_COVER_OBJECT.FOREST_COVER_OBJECT_ID is registered as the obid column.
The VRIMSD_ADMIN.VEGETATION_COVER_POLYGON_EST table does not have any spatial components and is registered with SDE. There is a 1-1 relationship from the FOREST_COVER_OBJECT table to the VEGETATION_COVER_POLYGON_EST table.
David, At Beta1 dynamic layers block creating a join based on OID (objectId) fields. This restriction is removed at Beta2. In your case can you join the two dynamic layers based on any other field (other than OID and Shape fields)?
At the moment we are trying to figure out the join syntax - I had just picked two tables our database. I also tried switching the join to use a varchar2(10) join field with a foreign key however I got the same results.
I tried including / excluding the schema and tables names in the field identifier incase the issue related to generating SQL in ArcSDE. I also checked the ArcSDE logs for errors; the log files had not been altered. I was wondering if this could be related to the data being persisted as SDO_GEOMETRY rather than SDE_BINARY?
I've tried to tackle this from a different approach by using the query source type, but ran into a similar set of issues. For these tests I simplified the json content by using the identify endpoint; I also tested one SDO Polygon layer and one SDE Binary layer.
In both cases I could access the data correctly using the 'table' syntax for a dynamic layer, but could not access the data using the 'query' syntax; an empty result set was being returned as opposed to an error code. Since the ArcGIS Server logs were not indicating any issues. I also ran an Oracle trace against the SDE proxy user; the connection only appeared to access the sde.versions table.
I was wondering if I needed to specify the layer version in the second request (the dataset is not SDE versioned), and is so what the syntax would be (it's not defined in the API reference provided with the 10.1 Beta).
I've re-tried all our examples using ArcGIS Server Beta 2 - the results were the same as Beta 1 where I could use dynamic layers to access a single layer but could not use the join or query syntax to access the same data.
I've set our server's logs to verbose and found that the requests appeared to be working normally, however the failed requests did not have any log entries indicating the layer was being drawn. I'm thinking this indicates that when an error occurs with the request, the layer is being dropped from the request model objects before the rendering stage. If there a way to configure the logging to indicate why the layers are being dropped from the request?
I would be happy to provide any details about the layers / tables being used for the tests, just let me know which details would be useful.
Thanks for your interest. The original SDE workspace is registered as an Application Server; I also created an Oracle workspace registered as a direct connection for testing.
I tried the json provided using the SDE workspace without success; I unable to create a query layer within Arcmap using the existing SDE Application Server connection (looks like it needs a direct connection).
I also tried a number of tests using a direct connection. In ArcMap I was unable to visualize the data. Using the 'Add Query' interface ArcMap did not detect the data as a FeatureClass. ArcCatalog did detect the layer as a FeatureClass, however after 'draging' the layer into ArcMap it was also unable to render the data - I killed ArcMap after 10 minutes of network activity; I'm guessing the connection could not leverage the spatial index on the database server and was trying to download a complete copy of the layer (~4.5m point features).
Tests performed against the direct connection on the ArcGIS server failed quickly; I am guessing this is because we have not installed an Oracle Home. I could not confirm this is the problem by reviewing the logs (no errors were logged), but after encountering the issues above with this connection in ArcMap I'm thinking adding the new driver won't let the layer be rendered.
sdelayer -o describe_long
ArcSDE 9.3.1 for Oracle10g Build 3092 Wed Nov 23 09:54:53 2011 Layer Administration Utility ----------------------------------------------------- Layer Description ....: Veg-Label Layer Table Owner ..........: WHSE_FOREST_VEGETATION Table Name ...........: VEG_LABEL Spatial Column .......: GEOMETRY Layer Id .............: 185 SRID .................: 50 Minimum Shape Id .....: 1 Offset ...............: falsex: 0.000000 falsey: 0.000000 System Units .........: 1000.000000 Z Offset..............: -1000000.000000 Z Units ..............: 1000.000000 Measure Offset .......: <None> Measure Units ........: <None> XY Cluster Tolerance .: 0.0 Spatial Index ........: parameter: SPIDX_GRID,GRID0=7500,FULL exist: No array form: 7500,0,0 Layer Envelope .......: minx: 200000.00000, miny: 300000.00000 maxx: 1900000.00000, maxy: 1800000.00000 Entities .............: p Layer Type ...........: SDE Creation Date ........: 03/21/03 15:48:33 I/O Mode .............: LOAD Autolocking ..........: Enabled Precision.............: Basic User Privileges ......: SELECT Coordinate System ....: PROJCS["PCS_Albers",GEOGCS["GCS_North_American_1983",DAT UM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM[ "Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Albers"],PARAMET ER["False_Easting",1000000.0],PARAMETER["False_Northing",0.0],PARAMETER["Central _Meridian",-126.0],PARAMETER["Standard_Parallel_1",50.0],PARAMETER["Standard_Par allel_2",58.5],PARAMETER["Latitude_Of_Origin",45.0],UNIT["Meter",1.0]]
Hi Srinivas, I have been working under the assumption that if I cannot visualize the data in ArcMap then I will be unable to display the data on ArcGIS Server. To this end, I've been focusing on getting the query tool to work in ArcMap but haven't had alot of success. I created three small test layers containing polygons; each layer was created using the ArcCatalog wizard from a simple shp file. All three layers were persisted in the same schema hosted on a 11g/Sde 9.3.1 server; the only difference between each layer was the hint provided at creation indicating the underlying geometry type (SDELOB, ST_GEOMETRY, SDO_GEOMETRY). For each of the layers I was able to visualize the data using both an SDE Connection and a Direct Oracle Connection however I had varying degrees of success when attempting to view the layers using the query dialog.
In the case of the SDELOB layer, the query dialog completed successfully however the object added to the map acted like a regular table. After digging in the properties, the SHAPE column was being detected as being a LONG datatype rather than a GEOMETRY. I tried enumerating a couple of the column names and using a simple "SELECT * FROM TEST" query.
In the case of the ST_GEOMETRY layer, the query dialog was unable to pass the validation steps. The query could be executed in sqlplus logged in as the same user (SELECT * FROM TEST2).
Lastly, in the case of the SDO_GEOMETRY, the query dialog completed successfully and the object added to the map legend indicated the query layer contained polygons. I could view the data in the attribute viewer, select a feature and make the feature 'Flash' however the data did not display as a layer ('Flash'ing the feature was the only way to see the spatial).