This content has been marked as final. Show 5 replies
Would say YES, but try it by yourself:
DROP TABLE sdo_test; CREATE TABLE sdo_test ( nr NUMBER, GEOM SDO_GEOMETRY); COMMIT; TRUNCATE TABLE sdo_test; INSERT INTO sdo_test VALUES (1, SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.899771, 42.000853, 0, -87.899109, 42.000847, 54.8504622))); INSERT INTO sdo_test VALUES (2, SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.917489, 41.992077, 0, -87.917063, 41.99174, 51.4503307))); INSERT INTO sdo_test VALUES (3, SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-87.925704, 41.965994, 0, -87.925705, 41.965445, 60.9789892))); DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'SDO_TEST' AND COLUMN_NAME = 'GEOM' ; INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('SDO_TEST', 'GEOM', MDSYS.SDO_DIM_ARRAY (MDSYS.SDO_DIM_ELEMENT('X', -87.925705, -87.8991090, 0.001), MDSYS.SDO_DIM_ELEMENT('Y', 41.965445, 41.9654450, 0.001), MDSYS.SDO_DIM_ELEMENT('M', 0.00000, 60.9789892, 0.001) ),8307); CREATE INDEX sdo_test_geom_spix ON sdo_test(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('sdo_indx_dims=2'); INSERT INTO sdo_test VALUES (4, NULL); SELECT nr, SDO_GEOM.VALIDATE_GEOMETRY(geom,0.001) val FROM sdo_test; nr val ------------- 1 TRUE 2 TRUE 3 TRUE 4 NULL 4 rows selected.
I have a SDO_GEOM column in a table, which has NULL values for some records. When I create spatial Index on this column, I get error. I have to run spatial query based on this column so need spatial index on it.
Error starting at line 5 in command:
CREATE INDEX field_idx ON WM_DIM_FIELD(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
Error at Command Line:5 Column:14
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in spatial index: [mdrcrtxfergm]
ORA-13249: Error in spatial index: [mdpridxtxfergm]
ORA-13200: internal error [ROWID:AAAUMIAAEAAAl9TAAA] in spatial indexing.
ORA-13206: internal error  while creating the spatial index
ORA-13365: layer SRID does not match geometry SRID
ORA-13249: SRID 20002 does not exist in MDSYS.CS_SRS table
ORA-29400: data cartridge error
Error - OCI_NODATA
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.
Could you please suggest why is it happening? Is it due to NULL values in GEOM column.
I have created another question for this, The error which I posted here is not which I was getting, However why I was asking the question is resolved.
Yes we can have NULL data in SDO_GEOM fields and can be spatially indexed and spatial queries can be fired on that field.
Thanks everyone for looking into it.