oracle导入空间数据

使用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');