One to many view woes

[MelissaKrieg]

Is anyone successfully using 1:M views in ArcSDE 9.3.1 and Oracle 10g (10.2.0.2)?

I have been providing several 1:M views to users for years.  They stopped working correctly at SDE 9.2 and now work inconsistently at SDE 9.3.1.  By that I mean that the 1:M relationships show quite plainly as stacked polygons in ArcCatalog, but not in ArcMap.  And also that the 1:M views export incorrectly, i.e a user has 1500 features selected on the view and exports his selection to a shapefile only 500 features export.

As an example, I have a 1:M view where the '1' part of the view is a county feature class, and the 'M' is a table which has one or more rows per county.  In the view, I'm using an OBJECTID field from the M table in order to have a unique OBJECTID for the view.   In fact, all fields are coming from the 'M' table except for SHAPE which is coming from the '1' feature class. 

I've eliminated the following as contributors to the problem:
Oracle client version
BLOB vs. LONGRAW geometry storage
outer join (+) added to 'M' side of business table view post sde command line creation
OBJECTID for view coming from '1' feature class (results in duplicate OBJECTID)

Running traces in the database for views that mysteriously work versus views that do not tells me that an additional select on SHAPE.FID from the F view (see below) seems to make the difference.  But, I have no idea how ArcGIS generates this SQL from the sde view I created.  Does ArcGIS Desktop generate the SQL or does ArcSDE?

You can probably tell from the bind variables at the end of the SQL, but the below was captured from a DB trace while I was in ArcMap and used the identify tool on a county that has 2 records in the many table. 

SELECT /*+ LEADING INDEX(S_ S3060_IX1) INDEX(SHAPE) INDEX(COUNTIES_MAP_STATUS_VW A3060_IX1) */
       objectid,
       st_name,
       cnty_name,
       stcnty,
       agmt_type,
       incomplete_legal,
       arcmap_requested,
       update_requested,
       needs_review,
       polygen_error_general,
       polygen_error_no_grid,
       polygen_error_no_legal,
       polygen_error_no_lot,
       shape,
       shape.area,
       shape.len,
       shape.fid,
       s_.eminx,
       s_.eminy,
       s_.emaxx,
       s_.emaxy,
       shape.fid,
       shape.numofpts,
       shape.entity,
       shape.points,
       shape.ROWID
FROM (SELECT /*+ INDEX(SP_ S3060_IX1) */
            DISTINCT
             sp_fid, eminx, eminy, emaxx, emaxy
      FROM lease.s3166 sp_
      WHERE     sp_.gx >= :1
            AND sp_.gx <= :2
            AND sp_.gy >= :3
            AND sp_.gy <= :4
            AND sp_.eminx <= :5
            AND sp_.eminy <= :6
            AND sp_.emaxx >= :7
            AND sp_.emaxy >= :8) s_,
     lease.counties_map_status_vw,
     lease.f3166 shape
WHERE s_.sp_fid = shape.fid
      AND s_.sp_fid = lease.counties_map_status_vw.shape
...

I'm looking for help diagnosing why our 1:M views no longer work correctly, or help understanding how the spatial view is used by ArcGIS, or to hear from anyone using 1:M views successfully.

[VinceAngelo]

It would help if you posted the 'sdetable -o describe' output for the '1' and 'M' tables,
plus that of the view, and also posted the 'sdelayer -o describe_long' output for the
layer and the view. 

What command did you use to create the view?  Have you tried deleting and recreating
the view?

Note that Oracle 10.2.0.2 is not supported with ArcSDE 9.3.1 -- the minimum supported
release is Oracle 10.2.0.3.

- V

[MelissaKrieg]

'sdetable -o describe' output for the 'M' table
Table counties_map_status_data:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OBJECTID                SE_INT32         NOT NULL       10
ST_NAME                 SE_STRING        NULL           30
CNTY_NAME               SE_STRING        NULL           30
STCNTY                  SE_STRING        NULL            6
AGMT_TYPE               SE_STRING        NULL            3
INCOMPLETE_LEGAL        SE_FLOAT64       NULL           38,10
ARCMAP_REQUESTED        SE_FLOAT64       NULL           38,10
UPDATE_REQUESTED        SE_FLOAT64       NULL           38,10
NEEDS_REVIEW            SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_GENERAL   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_GRID   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LEGAL  SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LOT    SE_FLOAT64       NULL           38,10


'sdetable -o describe' output for the '1' table
Table counties_tobin:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OBJECTID                SE_INT32         NOT NULL       10        SDE Set
SNAME                   SE_NSTRING       NULL           20
CNAME                   SE_NSTRING       NULL           40
STATE                   SE_INT16         NULL            2
COUNTY                  SE_INT16         NULL            3
CLASS                   SE_INT16         NULL            2
TYPE                    SE_INT16         NULL            2
STCTY                   SE_INT32         NULL            5
MODDATE                 SE_DATE          NULL            0
SHAPE                   SE_SHAPE         NULL            0

'sdelayer -o describe_long' output for the feature class
ArcSDE 9.3.1  for Oracle10g Build 1632 Thu Feb 26 12:05:37  2009
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: BOUNDARY
Table Name ...........: COUNTIES_TOBIN
Spatial Column .......: SHAPE
Layer Id .............: 3060
SRID .................: 329
Minimum Shape Id .....: 1
Offset ...............:
  falsex:       -400.000000
  falsey:       -400.000000
System Units .........: 1000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
  parameter:    SPIDX_GRID,GRID0=3.3,FULL
  exist:        Yes
  array form:   3.3,0,0
Layer Envelope .......:
  minx:      -179.22783,        miny:        24.54290
  maxx:       -66.88582,        maxy:        71.46868
Entities .............: nac+
Layer Type ...........: SDE-LOB
Creation Date ........: 09/24/10 16:11:28
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT, UPDATE, INSERT, DELETE
Coordinate System ....: GEOGCS["GCS_North_American_1927",DATUM["D_North_American_1927",SPHEROID["Clarke_1866",6378206.4,294.9786982]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: GIS_DATA_BLOB

'sdelayer -o describe_long' output for the view
ArcSDE 9.3.1  for Oracle10g Build 1632 Thu Feb 26 12:05:37  2009
Layer    Administration Utility
-----------------------------------------------------
Layer Description ....: <None>
Table Owner ..........: LEASE
Table Name ...........: COUNTIES_MAP_STATUS_VW
Spatial Column .......: SHAPE
Layer Id .............: 3176
SRID .................: 329
Minimum Shape Id .....: 1
Offset ...............:
  falsex:       -400.000000
  falsey:       -400.000000
System Units .........: 1000000000.000000
Z Offset..............:          0.000000
Z Units ..............:          1.000000
Measure Offset .......: <None>
Measure Units ........: <None>
XY Cluster Tolerance .:          0.000000008983
Spatial Index ........:
  parameter:    SPIDX_GRID,GRID0=3.3,FULL
  exist:        Yes
  array form:   3.3,0,0
Layer Envelope .......:
  minx:      -179.22783,        miny:        24.54290
  maxx:       -66.88582,        maxy:        71.46868
Entities .............: nac+
Layer Type ...........: SDE-LOB
Creation Date ........: 11/02/10 09:24:56
I/O Mode .............: NORMAL
Autolocking ..........: Enabled
Precision.............: High
User Privileges ......: SELECT
Coordinate System ....: GEOGCS["GCS_North_American_1927",DATUM["D_North_American_1927",SPHEROID["Clarke_1866",6378206.4,294.9786982]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

Layer Configuration ..: GIS_DATA_BLOB

I'm using a script to create the view (see below), which I have used to recreate the view many times.

set SDESERVER=ftwuxsdetst
set SDEINSTANCE=5154
set SDEUSER=BOUNDARY
set SDEPASS=%1

REM## The owner of the schema containing the view must have the privileges
REM## necessary to either select, insert, update, or delete rows from all the
REM## tables or views on which the view is based that are not in the same schema.
REM## The owner must be granted these privileges directly, rather than through a
REM## role.  Also, the grant needs to include the WITH GRANT option (the -I
REM## parameter in SDE syntax).
sdetable -o grant -t COUNTIES_TOBIN -I -U LEASE -A SELECT -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

REM## Change user to LEASE
set SDEUSER=lease
set SDEPASS=%2

REM## Delete the view if it exists
sdetable -o delete -t COUNTIES_MAP_STATUS_VW -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS% -N

REM## Create map status view
sdetable -o create_view -T COUNTIES_MAP_STATUS_VW -t "BOUNDARY.COUNTIES_TOBIN c ,COUNTIES_MAP_STATUS_DATA d" -c

d.OBJECTID,d.ST_NAME,d.CNTY_NAME,d.STCNTY,d.AGMT_TYPE,d.INCOMPLETE_LEGAL,d.ARCMAP_REQUESTED,d.UPDATE_REQUESTED,d.NEEDS_REVIEW,d.POLYGEN_ERROR_GENERAL,d.POLYG

EN_ERROR_NO_GRID,d.POLYGEN_ERROR_NO_LEGAL,d.POLYGEN_ERROR_NO_LOT,c.SHAPE -w "c.STCTY=d.STCNTY" -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

REM## Grant SELECT on the new views to LAND_USER
sdetable -o grant -t COUNTIES_MAP_STATUS_VW -U LAND_USER -A SELECT -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

Do you think our Oracle version is the cause of our issue?

[MelissaKrieg]

You might notice that the data type on the two fields I'm using for the join are different.  I forgot to mention in my original post that this has been tested as well.  We updated the data type for STCTY in the M table to NUMBER(5), but it had no effect.  Here's the new describe on the M table.

'sdetable -o describe' output for the 'M' table
Table counties_map_status_data:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
OBJECTID                SE_INT32         NOT NULL       10
ST_NAME                 SE_STRING        NULL           30
CNTY_NAME               SE_STRING        NULL           30
STCNTY                  SE_INT32         NULL            5
AGMT_TYPE               SE_STRING        NULL            3
INCOMPLETE_LEGAL        SE_FLOAT64       NULL           38,10
ARCMAP_REQUESTED        SE_FLOAT64       NULL           38,10
UPDATE_REQUESTED        SE_FLOAT64       NULL           38,10
NEEDS_REVIEW            SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_GENERAL   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_GRID   SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LEGAL  SE_FLOAT64       NULL           38,10
POLYGEN_ERROR_NO_LOT    SE_FLOAT64       NULL           38,10

[VinceAngelo]

It certainly wouldn't hurt to use the supported Oracle release, but I'm not willing
to assign "cause" status until the problem goes away.

The only obvious thing I see is the use of an alias for the shape column.  ArcSDE
has a history of not handling aliases on the geometry column, so I'm more likely
to structure my view with:

f=nga_geonames_feature_p_3
n=nga_geonames_name_3
sdetable -o create_view -T nga_geonames_name_p_v3 -t %n%,%f% ^
    -c %n%.objectid,%n%.ufi,%n%.uni,%f%.shape... ^
    -w "%f%.ufi = %n%.nfi"

The projection defined on your layer is the "no projection defined" GCS from Arc/Info
days.  Since no one really used that projection for anything, especially global mapping,
you probably want to do some research to determine the actual datum used.

If the SDEUSER/... environment variables are defined, there's no real reason to specify
the -u/-i/-p/-s flags on your command directives.

It probably wouldn't hurt to alter the layer envelope to -180,-90,180,90.

Do you really want your flag variables defined FLOAT64,38.10 when you could map
them to just about any type, including INT16,1?

- V

[MelissaKrieg]

I'll start with your first suggestion.  I substituted the aliases for all columns with the fully qualified name, but there was no effect in the view.

My view creation command is now:
sdetable -o create_view -T COUNTIES_MAP_STATUS_VW -t BOUNDARY.COUNTIES_TOBIN,COUNTIES_MAP_STATUS_DATA -c "COUNTIES_MAP_STATUS_DATA.OBJECTID,COUNTIES_MAP_STATUS_DATA.ST_NAME,COUNTIES_MAP_STATUS_DATA.CNTY_NAME,COUNTIES_MAP_STATUS_DATA.STCNTY,COUNTIES_MAP_STATUS_DATA.AGMT_TYPE,COUNTIES_MAP_STATUS_DATA.INCOMPLETE_LEGAL,COUNTIES_MAP_STATUS_DATA.ARCMAP_REQUESTED,COUNTIES_MAP_STATUS_DATA.UPDATE_REQUESTED,COUNTIES_MAP_STATUS_DATA.NEEDS_REVIEW,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_GENERAL,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_NO_GRID,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_NO_LEGAL,COUNTIES_MAP_STATUS_DATA.POLYGEN_ERROR_NO_LOT,BOUNDARY.COUNTIES_TOBIN.SHAPE" -w "BOUNDARY.COUNTIES_TOBIN.STCTY=COUNTIES_MAP_STATUS_DATA.STCNTY" -s %SDESERVER% -i %SDEINSTANCE% -u %SDEUSER% -p %SDEPASS%

We're using the 'GCS_North_American_1927' coordinate system definition in ArcGIS.  How is that undefined?

We don't set up environment variables because we have several SDE databases we flip between, i.e. dev, test, and prod.

For the data types, what we see when we load the data is NUMBER, VARCHAR2, etc.  Where are the other definitions stored, DBTUNE?

[VinceAngelo]

Try driving your view from the many side -

... -t COUNTIES_MAP_STATUS_DATA,BOUNDARY.COUNTIES_TOBIN ...

This may impact spatial query performance, though.

Dealing with an incorrect coordinate system is a very complex issue. Your first
task is to determine what the proper coodsys of the source data was, and whether
the data has been reprojected from that. There may be Clarke 1866 data around,
but it isn't common (or precise, or accurate, especially for applications outside the
continental United States).

Your code *is* using the supported environment variables (except for SDEPASSWORD);
it's just cluttering the script to include "-i %SDEINSTANCE%" when SE_connection_create
will use the contents from SDEINSTANCE if no -i flag is specified.

The easiest way to have types map correctly is to define width limits at column
creation ("NUMBER(1)"). Correctling this after creation is possible, but difficult
(and if you hack the COLUMN_REGISTRY table, unsupported).

- V

[MelissaKrieg]

I switched the -t parameters around, but there was no effect in the view.  I'm curious what should that have changed and why would it effect performance?

The data is in GCS NAD27, it is not projected.  This coordinate system definition is old yes, but it's available in ArcGIS, so I still don't understand why you say it's incorrect?

We're pretty happy with how we're using lcoal environment variables, so let's shelve that one unless you think it could somehow contribute to the issues I'm seeing with the 1:M view.

For the M table, I did specify NUMBER(5) for the STCNY field, for example.  However, for the feature class it comes to us as an SDE export file and we load as-is.

[RobertHu]

Hi Melissa,

Are there lots of changes on the feature class and the joined tables in your main work hours (say, 8:00am - 5:30pm) every day? If so, does your business flow require those spatial view users to see live changes?

If you've figured out a solution for your issue, forget my questions. Thanks!

Robert

[MelissaKrieg]

The feature class (1 side of the view) changes only quarterly when we receive vendor updates.  The data table (the M side of the view) is truncated daily and repopulated, but does not change throughout the day.  Why do you ask?