【转】+【个人使用总结】Oracle 10g 导入空间数据

【自己操作】

1.下载shp2sdo.exe，把此文件复制到PATH变量包含的目录下，
如我的oracle客户端安装后自动注册的环境变量是 path  ：D:/oracle/product/10.2.0/db_1/bin。

2.在dos下定位到shp文件的目录，使用命令：cd /d 文件目录 进入需要操作的文件目录。（注意：文件目录不能含有汉字）

3.在文件目录下操作。输入命令为：shp2sdo shapefile tablename -g geom -d -s 8307 -t 0.5 -v，

可以根据说明定义各个参数，这样就运行了shp2sdo 工具。这样在文件夹中会生成两个文件（.sql和.ctl）。

4.连接数据库F:/test>sqlplus user/password@orcl，访问上一步已经建立的.sql文件SQL> @line.sql;

  提交操作SQL>quit

5.导入.ctl文件

F:/test>sqlldr user/password@orcl 第三步创建的.ctl文件名

 

附注：各参数的含义

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 include suffix .shp .dbf or .shx)
    tablename           - spatial table name
                          if not specified: same as input file name
  Generic options:
    -o                  - Convert to object/relational format (default)
    -r                  - Convert to the relational format
    -d                  - store data in the control file
                          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 the SDO_GEOMETRY object
                          if not specified: GEOM
    -i id_column        - Name of the column used for numbering the geometries
                          if not specified, no key column will be generated
                          if specified without name, use ID
    -n start_id         - Start number for IDs
                          if not specified, start at 1
    -p                  - Store points in the SDO_ORDINATES array
                          if not specified, store in SDO_POINT
    -s                  - Load SRID field in geometry and metadata
                          if not specified, SRID field is NULL
    -t                  - Load tolerance fields (x and y) in metadata
                          if not specified, tolerance fields are 0.00000005
    -8                  - Write control file in 8i format
                          if not specified, file written in 9i format
    -f                  - Write geometry data with 10 digits of precision
                          if not specified, 6 digits of precision is used
  Options valid for the relational model only:
    -c ordcount         - Number of ordinates in _SDOGOEM table
                          if not specified: 16 ordinates
    -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

 

【转】

Step 1
Download tool Oracle Shapefile Converter at http://www.oracle.com/technology/software/products/spatial/index.html and extract it into any directory.
Open the directory correspond with your operating system (ex: directory shp2sdo_nt for MS Windows), called CONVERT_HOME.
Call the directory that contains your shapefiles is SHAPE_HOME.
Call the directory that installs Oracle 10g is ORACLE_HOME.

Step 2
1. Open console, run the following commands:
> cd $SHAPE_HOME
> set path="$CONVERT_HOME"

2. Run shp2sdo converter:

• on Windows NT

shp2sdo.exe <file_name> <table_name> -g <geom_name> -d -x (<minx>,<maxx>) -y (<miny>,<maxy>) -s <SRS> -t <tolerance> -v

• on Sun Sparc Solaris or Linux

shp2sdo.exe <file_name> <table_name> -g <geom_name> -d -x /(<minx>,<maxx>/) -y /(<miny>,<maxy>/) -s <SRS> -t <tolerance> -v

Ex:
shp2sdo.exe tuyendg spa_tuyendg -g geom -d -x (575977.125,577368.125) -y (1195219.125,1196522.0) -s 32648 -t 0.5 -v

Note: Type shp2sdo.exe -h for help.


Step 3
After running shp2sdo converter, it creates 2 files:

• *.sql : to create oracle table.

• *.ctrl : to store data.

1. Open iSQL*Plus with database that you want to import, execute the content in *.sql (ex: @spa_tuyendg.sql).

2. Open file *.ctrl, insert line " CHARACTERSET UTF8" after line "LOAD DATA" and save it.

3. Open console, use Oracle SQL*Loader by running the following commands:
> set path="$ORACLE_HOME/product/10.1.0/db_1/BIN"
> sqlldr <username>/<password> <table_name>
Ex: sqlldr SYSTEM/oracle spa_tuyendg


Step 4
Open iSQL*Plus, execute the following commands:
CREATE INDEX <index_name> ON <table_name>(<geom_name> ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
EXECUTE SDO_MIGRATE.TO_CURRENT('<table_name>','<geom_name>');

Ex:
CREATE INDEX spa_tuyendg_spatial_idx ON spa_tuyendg(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
EXECUTE SDO_MIGRATE.TO_CURRENT('SPA_TUYENDG','GEOM');