layer_gtype keyword issue when indexing linear data on 11g

I always recommend the setting of the layer_gtype property in the parameters of an mdsys.spatial_index. I have entered some details on this in a previous blog article.

There has been a change to the supported layer_gtype keywords in 11g with the keywords…

• LINESTRING
• MULTILINESTRING

… no longer being supported. Now, for linestrings and multi-linestrings (xx02, xx06), only the following are supported:

• LINE
• MULTILINE

Here is an sample script you can run yourself.

 Drop Table Lines;
 
 Create Table Lines (Id Number, Geom Mdsys.Sdo_Geometry); 
 
 Delete From User_Sdo_Geom_Metadata Where Table_Name = 'LINES' And Column_Name = 'GEOM';
 
 commit;
 
 Insert Into User_Sdo_Geom_Metadata Values('LINES','GEOM', Mdsys.Sdo_Dim_Array(Mdsys.Sdo_Dim_Element('X',141,142,0.005),Mdsys.Sdo_Dim_Element('Y',-37,-38,0.005)), 4283);
  
 commit;
  
 Insert Into Lines Values (1, Mdsys.Sdo_Geometry(2002, 4283, Null, Mdsys.Sdo_Elem_Info_Array(1,2,1), Mdsys.Sdo_Ordinate_Array(141.572473933333, -38.3540015, 141.572765733333, -38.3520586416667)));
  
 -- Try LINESTRING keyword
 -- 
 Create Index Lines_Geom_Spidx On Lines(Geom) Indextype Is Mdsys.Spatial_Index Parameters('sdo_indx_dims=2, layer_gtype=linestring');
  
 -- 10g Success ie Linestring works on 10gR2 
 index LINES_GEOM_SPIDX created.
  
 -- 11g Error ie Linestring fails on 11gR1 or 11gR2
 Error starting at line 10 in command:
 Create Index lines_geom_spidx On lines(geom) Indextype Is Mdsys.Spatial_Index Parameters('sdo_indx_dims=2, layer_gtype=linestring')
 Error at Command Line:10 Column:13
 Error report:
 SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
 ORA-13376: invalid type name specified for layer_gtype parameter
 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.
  
 -- Try LINE keyword
 Drop Index Lines_Geom_Spidx;
 index LINES_GEOM_SPIDX dropped.
  
 Create Index Lines_Geom_Spidx On Lines(Geom) Indextype Is Mdsys.Spatial_Index Parameters('sdo_indx_dims=2, layer_gtype=line');
 -- 10g Success ie Line works on 10gR2 
 index LINES_GEOM_SPIDX created.
 -- 11g Success ie Line works on 10gR2 
 index LINES_GEOM_SPIDX created.

Summary, for all spatial indexing on 10gR2 and beyond, use the LINE/MULTILINE keywords and not LINESTRING/MULTILINESTRING.

I hope this helps someone.