External Tables and SDO_Geometry data.

I have blogged before on using an external table to load LiDAR data from a CSV file.

Today I am going to show how to load Open GeoSpatial Consortium Well Known Text (WKT) spatial data into Oracle.

 -- First let's generate some data to put in a text file.
 -- Note that I have chosen to use the @ symbol to separate fields because the WKT already uses a comma
 -- Additionally, I have enclosed the text field LABEL in double quotes
 --
 Select A.Id || '@"' || A.Label || '"@' || A.Angledegrees || '@'|| A.Geom.Get_Wkt()
   From projmultiline2d a;
  
 -- These records are saved to a text file called "LINES.CSV"
 -- Here are some sample records
 --
 -- 413@"E369"@138.5@MULTILINESTRING ((353939.49 5408647.49, 356176.87 5407976.99), (362085.52 5405637.39, 361539.69 5408568.96))
 -- 414@"J318"@251.3@MULTILINESTRING ((362731.43 5408178.05, 360601.03 5407188.5), (361788.72 5409654.04, 354179.78 5409014.27))
 -- 415@"V485"@309.8@MULTILINESTRING ((358775.23 5407751.35, 359482.97 5409145.58), (362924.05 5406099.8, 359476.0 5405402.33))
 -- 416@"I929"@56.7@MULTILINESTRING ((354898.32 5405632.43, 361058.09 5407528.99), (358365.02 5407617.08, 356320.21 5408776.58))
 -- 417@"J775"@267.3@MULTILINESTRING ((358452.34 5407176.96, 359657.75 5407859.83), (354193.59 5407366.76, 355039.55 5407778.69))
 -- 418@"B859"@338.5@Multilinestring ((359542.46 5407820.07, 355220.92 5409103.78), (357681.52 5406066.72, 355039.44 5408211.56))
 -- 419@"N420"@298.7@Multilinestring ((359909.51 5405734.05, 355086.05 5409832.58), (354228.93 5408457.61, 357882.47 5407889.09))
  
 -- Now let's define a directory so the External Table can find this data
 --
 Create Directory Ext_Temp_Dir As 'c:\temp';
  
 -- We can check it exists as follows
 --
 Select *
   From All_Directories
  Where directory_name = 'EXT_TEMP_DIR';
  
 OWNER DIRECTORY_NAME DIRECTORY_PATH
 ----- -------------- -------------
 SYS   EXT_TEMP_DIR  c:\temp
  
 -- Now Create an External Table over the data in lines.csv 
 -- 
 CREATE TABLE LINES_EXT (
     ID           NUMBER(*,0),
     LABEL        VARCHAR2(20 BYTE),
     ANGLEDEGREES Number,
     GEOMWKT      VARCHAR2(4000)    /* This could be CLOB */
   )
 ORGANIZATION EXTERNAL
 (Type Oracle_Loader
      DEFAULT DIRECTORY ext_temp_dir
      ACCESS PARAMETERS
        (Records Delimited By Newline
         Fields Terminated By '@'
         OPTIONALLY ENCLOSED BY '"'
        )
      LOCATION ('lines.csv')
     );
  
 -- We can now view/process the external data using straight SQL
 --
 Select Id,Label,Angledegrees,Sdo_Geometry(Geomwkt,Null) As Geom 
   From Lines_Ext;
  
 -- Now convert the GEOMWKT column into a real SDO_GEOMETRY in an internal table
 --
 Create Table Lines 
 As
 Select Id,Label,Angledegrees,Sdo_Geometry(Geomwkt,Null) As Geom 
   From Lines_Ext;
  
 -- Now we can spatially index it
 --
 Insert Into User_Sdo_Geom_Metadata (Table_Name,Column_Name,Diminfo,Srid)
 Values('LINES','GEOM',Mdsys.Sdo_Dim_Array(Mdsys.Sdo_Dim_Element('X',353885.69,5404973.19,0.05),Mdsys.Sdo_Dim_Element('Y',363874.6,5409972.56,0.05)),Null);
  
 commit;
  
 Create Index Lines_Geom_Spix
           On Lines(Geom)
        Indextype Is Mdsys.Spatial_Index
        Parameters('sdo_indx_dims=2, layer_gtype=multiline');

To clean up:

 Drop Table Lines_Ext;
 Drop Table Lines;
 Delete From User_Sdo_Geom_Metadata Where Table_Name = 'LINES';
 Commit;

This method can be used for any WKT data not just mutli-linestrings. It is a very nice alternative to trying to convert shapefiles or other binary files.

Note that external text files could also be compressed.

I hope this helps someone.