Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am having issues registering an inner join SQL view with a spatial column in ArcSDE. Any help would be much appreciated.

Here is the background info:

ArcSDE 10, SQL Server Enterprise 2008 R2, SQL 'Geometry' Spatial Type, Non-Versioned database, DBO schema, My Windows login is a sysadmin user

Here is the goal:

The view I am trying to register is an inner join of a polygon feature class to a table. The left of the join is the table, which in the database has a ONE_TO_MANY relationship with the feature class, where the feature class is the primary key as ONE and the table is the foreign key as MANY. I need to display in ArcMap the polygon geometries of all the feature class records as joined to the table. Since this is a ONE_TO_MANY relationship, it will result in coincident/duplicate polygon geometries, but that's OK.

Initially I am adding the view to SDE using sdetable -o create_view:

sdetable -o create_view -T Int_Veg_TEST -t "Interaction,Vegetation" -c
"Interaction.ObjectID AS INT_OID, Interaction.INT_UUID, Interaction.EVENT_UUID,
Vegetation.ObjectID AS VEG_OID, Vegetation.VEG_UUID, Vegetation.Shape" -w
"Interaction.EVENT_UUID = Vegetation.VEG_UUID" -i -D

This finishes OK, but the resulting view only displays the count and geometries from the right of the join (feature class) and I need it to display the feature class geometry and count as joined to the left (table). I would love to set the Row_ID when creating the view but that option is not available in -o create_view. When using the sdetable -o describe option, it shows that ArcSDE has pulled the feature class ObjectID field as the Row_ID:

Table Int_Veg_TEST:
Column name    Attribute    type    Null?  Length,DPs   RowID Column?
-------------------------------------------------------------------------------
INT_OID                  SE_INT32  NOT NULL   10
INT_UUID                 SE_UUID   NULL       38
EVENT_UUID               SE_UUID   NULL       38
VEG_OID                  SE_INT32  NOT NULL   10
VEG_UUID                 SE_UUID   NULL       38
SHAPE                    SE_SHAPE  NULL        0
OBJECTID                 SE_INT32  NOT NULL   10          SDE Set

From here I am having trouble using command line sdetable or sdelayer to update the Row_ID of an existing spatial view. Anyone know if this is possible using command line?

I can update the Row_ID manually in the sde_table_registry rowid_column, and it works. All polygon geometries for the join are displaying in ArcMap.

2 problems here though:

A: SDE still thinks it is maintaining the Row_ID. And I can't find where in the sde registry to manually update this to USER.

Table Int_Veg_TEST:
Column name   Attribute   type Null?   Length,DPs   RowID Column?

INT_OID       SE_INT32     NOT NULL        10          SDE Set
INT_UUID      SE_UUID      NULL            38
EVENT_UUID    SE_UUID      NULL            38
VEG_UUID      SE_UUID      NULL            38
VEG_OID       SE_INT32     NOT NULL        10
SHAPE         SE_SHAPE     NULL            0

B. I'd rather not update the SQL tables directly.

I've also tried creating a new view using SQL Server Management Studio and registering it in SDE using sdelayer -o register, but this also does not work:

SQL:

SET QUOTED_IDENTIFIER ON
GO 

CREATE VIEW [Int_Veg_TEST]

WITH SCHEMABINDING

AS 

SELECT dbo.INTERACTION.OBJECTID AS INT_OID, dbo.INTERACTION.INT_UUID,     
dbo.INTERACTION.EVENT_UUID, dbo.VEGETATION.OBJECTID AS VEG_OID, 
dbo.VEGETATION.VEG_UUID, dbo.VEGETATION.SHAPE

FROM dbo.INTERACTION
INNER JOIN dbo.VEGETATION ON dbo.INTERACTION.EVENT_UUID = dbo.VEGETATION.VEG_UUID

GO

CREATE UNIQUE CLUSTERED INDEX Ix_INT_OID_UNIQ_CLUSTERED
ON Int_Veg_TEST (INT_OID)

GO

command line:

sdelayer -o register -l Int_Veg_TEST,Shape -e a -t Geometry -C INT_OID,USER -i -D 

Error: Underlying DBMS error (-51).
Error: Cannot Create Layer.
Microsoft SQL Server Native Client 10.0: Cannot alter 'INT_VEG_TEST' because it is not   
a table.

More information is available.

Many thanks in advance for all replies.

Dan Schall --- Stowe, VT

share|improve this question
add comment

1 Answer

RESOLVED.

sde_layer error is a bug in ArcSDE 10 service packs 1 & 3. Service pack 2 works fine and ESRI tech support claims 10.1 will work fine too.

Dan

share|improve this answer
2  
Thanks for answering the question. Please go ahead and mark it as answered. –  Kirk Kuykendall Mar 2 '12 at 15:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.