Search code examples
databaseoraclespatialarcgisspatial-index

ORA-20085: Different SRID's

Here is my case. I had 2 geodatabases. Second one is created later. And i import all tables in geodatabase-1 to geodatabase-2 by using ArcCatolog. I use srid 6 for my geodatabase 1 and its kind of a standard. But in geodatabase 2 the tables and indexes are imported with an different srid. I have to change srid of all geometry columns and spatial indexes to 6.

First I checked ST_spatial_references table for srid 6 and couldn't find it. There is srid 30002 with same content with srid 6 in geodatabase 1.

Then I create an entry in ST_Spatial_references table manually for srid 6. After that I tried to create a spatial index with following query.

 CREATE INDEX SDE.INDEX_NAME ON SDE.TABLE_NAME (SHAPE)
 INDEXTYPE IS SDE.ST_SPATIAL_INDEX
 PARAMETERS('st_grids=1:0:0 st_srid=6')
 NOPARALLEL;

and get this error:

ORA-20085: Different SRID '6' do not match.

I need to create and srid entry with id 6 properly. And change all the tables and spatial indexes set to srid 6. Can anyone help me?

Note: srid 30002 and 6 has exactly same content in ST_SPATIAL_REFERENCES table except the id column.

Thank You.

Solution

  • The reason you're seeing that error is that the SRID of a table (defined in the ST_GEOMETRY_COLUMNS table) doesn't match with the one of the geometries contained in it (defined in the geometry field of each record).

    If you really need to change the SRID of a table (even though you shouldn't mess with SRIDs, as they are internally managed by SDE), follow this procedure:

    • Create the entry in the ST_SPATIAL_REFERENCE table with SRID=6
    • Remove with ArcCatalog any existing spatial index on the table. If it doesn't work try to recreate it and then remove it.

    • Update the geometries contained in the table with the following SQL command:

      UPDATE b SET b.shape.srid = 6

    • Update to 6 the SRID of the table in the ST_GEOMETRY_COLUMNS table

    • Create the spatial index with ArcCatalog

    This has to be done for each table (except of course for the first step which has to be done only once).