Changing reference system with sde.st_transform but layer isnt visible in Arcmap after conversion

1225
6
04-25-2021 10:39 AM
Labels (2)
AndersMark
New Contributor II

Back in the days we used a spatial reference (SRID) of 2 in our databases. This was at the beginning of the st_geometry type era and what I remember the 3006 wasnt available. However the coding is exactly the same. For several reasons we now want to change this once and for all to 3006. According to this article this should be very easy: https://support.esri.com/en/technical-article/000010463

The problem is when I made all the steps the layer isn't visible any more in Arcmap. It doesn't draw anything. 

 

In short I do this:

  1. Copy an existing feature class in arcmap for test. Lets say I copy feature class LAKE to LAKE_COPY. 
  2. Then I remove any spatial index on LAKE_COPY.
  3. As the schema owner, with sql: UPDATE LAKE_COPY SET shape = sde.st_transform(shape,3006);
  4. As SDE-user: UPDATE st_geometry_columns SET srid = 3006 WHERE table_name = 'LAKE_COPY';
  5. Then I recreate the spatial index on LAKE_COPY.
  6. Now I try to view the converted layer in Arcmap but nothing shows up. 

When checking SDE.ST_GEOMETRY_COLUMNS, SDE.LAYERS everything seems to be updated correctly. If I check existing shape data in de LAKE_COPY feature class I can see that it now has 3006 as spatial reference. 

What has gone wrong? Should I do something else?  

 

Environment: ArcGIS 10.3.1 and Oracle 11.2

0 Kudos
6 Replies
DavidPike
MVP Frequent Contributor

I'd probably check the metadata for the geometry also (have a gander in the table in sql developer).  I think that would need to be updated.

AndersMark
New Contributor II

That sounds interesting. Can you be a little more specific about where I should check? In my test table I have verified that the SRID stored together with the geometries are correct. 

For example: SELECT DISTINCT a.shape.srid FROM LAKE_COPY a;

That gives me 3006 which now is the only unique number stored together with the geometry. I.e. the old number of 2 is replaced. 

 

0 Kudos
DavidPike
MVP Frequent Contributor

I've only experience with the SDO geometry type in Oracle unfortunately so the metadata table might not even exist.  In SDO geometry, the metadata has an SRID attribute.  To quickly view this in SQL developer 19.1 I would:

right-click on the table/FC in the connections pane -> Spatial -> Update Spatial Metadata

0 Kudos
AndersMark
New Contributor II

Ok maybe the problem behaves in a different way when you compare SDO_GEOMETRY and ST_GEOMETRY. If I check the FC in Arcmap I cant see the option "Update Spatial Metadata". 

Anyway, thanks for suggestions! 

 

 

0 Kudos
DavidPike
MVP Frequent Contributor

In SQL Developer not ArcMap

0 Kudos
AndersMark
New Contributor II

Yes I understand but my thought was that since ST_GEOMETRY is an ESRI datatype it can be managed within ArcGIS products. SDO_GEOMETRY is an Oracle datatype and that can be managed in SqlDeveloper. If I do what you suggest in SqlDev I cant see any spatial columns in my table. I guess that is because SqlDev searches for column type SDO_GEOMETRY.  

0 Kudos