sdelayer -o add -R srid seems to set a different srid

[GeoffLau]

Environment:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
ArcSDE 10.0  for Oracle11g

Using st_geometry as the spatial column datatype.

I pre-create my table in sql with just my attribute columns, then use sdelayer -o add to add the objectid and st_geometry columns, using -R 30 to set the srid:

sdelayer -o add -l skate_park,shape -e a -t ST_GEOMETRY -R 30 -E 640000,6000000,740000,6120000 -C objectid,SDE -P HIGH -s prdapp008 -u cgdweb -p *****

Both SRIDs 30 and 45 are defined in st_spatial_references:
select srid, sr_name from st_spatial_references where srid in (30,45);
      SRID SR_NAME
---------- ----------------------
        30 GDA_1994_MGA_Zone_55
        45 ACT Stromlo Grid

After the sdelayer command, querying st_geometry_columns shows the srid has been set to 45, not 30:

select column_name, geometry_type, srid from st_geometry_columns
where owner = 'CGDWEB' and table_name = 'SKATE_PARK';

COLUMN_NAME                      GEOMETRY_TYPE                          SRID
-------------------------------- -------------------------------- ----------
SHAPE                            ST_GEOMETRY                              45

Using sdelayer -o alter ... -P HIGH -R 30 ... makes no change.

After I insert data in which the srid is set to 30 and then try to create a spatial index specifying srid 30 I get an error:

create index skate_park_idx0
on cgdweb.skate_park (shape)
indextype is sde.st_spatial_index
parameters('st_grids=100, 0, 0 st_srid=30');

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20085: Parameter ST_SRID 30 is different from ST_GEOMETRY_COLUMNS srid
(45).
ORA-06512: at "SDE.SPX_UTIL", line 1015
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1319

Using sdetable -o create to create the table instead of pre-creating it in sql gives the same result. Somehow srid 45 seems to be the local default!


If I pre-create the table including the objectid and shape columns in sql and then use sdelayer -o register, the correct srid is set and the index creation does not error. However only some of my records show when browsing the attribute table in ArcMap and are selectable. Other records appear on the map but are not selectable. Hence my attempts to allow sdelayer to add the objectid and shape columns.

Any suggestions?

[GeoffLau]

Update:

If I pre-create the table including the objectid and shape columns and then populate the table with data before using sdelayer -o add, then the correct srid 30 is set and the layer behaves correctly in ArcMap.

So this is a workaround but doesn't explain the behaviour of sdelayer -o add on the empty table.

[VinceAngelo]

If you're using ST_GEOMETRY, then there's no reason why you need to use
'sdelayer -o add' at all -- Just include all the columns in the CREATE TABLE
statement, and use 'sdelayer -o register' to register it with ArcSDE.

Please do contact Tech Support, because 'sdelayer -o add' has some new
"features" that need to removed.

-V

[GeoffLau]

Thanks Vince.

Yes, from the doco I think I should be able to use -o register, however for me it always errors if I specify the st_geometry type (but it works fine with sdo_geometry type):

C:\>sdelayer -o register -l crashes,shape -e p -t ST_GEOMETRY -R 30 -E 640000,6000000,740000,6120000 -C objectid,SDE -P HIGH -s prdapp008 -u cgdweb -p ****

ArcSDE 10.0  for Oracle11g Build 1343 Thu Feb 17 11:45:42  2011
Layer    Administration Utility
-----------------------------------------------------
Error: Wrong column type (-114).
Error: Cannot Create Layer.

But -o add does not error, so I use that:

C:\>sdelayer -o add -l crashes,shape -e p -t ST_GEOMETRY -R 30 -E 640000,6000000,740000,6120000 -C objectid,SDE -P HIGH -s prdapp008 -u cgdweb -p *****

ArcSDE 10.0  for Oracle11g Build 1343 Thu Feb 17 11:45:42  2011
Layer    Administration Utility
-----------------------------------------------------
Successfully Created Layer.

Just another of the bizarre eccentricities of the sde commands it seems. Another I struck today was in that one table with sdo_geometry, -o register insisted on adding an objectid column although I'd specified with -C another key column, while in a copy of the table in a different schema an identical -o register with just the different user and password did NOT create an objectid column.

Geoff

[VinceAngelo]

There are strict requirements on what is an acceptable rowid column.  It must
map to an SE_INT32_TYPE, and be NOT NULL.  Your errors indicate that the
column you chose did not qualify, and this would cause all the other commands
to fail as well.

- V