topic Re: Changing reference system with sde.st_transform but layer isnt visible in Arcmap after conversion in Data Management Questions

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

AndersMark — Sun, 25 Apr 2021 18:09:43 GMT

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:

Copy an existing feature class in arcmap for test. Lets say I copy feature class LAKE to LAKE_COPY.
Then I remove any spatial index on LAKE_COPY.
As the schema owner, with sql: UPDATE LAKE_COPY SET shape = sde.st_transform(shape,3006);
As SDE-user: UPDATE st_geometry_columns SET srid = 3006 WHERE table_name = 'LAKE_COPY';
Then I recreate the spatial index on LAKE_COPY.
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

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

DavidPike — Sun, 25 Apr 2021 19:46:30 GMT

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.

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

AndersMark — Mon, 26 Apr 2021 06:03:40 GMT

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.

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

DavidPike — Mon, 26 Apr 2021 14:32:00 GMT

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

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

AndersMark — Mon, 26 Apr 2021 14:40:00 GMT

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!

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

DavidPike — Mon, 26 Apr 2021 14:58:15 GMT

In SQL Developer not ArcMap

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

AndersMark — Mon, 26 Apr 2021 15:35:17 GMT

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.