Error when creating spatial index in 10g

619430

Hello.

I have a problen when I try to create a spatial index. The strange thing is that the same commands always works fine in some machines, but if always fails in others. I tryed in diferent versiones of Oracle, but I have the error in al of them. The versions I have tryed are:

- 10.2.0.1
- 10.2.0.4

The operating systems are:
Windows XP professional 32 bits
Windows 2003 Server 32 bits


These are the steps i make:

1) Create a Table with a SDO_GEOMETRY column (GEOMETRY)
2) Load data with SQLLDR (I hve tryed different SRID's, and all fail)
So far everything is ok

3) Create the INDEX
When I execute the CREATE INDEX command CREATE INDEX MADRID_SX ON MADRID (GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

I obtain the error:
ERROR en linea 1:+
ORA-29855: se ha producido un error en la ejecucion de la rutina+
ODCIINDEXCREATE+
ORA-13282: fallo al inicializar la transformacion de coordenadas+
ORA-06512: en "MDSYS.SDO_INDEX_METHOD_10I", line 10+


I too have noticed that if I execute the next command, I have an error:
SELECT MDSYS.sdo_cs.transform(sdo_geometry(2001,8192,sdo_point_type(13.6,52.4,null),null,null),25830) from dual;

ERROR en linea 1:+
ORA-13282: fallo al inicializar la transformacion de coordenadas+
ORA-06512: en "MDSYS.SDO_CS", linea 75+
ORA-06512: en "MDSYS.SDO_CS", linea 112+
ORA-06512: en "MDSYS.SDO_CS", linea 2678+


And if I execute the next command, I too have another error:
SELECT SDO_CS.VALIDATE_WKT(25830) FROM DUAL;
--------------------------------------------------
FALSE (169)*



Any ideas? Could it be related with something inside the machines, user privileges, etc.?

Thanks in advance.

sujnan

hello,
Have inserted metadata? If not insert the metadata.

INSERT INTO user_sdo_geom_metadata VALUES('TABLE_NAME', 'GEOMETRY',
sdo_dim_array( sdo_dim_element('X', -100,100, 0.000005),
sdo_dim_element('Y', -100,100, 0.000005),
null), NULL);

Refer Oracle Spatial User Guide for more info

Sujnan

619430

Hello.

Yes, I've inserted metadata (SRID among other data) in USER_SDO_GEOM_METADATA. I too have inserted each geometry with its SRID in the table that has Geometry (I made several test with different SRID's).

Thanks

Siva Ravada-Oracle

Problem seems to be with the SRIDs.

And if you are getting an error for
SELECT SDO_CS.VALIDATE_WKT(25830) FROM DUAL;

Then something is definitely wrong with your Coordinate system tables.

Can you check the WKT between the system that works and the system that does not work to see if
you can spot anything.

siva

aggieangler

Oracle changed the model for SRIDs between 10.2.0.1 and 10.2.0.4 allowing for more flexibility, but increasing the burden at setup time to initially define one. Are you using a custom SRID?

If it works in 10.2.0.1 and not in 10.2.0.4, I would suspect this. If so, you will want to look at this note, and search for others for more info:

1358822

HTH,

Jeff

619430

I have found that the problem is to use a SRID of AUTH_NAME column in MDSYS.CS_SRS table without the value "Oracle." in it.

If I use an Oracle’s SRID, everything works fine. If I use an EPSG’s SRID, fails.

For example, this command uses an Oracle SRID (8192) and one from the EPSG (25830), and fails:

SELECT MDSYS.sdo_cs.transform(sdo_geometry(2001,8192,sdo_point_type(13.6,52.4,null),null,null),25830) from dual;
ERROR en linea 1:
ORA-13282: fallo al inicializar la transformacion de coordenadas
ORA-06512: en "MDSYS.SDO_CS", linea 79
ORA-06512: en "MDSYS.SDO_CS", linea 116
ORA-06512: en "MDSYS.SDO_CS", linea 2690

However, if I use two Oracle SRID (8192 and 83030), it works.
SELECT MDSYS.sdo_cs.transform(sdo_geometry(2001,8192,sdo_point_type(13.6,52.4,null),null,null),83030) from dual;
SDO_GEOMETRY(2001, 83030, SDO_POINT_TYPE(1625183.71, 5936269.06, NULL), NULL, NULL

Therefore, the problem seems to be to use a non Oracle SRID.