在做GIS工程时,我们经常用到ARCGIS这一GIS领域里最有名的软件,而ARCGIS最常用的文件类型就是shpfiles,它有三种格式的文件组成:shp、shx和dbf。同时GIS工程需要用到大量地理数据,因此选择良好的数据库也是至关重要的,而oracle数据库在空间存储和查询方面有很强优势,因此就需要将shpfiles文件导入到oracle数据库里存储。这时问题便来了,oracle不能识别shpfiles文件,因此不能直接将其导入。就此问题我总结了一解决方法,步骤如下:
1.分割数据文件:
首先我们要把shp文件转换分割成oracle可以导入的数据库文件,这个转换可以使用oracle提供的一个现成的工具shp2sdo,下载后把此文件复制到PATH变量包含的目录下,如我的oracle客户端安装后自动注册的环境变量是 path D:\Oracle\product\11.2.0\dbhome_1\BIN;运行shp2sdo,输入shpfiles文件绝对路径(不带扩展名),在输入表名时若不写则生成在原数据文件夹里,若只写文件名(无路径)则生成在oracle默认文件夹里(D:\Oracle\product\11.2.0\dbhome_1\BIN),所以建议输入绝对路径.执行之后生成三个文件ctl,dat,sql(名字跟前面表名一致)。
2.生成表并建立索引:
在DOS运行此命令(或直接打开D:\Oracle\product\11.2.0\dbhome_1\BIN里的sqlplus工具):sqlplus pgg/pgg@orcl
SQL>@[d:\data\name].sql
SQL>CREATE Index [NAME]_idx ON [NAME] (GEOM) INDEXTYPE is MDSYS.SPATIAL_INDEX;
SQL>quit
3.载入数据:
sqlldr scott/[password] control=D:\data\[name].ctl
不行的话修改ctl文件中关于dat文件的路径,要加双引号!
到这里我们已经将shpfiles文件导入到oracle数据库中,可以通过oracle的界面工具developer来查看。
另外还需要建立一个视图,具体作用说不大清楚,但在启动程序发布地图的时候会用到,如果没有此视图文件会出现图层无法map的错误,建立过程如下:
SQL> CREATE OR REPLACE VIEW CS_SRS AS
2 SELECT "CS_NAME","SRID","AUTH_SRID","AUTH_NAME","WKTEXT","CS_BOUNDS"
3 FROM MDSYS.CS_SRS
4 /
另外如果自己手工新建图层的话,过程如下:
DROP TABLE BZXX;
CREATE TABLE BZXX(
GID VARCHAR2(32) NOT NULL,
NAME VARCHAR2(20),
BZNR VARCHAR2(400),
BEIZ VARCHAR2(600),
COOR MDSYS.SDO_GEOMETRY
);
ALTER TABLE BZXX
ADD CONSTRAINT PK_BZXX PRIMARY KEY (GID);
DELETE FROM USER_SDO_GEOM_METADATA
WHERE TABLE_NAME = 'BZXX' AND COLUMN_NAME = 'COOR' ;
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('BZXX', 'COOR',
MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', -180.000000000, 180.000000000, 0.500000000),
MDSYS.SDO_DIM_ELEMENT('Y', -90.000000000, 90.000000000, 0.500000000)
),
8307);
COMMIT;
CREATE INDEX BZXX_GEOM_INDEX ON BZXX(COOR)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('SDO_INDX_DIMS=2, LAYER_GTYPE=POINT');
************************以下为shp2sto使用说明及参数详解**************
Example usage of the shape to sdo converter for Oracle9i Spatial and higher versions -
on Windows NT:
shp2sdo.exe states states -g geom -d -x (-180,180) -y (-90,90) -s 8307 -t 0.5 -v
on Sun Sparc Solaris or Linux:
shp2sdo.exe states states -g geom -d -x \(-180,180\) -y \(-90,90\) -s 8307 -t 0.5 -v
In the previous 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 load for the geometry object
-d - put the data in the control file generated by the tool
-x - the bounds of the first dimension in the coordinate system
-y - the bounds of the second dimension in the coordinate system
-s - the SRID (spatial reference system ID)
-t - the tolerance
-v - verbose output
If the target database is Oracle8i, then use the -8 option on the
command line.
Type shp2sdo.exe -h for help:
shp2sdo.exe -h
shp2sdo - Shapefile(r) To Oracle Spatial Converter
Version 2.14 09-Jan-2004
Copyright 1997,2004 Oracle Corporation
For use with 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 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
补充:-d代表含义是将分解后的 ctl文件(控制文件)和data文件(数据存储文件)分别生成,如果没有该选项,则不会有单独的data文件生成,数据存储和控制都在ctl一个文件中, 经常用到的还有选项 -i id_colum指定id序列列,默认是id,即作为生成数据的唯一性标志,此列是不可能重复的,一般作为索引列,-s **指定生成srid,默认是null,目前一般是8307。
After running the shp2sdo converter the next step is to create the Oracle table
and load the user_sdo_geom_metadata table. The file used to do this is generated
by the converter. In the example above, it would be called states.sql. Log into
SQL*Plus as the user who will own the layer, and type:
@states.sql.
After this, load the data using sql*loader. In the above example, assuming
user scott with password tiger, do:
sqlldr scott/tiger states
After the layer is loaded, the final requirement (for polygon layers only)
is to migrate the layer to the 8.1.6 and higher format (fixes the SDO_GTYPES
and etypes as well as all polygon rotation and ordering requirements).
After logging into SQL*Plus:
If you are using Oracle8i, type:
EXECUTE SDO_MIGRATE.FROM_815_TO_81X('STATES');
If you are using Oracle9i or higher, type:
EXECUTE SDO_MIGRATE.TO_CURRENT('STATES','GEOM');