How to import shpfiles to an Oracle database

When doing GIS engineering, we often use the most famous software in the GIS field of ARCGIS, and the most common file type of ARCGIS is shpfiles, which has three formats of file composition: shp, shx, and dbf. At the same time, the GIS project requires a large amount of geographic data. Therefore, it is vital to select a good database. The Oracle database has a strong advantage in space storage and query, therefore, you need to import the shpfiles to the oracle database for storage. Now the problem arises. oracle cannot identify the shpfiles file, so it cannot be imported directly. To solve this problem, I have summarized the following steps:

1. Split the data file:
First, we need to split the shp file into database files that can be imported to oracle. This conversion can be done using a ready-made tool shp2sdo provided by oracle, copy the downloaded file to the directory contained in the path variable. For example, the Environment Variable automatically registered after my oracle client is installed is path D: \ Oracle \ product \ 11.2.0 \ dbhome_1 \ BIN; run shp2sdo and enter the absolute path of the shpfiles file (without the extension). If the table name is not entered, it is generated in the original data folder. If only the file name is written (without the path) it is generated in the default oracle folder (D: \ Oracle \ product \ 11.2.0 \ dbhome_1 \ BIN). Therefore, we recommend that you enter the absolute path. run the command to generate three files: ctl, dat, and SQL (The names are the same as those of the preceding table ).

2. Generate a table and create an index:
Run this command in DOS (or directly open the sqlplus tool in D: \ Oracle \ product \ 11.2.0 \ dbhome_1 \ BIN): 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. load data:
Sqlldr scott/[password] control = D: \ data \ [name]. ctl
Otherwise, you must add double quotation marks to modify the dat file path in the ctl file!

Here, we have imported the shpfiles file into the oracle database. You can view it through the oracle interface tool developer.

 

In addition, you need to create a view. The specific function is not clear, but it will be used when you start the program to publish a map. If this view file is not available, the layer cannot be mapped, the procedure is as follows:

SQL> CREATE OR REPLACE VIEW CS_SRS
2 SELECT "CS_NAME", "SRID", "AUTH_SRID", "AUTH_NAME", "WKTEXT", "CS_BOUNDS"
3 from mdsys. CS_SRS
4/

In addition, if you manually create a layer, the process is as follows:

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

1 2 Next Page