One to many view woes

MelissaKrieg · ‎11-02-2010

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.

RobertHu · ‎11-09-2010

I have a 1:M spatial view, too. The M is on the shape side. My solution is to create a feature class by "materializing" the spatial view, and use the view to update the FC periodically, say once a day at night or once a week in weekend. My users can only access the FC, so they do not have the ArcMap/ArcCatalog inconsistance.

Another benefit is that the performance of the FC is much better than the view. Because on my table side, it is a table view joining several tables in a complicated way. The spatial view actually is a view of a FC and a table view. Very slow.

Thanks!

Sorry. The M is on the table view side.

MelissaKrieg · ‎11-09-2010

Just this morning I was thinking about converting the view into a feature class. I'd need to create an automated process to keep it updated, but we already have several such proceses in place for other data. Unfortunately, I've decided that ESRI no longer supports 1:M views and will move on. 😞

My views perform very well and I'll have the added maintenance for the process, so I'm reluctant to go this route. But, I see no other choice.

JuliePaine · ‎11-16-2010

How did you 'materialize' the view to featureclass? (And get it to update daily?) I have a spatial view using a table view and a fc in a full outer join and was ready to abandon it because of drawing speed until I saw this thread. Any suggestions would be helpful. Thanks

RobertHu · ‎11-17-2010

It is roughly like these,
1. Create a GDB feature class (FC) and make sure the field schema matchs the spatial view (SV).
2. Build indexes on the FC as necessary.
3. Run sdeexport and directly pipe into sdeimport to populate the FC. In the attribute file, I will not include OBJECTID field,
sdeexport -o create -l <SV>,shape -a file=<att_file> -f - .... | sdeimport -o append -f - -l ....
4. Rebuild index (sdetable -o rebuild_index) and statistics (sdetable -o update_dbms_stats).

In the subsequent update, it is roughly as these,
1. Put FC in load only mode (sdelayer -o load_only_io)
2. Truncate it (sdetable -o truncate)
3. Step 3 above
4. Put FC back to normal mode (sdelayer -o normal_io)
5. Step 4 above.

I am doing these using C-shell script in Unix and batch file in windows. I think if you leave your views in the DB (I dynamically create the views before update and drop them after update in my script), you can easily accomplish all these using python/GP tools, except the rebuild_index.

Thanks!

MelissaKrieg · ‎11-17-2010

Yup, that's what we're doing as well, but with Python instead of C (rebuild index works fine in Python). We drive the Python script from a BAT file so we can schedule it in the OS of the applications server where the script lives. We also pipe output to a text file and use BLAT to email it to our team for verification.

CiaranDoyle · ‎12-01-2010

I also hit this problem recently and it is very annoying.

According to the last line of the "Using Database Views" page in the esri web-help, it would seem that this type of one-to-many view is not supported in ArcMap (although confusingly it appears to work as expected when viewed through ArcCatalog).
http://webhelp.esri.com/arcgiSDEsktop/9.3/index.cfm?TopicName=Using_database_views

This is backed up by this knowledge base article
http://resources.arcgis.com/content/kbase?fa=articleShow&d=35945

This behaviour is in direct contradiction of my understanding of what a database view should do and should be more explicit in the web help.

Even the esri staff-member that responded to this post doesn't seem to be aware of the shortcomings of spatial views.

Rant ends.

VinceAngelo · ‎12-02-2010

I've been familiar with ArcObjects' requirements for a unique registered rowid column
since 8.0 went beta. If you look at my view construction commands, both here and in the
old Forums, you'll see that I always take the objectid column from the many side, which
circumvents the Knowledge Base issue. Note that it's not ArcSDE which has an issue
with non-unique objectids -- Desktop has to juggle the relationship between attribute
metadata and graphics in the display pane; without a unique feature key to associate
them, that task becomes nearly impossible.

I just ran a trivial test of 1:M views with ArcSDE 10 (SQL-Server 2008) and ArcGIS 10,
and my spatial views continue to function as expected --

C:\Incoming>more 1toMone.txt 1toMone.ctl 1toMmany.txt 1toMmany.ctl | cat
::::::::::::::
1toMone.txt
::::::::::::::
AAAA|box:-10,6,-6,10
BBBB|polygon:6,6,10,6,8,10,6,6
CCCC|circle:0,0,3
DDDD|polygon:-6,-6,-10,-6,-8,-10,-6,-6
EEEE|box:6,-10,10,-6
::::::::::::::
1toMone.ctl
::::::::::::::
COORDSYS        GCS_WGS_1984
COORDREF_XY     -210,-120,1000000
EFLAGS          "na+"
DELIMITERS      "|"
REGISTER        objectid
COLUMNS
objectid        Sequence()                      -  10   N
keyval          String                          -   4   N
shape           Shape                           -   1   Y
END
::::::::::::::
1toMmany.txt
::::::::::::::
AAAA
BBBB
CCCC
DDDD
EEEE
BBBB
CCCC
DDDD
EEEE
CCCC
DDDD
EEEE
DDDD
EEEE
EEEE
::::::::::::::
1toMmany.ctl
::::::::::::::
REGISTER        objectid(SDE)
COLUMNS
objectid        Sequence()                      -  10   N
keyval          String                          -   4   N
END
 
C:\>asc2sde -o create -l otm_layer,shape -g 5 -f 1toMone.txt
ASCII to ArcSDE 10.0 Loader Utility      Thu Dec 02 07:57:28 2010
------------------------------------------------------------------------
Results:
        Records read: 5
        Rows created: 5
         Insert time: 218.00 ms (22.94 TPS)
        Elapsed time: 2.95 secs
 
C:\>asc2sde -o create -t otm_table -f 1toMmany.txt
ASCII to ArcSDE 10.0 Loader Utility      Thu Dec 02 07:58:53 2010
------------------------------------------------------------------------
Results:
        Records read: 15
        Rows created: 15
         Insert time: 16.00 ms (937.50 TPS)
        Elapsed time: 422.00 ms
 
C:\>sdetable -o create_view -T otm_view1 -t otm_layer,otm_table -w "otm_table.keyval = otm_layer.keyval" -c otm_table.objectid,otm_layer.keyval,otm_layer.shape
 
ArcSDE 10.0  for SQL Server Build 775 Fri Sep 17 11:45:27  2010
Attribute        Administration Utility
-----------------------------------------------------
Successfully created view otm_view1.
 
C:\>sdetable -o create_view -T otm_view2 -t otm_layer,otm_table -w "otm_table.keyval = otm_layer.keyval" -c otm_layer.objectid,otm_table.keyval,otm_layer.shape
 
ArcSDE 10.0  for SQL Server Build 775 Fri Sep 17 11:45:27  2010
Attribute        Administration Utility
-----------------------------------------------------
Successfully created view otm_view2.
 
C:\>sdequery -t otm_view1
ArcSDE 10.0 Generic Query Tool           Thu Dec 02 08:04:56 2010
------------------------------------------------------------------------
         1
            objectid: 1
              keyval: AAAA
               shape: Area shape (1 part, 5 vertices)
         2
            objectid: 2
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         3
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         4
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         5
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
         6
            objectid: 6
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         7
            objectid: 7
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         8
            objectid: 8
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         9
            objectid: 9
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        10
            objectid: 10
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
        11
            objectid: 11
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        12
            objectid: 12
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        13
            objectid: 13
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        14
            objectid: 14
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        15
            objectid: 15
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
15 rows found in 375.00 ms
 
C:\>sdequery -t otm_view2
ArcSDE 10.0 Generic Query Tool           Thu Dec 02 08:05:04 2010
------------------------------------------------------------------------
         1
            objectid: 1
              keyval: AAAA
               shape: Area shape (1 part, 5 vertices)
         2
            objectid: 2
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         3
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         4
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         5
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
         6
            objectid: 2
              keyval: BBBB
               shape: Area shape (1 part, 4 vertices)
         7
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
         8
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
         9
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        10
            objectid: 3
              keyval: CCCC
               shape: Area shape (1 part, 37 vertices)
        11
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        12
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        13
            objectid: 4
              keyval: DDDD
               shape: Area shape (1 part, 4 vertices)
        14
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
        15
            objectid: 5
              keyval: EEEE
               shape: Area shape (1 part, 5 vertices)
15 rows found in 250.00 ms
 
C:\>

The first attachment is a JPG of an identify on the lower-left triangle from using ArcSDE 10 with SQL-Server.
I repeated the process with ArcSDE 9.3.1sp2 on Oracle 10.2.0.3, and got the same behavior (second image).

If a spatial view with a unique registered rowid column doesn't behave properly while using a supported
database build, please bring it to the attention of Tech Support. They'll need a copy of your data (or the
smallest sanitized subset thereof which still exhibits the issue) to attempt reproduction and resolution.

BTW: Robert - You probably don't need to place the layer in load_only I/O mode before a truncate and
append -- the 'init' option of 'sdeimport' [nsic] does the truncate, load_only, insert, and normal_io in one
command.

- V

RobertHu · ‎12-02-2010

Hi Vince,

Thanks for your advice!

BTW: Robert - You probably don't need to place the layer in load_only I/O mode before a truncate and append -- the 'init' option of 'sdeexport' does the truncate, load_only, insert, and normal_io in one command.

You mean "sdeimport -o init ....", right?

VinceAngelo · ‎12-02-2010

Doh! Yes. - V

CiaranDoyle · ‎12-02-2010

thanks for the tip, vince

I've updated the knowledge base article with a link to this thread