Export Maps with Joins

2525
8
10-03-2011 08:36 AM
DavidAsh
New Contributor
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.

Environment:
    ArcServer 10.1 Beta 1
    ArcSDE 9.1
    Oracle 10gR2

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.

*** WORKS ***
[
  {
    "id": "VRIMSD_ADMIN.FOREST_COVER_OBJECT",
    "source": {
           "type": "dataLayer",
      "dataSource":
      {
            "type": "table",
            "workspaceId": "VegWrk",
            "dataSourceName": "VRIMSD_ADMIN.FOREST_COVER_OBJECT"
             }
    },
    "drawingInfo":
    {
      "renderer":
      {
        "type": "simple",
        "symbol":
        {
          "type" : "esriSFS",
          "style" : "esriSFSSolid",
          "color" : [166,36,0,255],
          "outline" :
          {
            "type" : "esriSLS",
            "style" : "esriSLSSolid",
            "color" : [0,255,0,255],
            "width" : 2
          }
        },
        "description": ""
      },
      "transparency": 60
    }
  }
]

*** RETURNS A WHITE IMAGE ***
[
  {
    "id": "VRIMSD_ADMIN.FOREST_COVER_OBJECT",
    "source": {
      "type": "joinTable",
      "leftTableSource":
      {
           "type": "dataLayer",
      "dataSource":
      {
            "type": "table",
            "workspaceId": "VegWrk",
            "dataSourceName": "VRIMSD_ADMIN.FOREST_COVER_OBJECT"
             }
      },
      "rightTableSource":
      {
           "type": "dataLayer",
            "dataSource":
            {
               "type": "table",
               "workspaceId": "VegWrk",
                "dataSourceName": "VRIMSD_ADMIN.VEGETATION_COVER_POLYGON_EST"
            }
      },  
      "leftTableKey": "VRIMSD_ADMIN.FOREST_COVER_OBJECT.FOREST_COVER_OBJECT_ID",
      "rightTableKey": "VRIMSD_ADMIN.VEGETATION_COVER_POLYGON_EST.FOREST_COVER_OBJECT_ID",
      "joinType": "esriLeftOuterJoin"
    },
    "drawingInfo":
    {
      "renderer":
      {
        "type": "simple",
        "symbol":
        {
          "type" : "esriSFS",
          "style" : "esriSFSSolid",
          "color" : [166,36,0,255],
          "outline" :
          {
            "type" : "esriSLS",
            "style" : "esriSLSSolid",
            "color" : [0,255,0,255],
            "width" : 2
          }
        },
        "description": ""
      }
    }
  }
]
0 Kudos
8 Replies
SrinivasVinnakota
Esri Contributor
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)?
0 Kudos
DavidAsh
New Contributor
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?

Thanks,

[
  {
    "id": "VRIMSD_ADMIN.FOREST_COVER_OBJECT",
    "source": {
      "type": "joinTable",
      "leftTableSource":
      {
           "type": "dataLayer",
      "dataSource":
      {
            "type": "table",
            "workspaceId": "VegWrk",
            "dataSourceName": "VRIMSD_ADMIN.FOREST_COVER_OBJECT"
             }
      },
      "rightTableSource":
      {
           "type": "dataLayer",
            "dataSource":
            {
               "type": "table",
               "workspaceId": "VegWrk",
                "dataSourceName": "VRIMSD_ADMIN.INVENTORY_STANDARD_CODE"
            }
      },  
      "leftTableKey": "VRIMSD_ADMIN.FOREST_COVER_OBJECT.INVENTORY_STANDARD_CODE",
      "rightTableKey": "VRIMSD_ADMIN.INVENTORY_STANDARD_CODE.INVENTORY_STANDARD_CODE",
      "joinType": "esriLeftOuterJoin"
    },
    "drawingInfo":
    {
      "renderer":
      {
        "type": "simple",
        "symbol":
        {
          "type" : "esriSFS",
          "style" : "esriSFSSolid",
          "color" : [166,36,0,255],
          "outline" :
          {
            "type" : "esriSLS",
            "style" : "esriSLSSolid",
            "color" : [0,255,0,255],
            "width" : 2
          }
        },
        "description": ""
      }
    }
  }
]
0 Kudos
DavidAsh
New Contributor
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).

Thanks,

*** WORKS ***

[
  {
    "id": "WHSE_FOREST_VEGETATION.VEG_LABEL",
    "source": {
           "type": "dataLayer",
      "dataSource":
      {
            "type": "table",
            "workspaceId": "lrdw",
            "dataSourceName": "WHSE_FOREST_VEGETATION.VEG_LABEL"
             }
    }
  }
]


*** BROKEN ***
[
  {
    "id": "WHSE_FOREST_VEGETATION.VEG_LABEL",
    "source": {
           "type": "dataLayer",
      "dataSource":
      {
            "type": "queryTable",
            "workspaceId": "lrdw",
            "query": "SELECT * FROM WHSE_FOREST_VEGETATION.VEG_LABEL",
            "oidFields": "OBJECTID",
            "geometryType": "esriGeometryPoint",
            "spatialReference": {"wkid": 3005}
             }
    }
  }
]
0 Kudos
DavidAsh
New Contributor
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.
0 Kudos
SrinivasVinnakota
Esri Contributor
Dash,
How was the SDE workspace registered - application server or direct connection? For queryTables to work it should be a direct connection.

If you were to copy the below text in to "dynamicLayer" resource does it work?

{
"id": "WHSE_FOREST_VEGETATION.VEG_LABEL",
"source": {
"type": "dataLayer",
"dataSource":
{
"type": "queryTable",
"workspaceId": "lrdw",
"query": "SELECT * FROM WHSE_FOREST_VEGETATION.VEG_LABEL",
"oidFields": "OBJECTID",
"geometryType": "esriGeometryPoint",
"spatialReference": {"wkid": 3005}
}
}
}

An alternate test would be to add "WHSE_FOREST_VEGETATION.VEG_LABEL" as a query layer in ArcMap. You can access documentation here -  http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/What_is_a_query_layer/00s50000000n0000... . Please let me know if this works.
0 Kudos
DavidAsh
New Contributor
Hi Srinivas,

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.

For reference:

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]]

Layer Configuration ..: FOREST_VEGETATION
0 Kudos
by Anonymous User
Not applicable
The layer is in Load-only I/O mode. Try changing the I/O mode to normal:

sdelayer -o normal_io -l VEG_LABEL,GEOMETRY -i sde:oracle11g -u <user> -p <pw@oracle_net_Service>

You can also use the port number if application server connection is used. e.g.; sdelayer -o normal_io -l VEG_LABEL,GEOMETRY -i 5151 -u <user> -p <pw >

Or, using ArcCatalog right click on the feature class and build spatial index from the indexes tab.

* Use direct connect for QueryLayers from ArcMap

- Subrat
0 Kudos
DavidAsh
New Contributor
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).
0 Kudos