使用Shp2sdo將空間數據導入Oracle

 以下操作以windows為例：

1、  準備好打斷路線的shp文件 （一般shp文件包括以下幾種文件）

Eg：

2、  在網上下載好shp2sdo.exe程序

我的做法是放在C:/Windows/System32中，然后就可以再windows命令窗口中直接使用shp2sdo命令。

Notice：如果下載的有三個包的話，最好用nt那個。

3、  在放在shp文件的目錄下，按住shift鍵的同時點擊鼠標右鍵，打開Windows命令窗口。

在windows命令窗口中輸入shp2sdo命令，系統會依次提示你輸入shp文件（不用帶擴展名）、輸出表名。

Notice：在此之后會出現一系列提示信息，全部按enter鍵跳過就行了。

最后在某個目錄下會生成三個文件：

LT_DTI_B.log文件是在執行入庫操作時生成的日志文件

正常步驟顯示如下：

4、  執行sql在數據庫中建表。

在windows命令窗口中輸入：

Sqlplus 用戶名/密碼@主機名：端口號/數據庫實例

Eg：sqlplus luk_jxgl/luk_jxgl@192.168.1.110:1521/jxlw

然后進入SQL命令行

輸入@先前生成的sql文件

Eg：@LT_DTI_B.sql

執行完sql后，退出sql模式。

5、  導入空間數據。

繼續在windows命令窗口中輸入：

Sqlldr 用戶名/密碼@主機名：端口號/數據庫實例

Eg：sqlldr luk_jxgl/luk_jxgl@192.168.1.110:1521/jxlwLT_DTI_B

6、  最后創建空間索引

首先進入sql命令行

Eg：sqlplus luk_jxgl/luk_jxgl@192.168.1.110:1521/jxlw

接著輸入sql命令

Eg：create index IDX_LT_DTI_B on LT_DTI_B(geom) indextype ismdsys.spatial_index;

完整執行步驟截圖：

……..提示信息

Shp2sdo使用說明文件

Exampleusage of the shape to sdo converter for Oracle9i Spatial and higher versions -

on WindowsNT:

shp2sdo.exestates states -g geom -d -x (-180,180) -y (-90,90) -s 8307 -t 0.5 -v

on SunSparc Solaris or linux:

shp2sdo.exestates states -g geom -d -x /(-180,180/) -y /(-90,90/) -s 8307 -t 0.5 -v

In thePRevious command:

states -the shapefile to convert (expects .dbf, .shp, and .shx files)

states -the name of the table to create and use in Oracle

-g  geom - geom is the name of the column to loadfor the geometry object

-d     - put the data in the control filegenerated by the tool

-x     - the bounds of the first dimension in thecoordinate system

-y     - the bounds of the second dimension inthe coordinate system

-s     - the SRID (spatial reference system ID)

-t     - the tolerance

-v     - verbose output

If thetarget database is Oracle8i, then use the -8 option on the

commandline.

Typeshp2sdo.exe -h for help:

shp2sdo.exe-h

shp2sdo -Shapefile(r) To Oracle Spatial Converter

Version2.14 09-Jan-2004

Copyright1997,2004 Oracle Corporation

For usewith Oracle Spatial.

USAGE:shp2sdo [-o] <shapefile> <tablename> -g <geometry column>

               -i <id column> -n<start_id> -p -d

               -x (xmin,xmax) -y (ymin,ymax) -s<srid>

  or

       shp2sdo -r <shapefile><outlayer> -c <ordcount> -n <start_gid> -a -d

               -x (xmin,xmax) -y (ymin,ymax)

    shapefile           - name of input shape file

                          (Do not includesuffix .shp .dbf or .shx)

    tablename           - spatial table name

                          if not specified:same as input file name

  Generic options:

    -o                  - Convert toobject/relational format (default)

    -r                  - Convert to the relationalformat

    -d                  - store data in the controlfile

                          if not specified:keep data in separate files

    -x                  - bounds for the X dimension

    -y                  - bounds for the Y dimension

    -v                  - verbose output

    -h or -?            - print this message

  Options valid for the object model only:

    -g geometry column  - Name of the column used for theSDO_GEOMETRY object

                          if not specified:GEOM

    -i id_column        -Name of the column used for numbering the geometries

                          if not specified, nokey column will be generated

                          if specified withoutname, use ID

    -n start_id         - Start number for IDs

                          if not specified, start at 1

    -p                  - Store points in theSDO_ORDINATES array

                          if not specified,store in SDO_POINT

    -s                  - Load SRID field in geometryand metadata

                          if not specified, SRID field is NULL

    -t                  - Load tolerance fields (xand y) in metadata

                          if not specified,tolerance fields are 0.00000005

    -8                  - Write control file in 8iformat

                          if not specified,file written in 9i format

    -f                  - Write geometry data with 10digits of precision

                          if not specified, 6digits of precision is used

  Options valid for the relational model only:

    -c ordcount         - Number of ordinates in _SDOGOEMtable

                          if not specified: 16ordinates

    -n start_gid        - Start number for GIDs

                          if not specified,start at 1

    -a                  - attributes go in _SDOGEOM table

                          if not specified,attributes are in separate table

Afterrunning the shp2sdo converter the next step is to create the Oracle table

and loadthe user_sdo_geom_metadata table.  Thefile used to do this is generated

by theconverter.  In the example above, itwould be called states.sql.  Log into

SQL*Plus asthe user who will own the layer, and type:

@states.sql.

After this,load the data using sql*loader.  In theabove example, assuming

user scottwith passWord tiger, do:

sqlldrscott/tiger states

After thelayer is loaded, the final requirement (for polygon layers only)

is tomigrate the layer to the 8.1.6 and higher format (fixes the SDO_GTYPES

and etypesas well as all polygon rotation and ordering requirements). 

Afterlogging into SQL*Plus:

If you areusing Oracle8i, type:

EXECUTESDO_MIGRATE.FROM_815_TO_81X('STATES');

If you areusing Oracle9i or higher, type:

EXECUTESDO_MIGRATE.TO_CURRENT('STATES','GEOM');