Spatial View help needed

SteveMacLean · ‎02-07-2014

Hi everyone,

I have a feature class that I want to join to a database view that contains related business data for the features on the map. I want to expose this join as a feature service and dynamic map service in ArcGIS Server.

I have tried creating a query layer based on a database view I created that joins the featureclass with the business table. This seems to work fine in ArcMAP, however, when I expose the new feature in ArcGIS Server it doesn't seem to work. I can add the new ArcGIS Server service to the map and it will display the features but when I do an identify on the features it doesn't come back with any data. Additionally, when I try and utilize the new service in the ArcGIS Server Javascript API as a feature class it doesn't load any data at all. It works fine when i access the web service that is hitting the raw feature class but not with the query layer.

I have also tried creating a MXD that does a regular JOIN to the business table. However, doing it this way does not expose the attributes that should be included from the join.

There must be a way to do this and link to business tables to spatial tables.

I am using Oracle 11g Enterprise and ArcGIS Sever 10.2

Any help would be appreciated.

JeffPace · ‎02-07-2014

WE do spatial views alot. I have published them with 10.2 (we are migrating now) but I have not tested querying them.

Is your REST endpoint public?

A few things I would check.

1. Is the spatial view registered with the Geodatabase?
2. Can you query the service with a 1=1 or a objectid=1 type simple query?

SteveMacLean · ‎02-07-2014

Thanks for your help.

1) The Rest end point isn't on a public server. It's an internal end point.

2) I didn't think with 10.2 you needed to register then with ArcSDE. Maybe I'm wrong on this? This is the article I read.

http://resources.arcgis.com/en/help/main/10.2/index.html#/Creating_a_spatial_view_in_Oracle/002n0000...

JeffPace · ‎02-07-2014

I am not positive

However, I am speculating the registering (the therefore creating/requiring a objectid) may be what is preventing identifying from working

If you dont have an objectid, i like to use rownum in the view cast (as number) to objectid. That way its always unique

here is our sample view that i expose with server

/* Formatted on 2/7/2014 5:15:27 PM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FORCE VIEW MCGIS.BUILDINGS_LUC_VIEW
(
   SHAPE,
   PARCELID,
   OBJECTID,
   LUC,
   LUC_RAW
)
AS
   SELECT buildings.shape,
          buildings.parcelid,
          ROWNUM objectid,
          CASE
             WHEN gis_parcels_view.luc = '2000'
             THEN
                'AIRPORTS'
             WHEN gis_parcels_view.luc IN ('2802', '0002')
             THEN
                'MOBILE HOME'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '0200'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '0205'
             THEN
                'MOBILE HOME'
             WHEN gis_parcels_view.luc IN ('7500', '9902')
             THEN
                'RESIDENTIAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '0000'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '0200'
             THEN
                'RESIDENTIAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '0300'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '0805'
             THEN
                'RESIDENTIAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '4300'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '4600'
             THEN
                'AGRICULTURAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '5100'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '6999'
             THEN
                'AGRICULTURAL'
             WHEN gis_parcels_view.luc IN ('7200', '8300', '8400')
             THEN
                'EDUCATION'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '8600'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '9002'
             THEN
                'GOVERNMENT'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '2001'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '2004'
             THEN
                'TRANSPORTATION'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '8600'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '9002'
             THEN
                'GOVERNMENT'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '7300'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '7400'
             THEN
                'MEDICAL'
             WHEN gis_parcels_view.luc = '7800'
             THEN
                'MEDICAL'
             WHEN gis_parcels_view.luc = '8500'
             THEN
                'MEDICAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '7100'
                  AND TO_NUMBER (gis_parcels_view.luc) < '7200'
             THEN
                'RELIGIOUS'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '3100'
                  AND TO_NUMBER (gis_parcels_view.luc) < '3300'
             THEN
                'CULTURAL'
             WHEN gis_parcels_view.luc IN ('7900', '3500', '7700')
             THEN
                'CULTURAL'
             WHEN gis_parcels_view.luc = '3900'
             THEN
                'HOTEL'
             WHEN TO_NUMBER (gis_parcels_view.luc) > '3999'
                  AND TO_NUMBER (gis_parcels_view.luc) < '4300'
             THEN
                'INDUSTRIAL'
             WHEN gis_parcels_view.luc IN ('9100', '9200', '9600', '4700')
             THEN
                'INDUSTRIAL'
             WHEN (TO_NUMBER (gis_parcels_view.luc) >= '3700'
                   AND TO_NUMBER (gis_parcels_view.luc) <= '3800')
             THEN
                'RECREATION'
             WHEN gis_parcels_view.luc IN ('8200', '9700', '3400', '3600')
             THEN
                'RECREATION'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '1000'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '1904'
             THEN
                'COMMERCIAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '2100'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '2800'
             THEN
                'COMMERCIAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '3300'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '3400'
             THEN
                'COMMERCIAL'
             WHEN gis_parcels_view.luc IN ('2900', '3000')
             THEN
                'COMMERCIAL'
             WHEN TO_NUMBER (gis_parcels_view.luc) >= '4800'
                  AND TO_NUMBER (gis_parcels_view.luc) <= '4999'
             THEN
                'WAREHOUSE'
             ELSE
                'GENERAL'
          END
             luc,
          gis_parcels_view.luc luc_raw
     FROM    mcgis.buildings
          LEFT OUTER JOIN
             mcgis.gis_parcels_view
          ON buildings.parcelid = gis_parcels_view.selectpin;


GRANT DELETE, INSERT, SELECT, UPDATE ON MCGIS.BUILDINGS_LUC_VIEW TO METADATA;

GRANT SELECT ON MCGIS.BUILDINGS_LUC_VIEW TO SDE WITH GRANT OPTION;

GRANT SELECT ON MCGIS.BUILDINGS_LUC_VIEW TO SDEVIEWER;

SteveMacLean · ‎02-07-2014

That's pretty much exactly what I have. I didn't do the grants though. I wonder if that's the problem.

JeffPace · ‎02-07-2014

the grants are from registering with sde,

you have an objectid field and it is number type, correct?

if you query your rest endpoint with a where clause of objectid=1 (or a valid value) do you get results?

SteveMacLean · ‎02-07-2014

This is what I did.

1. Created a simple view - create or replace view v_sites as select objectid, site_no, shape from properties;

2. In ArcMAP I brought the view in as a query layer. I am not able to do an identify here on any of the features. I thought I could but I just did a test and I can't.

3. I created a service.

4) I did a query on the endpoint and it brought back the data:

# records: 1
SITE_NO: C F 50079
Point:
X: -79.94871999975294 
Y: 44.98113999979796

5) I tried it as a feature class in my application and it brought no data back.

SteveMacLean · ‎02-07-2014

This is the code I am trying to use to load a feature layer from the spatial view and brings back no results. I know the code works because It works fine when I'm working with a feature class directly instead of the spatial view.

    var fl = new esri.layers.FeatureLayer("http://192.168.22.106:6080/arcgis/rest/services/vsite/MapServer/0", {
        //  mode: esri.layers.FeatureLayer.MODE_ONDEMAND,
        mode: esri.layers.FeatureLayer.MODE_ONDEMAND,
        outFields: ["*"],
        visible: true
    });

  map.addLayer(fl);

JeffPace · ‎02-10-2014

This is what I did.

1. Created a simple view - create or replace view v_sites as select objectid, site_no, shape from properties;

2. In ArcMAP I brought the view in as a query layer. I am not able to do an identify here on any of the features. I thought I could but I just did a test and I can't.

3. I created a service.

4) I did a query on the endpoint and it brought back the data:
# records: 1
SITE_NO: C F 50079
Point:
X: -79.94871999975294 
Y: 44.98113999979796 
5) I tried it as a feature class in my application and it brought no data back.

Instead of bring the view in as a query layer, have you tried bringing it in as a feature class in ArcMAP?

NianweiLiu · ‎02-10-2014

This is what I did.

1. Created a simple view - create or replace view v_sites as select objectid, site_no, shape from properties;

2. In ArcMAP I brought the view in as a query layer. I am not able to do an identify here on any of the features. I thought I could but I just did a test and I can't.

Assuming you are using SDO_GEOMETRY, you should verify the spatial index is been used. The identify operation will send a SDO_FILTER request to Oracle, something like "SELECT SELECT * FROM YOUR_SPATIAL VIEW WHERE SDO_FILTER (SHAPE, SDO_GEOMETRY ('POLYGON ((.....))', YOUR_SRID), 'querytype=window') = 'TRUE';". Review your execution plan, if the spatial index is not been used, you may need to force an index hint in the view definition. We used to have to do that, although seem no longer necessary in newer versions.

If you can not do identify in ArcMap, you are not going to get that in later steps. You need to fix it at that stage.