Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

External Tables and SDO_Geometry data.

Tuesday July 05 2011 at 06:45

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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment