使用DGN2SDO装载数据到Oracle Spatial

DGN2SDO是Bentley在GSX(GeoSpatial Extension)中提供的命令行数据装载工具。它可以把DGN数据转换为SQL Loader脚本。

以下通过一个例子介绍它的使用。首先我们可以创建一个批处理文件以方便我们多次使用。

文件名：load2sdo.bat

文件内容：

set  dgn2sdo=C:\ms08090365en\GeospatialExtension\bin\dgn2sdo.exe

set inmetadb=gsx/gsx@dev

%dgn2sdo% IN_FILE=C:\usa.dgn IN_TYPE=XFM IN_METADB=%inmetadb% IN_WU=usa_imp IN_FEATURE=roadtrl020

DGN2SDO支持DGN/XFM两种类型数据的转换，必须的参数为IN_TYPE、IN_FILE、IN_METADB。IN_TYPE的值为DGN或XFM；IN_FILE的值为文件路径名列表，可以使用通配符；IN_METADB为Oracle数据库连接信息。

对于DGN模式，必须的参数为IN_GEOM_TYPE、OUT_GEOM_TABLE。IN_GEOM_TYPE的值为Oracle geometry类型；OUT_GEOM_TABLE的值为输出的表名。

对于XFM模式，必须的参数为IN_WU、IN_FEATURE。IN_WU的值为XFM工作空间名；IN_FEATURE为转换的要素名称列表。

其他可选参数参见文档。

运行load2sdo.bat会生成output目录，里面有日志和SQL Loader文件(.SQL, .CTL, .DAT)。

然后运行生成的MasterLoad.BAT文件，将会自动加载数据到Oracle数据库。

GeomTableLoad_roadtrl020.log内容：

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Jun 29 13:11:46 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   C:\output\GeomTableLoad_roadtrl020.CTL2A
Data File:      Feature_ROADTRL020.dat
 Bad File:     C:\output\Feature_ROADTRL020.bad
 Discard File:  none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:   1:1 = 0X23(character '#'), in next physical record
Path used:      Conventional

Table ROADTRL020, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

  Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FNODE_                              FIRST     *   |       CHARACTER            
TNODE_                               NEXT     *   |       CHARACTER            
LPOLY_                               NEXT     *   |       CHARACTER            
RPOLY_                               NEXT     *   |       CHARACTER            
LENGTH                               NEXT     *   |       CHARACTER            
ROADTRL020                           NEXT     *   |       CHARACTER            
FEATURE                              NEXT     *   |       CHARACTER            
   NULL if FEATURE = BLANKS
NAME                                 NEXT     *   |       CHARACTER            
   NULL if NAME = BLANKS
STATE_FIPS                           NEXT     *   |       CHARACTER            
   NULL if STATE_FIPS = BLANKS
STATE                                NEXT     *   |       CHARACTER            
   NULL if STATE = BLANKS
OGC_GEOMETRY                      DERIVED     *           COLUMN OBJECT        

*** Fields in OGC_GEOMETRY
SDO_GTYPE                            NEXT     *   |       CHARACTER            
SDO_SRID                             NEXT     *   |       CHARACTER            
SDO_ELEM_INFO                     DERIVED     *   /       VARRAY              

*** Fields in OGC_GEOMETRY.SDO_ELEM_INFO
E                                   FIRST     *   |       CHARACTER            
*** End of fields in OGC_GEOMETRY.SDO_ELEM_INFO

SDO_ORDINATES                     DERIVED     *   /       VARRAY              

*** Fields in OGC_GEOMETRY.SDO_ORDINATES
O                                   FIRST     *   |       CHARACTER            
*** End of fields in OGC_GEOMETRY.SDO_ORDINATES

*** End of fields in OGC_GEOMETRY

XFM_ID                               NEXT     *   |       CHARACTER            
   NULL if XFM_ID = BLANKS

Table ROADTRL020:
 47014 Rows successfully loaded.
 0 Rows not loaded due to data errors.
 0 Rows not loaded because all WHEN clauses were failed.
 0 Rows not loaded because all fields were null.

Space allocated for bind array:                 248192 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:         47014
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Jun 29 13:11:46 2007
Run ended on Fri Jun 29 13:12:15 2007

Elapsed time was:     00:00:28.68
CPU time was:         00:00:08.36

创建了一个新的ROADTRL020空间数据表。

DGN2SDO.log内容：

2007-6-29 12:56:56
Output directory has been set to (C:\output)
Loading the Geospatial Extension
Opening design file "C:\usa.dgn"
Obtaining instance cursor

   
       
   

Commencing translation
Total instances read = 47014
Total instances written = 47014
Translation completed
Closed design file "C:\usa.dgn"
Generating scripts for (roadtrl020).
Generating master batch file (MasterLoad).
2007-6-29 13:01:34

可以看到转换了47014个要素，耗时4分38秒(在MS VPC2004中运行的结果)。