topic Re: Spatial View help needed in ArcGIS JavaScript Maps SDK Questions

Spatial View help needed

SteveMacLean — Fri, 07 Feb 2014 19:48:42 GMT

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.

Re: Spatial View help needed

JeffPace — Fri, 07 Feb 2014 19:56:58 GMT

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?

Re: Spatial View help needed

SteveMacLean — Fri, 07 Feb 2014 20:09:45 GMT

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/002n000000t3000000/

Re: Spatial View help needed

JeffPace — Sat, 11 Dec 2021 16:18:44 GMT

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;

Re: Spatial View help needed

SteveMacLean — Fri, 07 Feb 2014 20:21:11 GMT

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

Re: Spatial View help needed

JeffPace — Fri, 07 Feb 2014 20:24:18 GMT

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?

Re: Spatial View help needed

SteveMacLean — Sat, 11 Dec 2021 16:18:46 GMT

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.

Re: Spatial View help needed

SteveMacLean — Sat, 11 Dec 2021 16:18:49 GMT

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

Re: Spatial View help needed

JeffPace — Sat, 11 Dec 2021 16:18:52 GMT

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?

Re: Spatial View help needed

NianweiLiu — Mon, 10 Feb 2014 19:59:35 GMT

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.

Re: Spatial View help needed

JeffPace — Mon, 10 Feb 2014 20:03:33 GMT

nliu is correct, you need to be able to identify in ArcMAP. I just tested my spatial view and it identifies fine in arcmap

You should be doing a spatial view, or a query layer, but not both.

Re: Spatial View help needed

SteveMacLean — Thu, 13 Feb 2014 16:05:42 GMT

I was able to create the spatial view using the sdetable -o create_view command. Everything works fine including the identify. I was not able to create the view like they recommend in 10.2 using the standard Oracle create view command and include objectid, and the shape. When you bring the feature into ArcMAP doing it that way it automatically creates a query layer. Using SDETable everything works perfectly.

Thanks everyone for your help.

Steve

Re: Spatial View help needed

JeffPace — Thu, 13 Feb 2014 16:16:29 GMT

I really hope someone from esri seems this.

This is exactly what I have been very vocal about them NOT taking away our commandline tools for SDE. Query layers just dont have the complete functionality we need yet.

Glad you got it working

-Jeff