Vector data uploading method for spatial databases ___ database

Reproduced from: http://publishblog.blogchina.com/blog/tb.b?diaryID=5349656

Absrtact: Using Oracle spatial to store and manage spatial data, it is easy to solve the problems of data sharing, distributed processing, network communication, open development, concurrency control, networked integration, cross-platform operation and data security recovery mechanism, and thus becomes an application trend. How to import the spatial data from the existing GIS software into the database becomes the primary key link of the technology application. Based on the basic principle of uploading GIS spatial data to Oracle spatial, this paper introduces various methods of uploading vector data and raster data in Oracle spatial, focusing on the use of Microsoft's ADO interface, Oracle spatial Java API and Occi interface, such as manual upload program implementation process. Finally, the conclusion and relevant suggestions are given.

1 Introduction

Spatial data is the blood of GIS, and the management of spatial data will directly affect the quality of GIS system. The management of spatial data in GIS has been three stages of management of graphic data and attribute data, graphic data file mode management and attribute data relational database management, spatial data and attribute data integration through the method of pure file management. At present, most GIS software tend to adopt the third management mode, that is, both graphic data and attribute data are used in the database management mode. For example, using Oracle spatial, DB2 spatial Extender, Informix spatial Datablade (currently Informix has been acquired by IBM) and ARCSDE database engine, MapGIS Mapora engine and so on.

Oracle spatial provides both object-relational and relational patterns to store spatial data. The former is characterized by the fact that there is a mdsys.sdo_geometry field in the Space table, the latter being the earlier version of Spatial data cassette (spatial cartridge) of spatial, which is characterized by four tables per space geometry layer, respectively _ Sdolayer, _sdodim, _sdogeom and _

Sdoindex. These tables do not include attribute data, and property data needs to be connected. At present, many GIS software companies provide support for Oracle spatial, such as Intergraph's GeoMedia 4.0, MapInfo MapInfo 6.0, Autodesk 6.0, ESRI's MapGuide, ArcGIS, as well as domestic companies in the Mapgis 6.5.

In this paper, we mainly study the principle and concrete implementation methods of uploading GIS vector data and raster data to Oracle spatial, and compare the advantages and disadvantages of several uploading methods on the basis of programming practice.

2 principle of uploading GIS spatial data to Oracle spatial

Uploading GIS spatial data to Oracle spatial, in essence, is to write graphical data of spatial data (including vector data and raster data) and corresponding attribute data to the database table. Therefore, it is important to know how the data format of the GIS spatial data and the spatial data in Oracle spatial are stored before uploading.

Each GIS software has its own internal data format and data storage mode, and most GIS software does not directly provide users with the function of reading and writing internal data. In order to convert data to other software, an external data interchange format is usually defined, such as MapInfo's *.mif/*.mid, Mapgis's plaintext format and ESRI's shape format (non-ASCII code format), and so on. Most of these external interchange formats are ASCII files, and the data structures for these interchange formats are detailed in the documentation published by the company.

Oracle spatial is an object-relational database that provides the type of storage space data that is sdo_geometry. With the introduction of Oracle 10g, spatial has been augmented with topological data model, network data model and raster data model, each of which has its own data type, such as type sdo_topogeometry used to store topological data. Be sure to know the properties and methods of these data types before uploading, please refer to references 1, 2, 3 for more information.

3 Methods of uploading GIS spatial data to Oracle spatial

The method of uploading spatial data can be divided into two methods: using the tools provided by the GIS software company and the manual way. This section mainly introduces the various uploading methods of vector data and raster data and their specific implementation process.

3.1 Method of uploading vector data

3.1.1 Upload using manual method

Users can upload GIS spatial data using interactive SQL statements using a variety of application programming interfaces (such as ADO, ODBC, and so on) to upload, and users can use the development language to invoke these interfaces to develop a wide variety of upload tools with their own application needs. That is to achieve manual loading. This section describes the process of implementing the upload of spatial data using the Microsoft ADO Interface and Oracle spatial Java APIs and OCCI interfaces .

3.1.1.1 using Java API upload

The Oracle spatial Java API (application programing Interface) provides 3 classes, namely Jgeometry, Jgeometry.point, and DataException. The Jgeometry class corresponds to the object type Mdsys.sdo_geometry,jgeometry.point class of Oracle spatial, which corresponds to the object type Mdsys.sdo_point_type. The DataException class represents an exception. The following example demonstrates how to use the Java API implementation to write GIS spatial data to Oracle spatial:

Reading spatial data from a database

ResultSet rs = statement.executequery ("Select Geoloc from countries where name= '");

STRUCT st = (Oracle.sql.STRUCT) rs.getobject (1);

To convert a struct to a geometry object

Jgeometry J_geom = Jgeometry.load (ST);

// ... Perform a space operation on a space object or create a new space object ...

To store space objects in a database

PreparedStatement PS = connection.preparestatement ("UPDATE countries set geometry=?") Where name= ' the ' ");

Converts the Jgeometry object to the structure of the database.

STRUCT obj = Jgeometry.store (j_geom, connection);

Ps.setobject (1, obj);

Ps.execute ();

3.1.1.2 Occi Upload Sample

The upload example is a complete sample program that uses Occi (Oracle C + + call Interface) to upload space data. First, use the Oracle database data type translation tool OTT (Oracle type Translator) translation type Mdsys.sdo_point_type and type Mdsys.sdo_geometry, and then enter at the command line,

Ott attraccess=private code=cpp cppfile=spatial_classeso.cpp hfile=spatial_classesh.h Intype=spatial_types.typ Mapfile=spatial_classesm.cpp mapfunc=registerclasses Userid=scott/tig

Er@gis

After the command is successfully executed, 4 files are generated, namely SPATIAL_CLASSESH.H, Spatial_classeso.cpp, spatial_classesm.h, and spatial_classesm.cpp;

Then, in Scott User mode, create a space table (that is, a table of stored space data) spatial, which implements the following SQL statements:

CREATE TABLE Spatial (Geoloc Mdsys. Sdo_geometry);

Finally, using VC + + to establish a project, the previous translation of the documents SPATIAL_CLASSESH.H, Spatial_classeso.cpp, spatial_classesm.h, and Spatial_classesm.cpp added to the project. The contents of the main file are,

#include

#include

#include

#include

#include "spatial_classesh.h"

#include "spatial_classesm.h"

using namespace Std;

using namespace Oracle::occi;

const int sdo_gtype_2dpoint = 2001;

const int Sdo_gtype_2dpolygon = 2003;

const int Sdo_etype_polygon = 1003;

const int sdo_interpretation_rectangle = 3;

void Main ()

{Try

{//initialization of environment variables in object mode

Environment *env = environment::createenvironment (Environment::object);

Registerclasses (env);//Register type function

Connection *conn = env->createconnection ("Scott", "Tiger", "GIS");

Try

{Statement *stmt = conn->createstatement ("Insert into spatial (Geoloc) VALUES (: 1)");

Storage space Object

Number Srid_null;

Sdopointtype *point_null = new Sdopointtype ();

Point_null->setnull ();

Sdogeometry *spatial_obj = new Sdogeometry ()//Create Object

Spatial_obj->setsdo_gtype (Sdo_gtype_2dpolygon);

Spatial_obj->setsdo_srid (srid_null);//do not set the coordinate system

Spatial_obj->setsdo_point (point_null);//Set point object is null

Vector Elem_info, ordinates;

Elem_info.clear ()//Empty Elem_info

Ordinates.clear ()//Empty ordinates

Storing Elem_info objects

Elem_info.push_back (1); Elem_info.push_back (Sdo_etype_polygon);

Elem_info.push_back (Sdo_interpretation_rectangle);

Spatial_obj->setsdo_elem_info (Elem_info);

Storage rectangle, using ordinates can store more than 4,000 characters of parameters, ADO is less than 4000

Ordinates.push_back (1); Ordinates.push_back (1);//(1,1)

Ordinates.push_back (5); Ordinates.push_back (7);//(5,7)

Spatial_obj->setsdo_ordinates (ordinates);

Stmt->setobject (1, spatial_obj);

Stmt->executeupdate ();

Delete spatial_obj;//Release Object

Conn->terminatestatement (stmt);}

catch (SQLException &ex)

{env->terminateconnection (conn);

Environment::terminateenvironment (env);

Throw }

Env->terminateconnection (conn);

Environment::terminateenvironment (env); }

catch (SQLException &ex)

{cout << "Error running Demo:" << ex.getmessage () << Endl;}}

After the compilation was successful, the project stored a rectangle in the spatial table of Oracle spatial. The user can extend the project and store the public format of the GIS software in ASCII code, such as MapInfo's *.mif/*.mid format, Mapgis point, Line, area, network file's plaintext data, etc.

3.1.1.3 using VB and ADO upload

There is a difference between the various application interfaces, and if ADO is used, there may be situations in which parameter passing does not meet the requirements, such as the use of ADO statements in VB6.0 below.

Set Adocommand = Nothing

Adocommand.commandtype = adCmdText

Adocommand.commandtext = "Insert into Spatialtable_name (spatial_column) values" & Spatialdata

Adocommand.activeconnection = ADOConnection

Adocommand.execute

Because the length of the CommandText property of the ADO Command object cannot be passed with a value of more than 4,000 characters. Therefore, for the longer space objects need to be truncated, so the use of ADO interface upload is not the best choice. This problem does not occur if you choose the application programming interface provided by Oracle (except that you can use oo4o, OCI, etc.) as mentioned earlier. Therefore, it is recommended to develop the upload program using the application programming interface provided by Oracle.

3.1.2 directly using tools to implement upload

Many GIS software companies now offer tools for uploading spatial data into Oracle spatial, such as Easyloader, ArcSDE, Mapgis, and so on.

3.1.2.1 Easyloader

Easyloader is an upload tool provided by MapInfo Company, but the tool only supports uploading *.tab data in MapInfo format. This tool provides both command-line upload and window upload methods. The tool writes data to the database at the bottom by invoking OCI (Oracle call Interface). The great advantage of using Easyloader to upload spatial data into Oracle spatial is that the uploaded spatial data is stored in object-relational mode and can take full advantage of the Oracle spatial object-relational schema. After the MAPINFO Professional is installed, the tool is installed by default and can be found under%mapinfo%\tools directory, and is also available on MAPINFO's official website.

3.1.2.2 Sql*loader and SHP2SDO

Sql*loader reads ASCII data based on instructions accepted from the control file and puts the data into the Oracle database. The control file notifies the Sql*loader where the data should be placed and describes the types of data loaded into the Oracle database. Sql*loader can also filter data (that is, do not load unsuitable data), and can load data into multiple tables and generate unique keywords or operational data before putting data into an Oracle table.

SHP2SDO is provided by Oracle to convert ESRI's *.shp files into a command-line tool that can use Sql*loader control files and data files. The tool not only converts *.shp into an object-relationship format, but can also be transformed into a relational format. This tool can be downloaded free of charge from the Oracle Company's Web site.

With Sql*loader and SHP2SDO, ESRI's shape files can be uploaded to Oracle spatial.

Tools provided by 3.1.2.3 ArcSDE and Mapgis

If you purchased ESRI's ARCSDE, you can use the Arctoolbox tool to upload data in ESRI format through the ARCSDE Space database engine. Like ArcMap or Arccatalog, Arctoolbox is an important member of the ArcGIS family who specializes in importing and exporting data. In addition to ESRI-formatted data (such as Shapefile, covage files), Arctoolbox also provides data conversion work in common file formats such as E00, DWG, and so on. In the data conversion, the user can set the conversion parameters according to the actual needs, such as the Space data table field name, spatial index coordinates of the most value. Arctoolbox provides us with a good data conversion tool. Similarly, if you are using a mapgis of 6.5 or a later version, you can upload mapgis spatial data using Mapgis's property Library management subsystem and the tools provided by the editing subsystem. Although ARCSDE is logically object-oriented, it is a purely relational table of Oracle that is physically used. Therefore, the current version of these two tools only supports the relational schema, which is the spatial cartridge pattern.

3.2 Uploading method of raster data

Starting with Oracle 10g, Oracle Spatial has added modules for storing raster data, as well as Java APIs for uploading raster data, Pl/sql APIs, and upload Tool georasterloader. The procedure for uploading raster data using Georasterloader is given below:

1 CREATE TABLE jpegs (jpg mdsys). Sdo_georaster);

2 Call Sdo_geor_utl.createdmltrigger (' JPEGs ', ' jpg ');

3) CREATE TABLE rdt1 of Mdsys. Sdo_raster (primary key (Rasterid, Pyramidlevel,bandblocknumber, Rowblocknumber, Columnblocknumber));

4 INSERT into JPEGs VALUES (Mdsys). Sdo_geor.init (' rdt1 '));

5) SELECT jpg from JPEGs;

The return value is sdo_georaster (null, NULL, ' RDT1 ', null)

6) COMMIT;

7 The command line tool in the operating system uses the following command to upload the raster image "C:\TEST.jpg",

Java Georasterloader gis01 GIS 1521 Scott Tiger thin T jpegs jpg "blocking=true,blocksize= (256,256,1)" "C:\test.jpg,22, Rdt1 ";

8 after successful upload, you can also use Georasterviewer to browse raster images, under the operating system command line tools to enter the following command,

Java Georasterloader gis01 GIS 1521 Scott Tiger Thin T;

When uploading raster data, because the current Oracle10.1.2 version of the raster data model does not support Chinese characters, users in the installation of Oracle 10g, you must choose to use English, but not by default.

4 Conclusion

(1) Using Oracle spatial storage and management of spatial data, it is easy to solve the problems of data sharing, distributed processing, network communication, open development, concurrency control, networked integration, cross-platform operation and data security recovery mechanism, so it becomes a current application trend. How to import the spatial data from the existing GIS software into the database becomes the primary key link of the technology application.

(2) Uploading GIS spatial data to Oracle spatial, in essence, is to write graphical data of spatial data (including vector data and raster data) and corresponding attribute data to the database table. A full understanding of how the GIS data is stored and the data types of Oracle spatial and the attributes and methods of its objects are the basis of the implementation of the upload program.

(3) The upload can be implemented using the tools provided by the GIS company or hand-written program to upload. In specific ways: if the GIS software provides the appropriate upload tool, these tools are recommended for uploading, as this enables the development of Oracle spatial applications using the corresponding two development components (for example, after uploading *.tab files using Easyloader), Oracle Spatial data sources can be used in MapInfo's mapx and mapxtreme for Java, and if there are no corresponding upload tools, users can develop upload tools themselves through the interfaces of ADO, OCI, OCCI, oo4o, Java APIs, and so on.

(4) In the implementation of Java API, OCCI and ADO three interfaces introduced in this paper, the long space object will be truncated because the command object of the ADO interface cannot pass the value when the CommandText attribute is longer than 4,000 characters. Therefore, it is recommended to use the Java API and OCCI interface provided by Oracle to develop the upload program.

(5) The oracle10g Oracle spatial option adds modules for storing raster data, as well as Java APIs, Pl/sql APIs, and upload tool georasterloader for uploading raster data. You can upload raster data using Georasterloader, but because the current Oracle10.1.2 version uses a raster data model that does not support Chinese characters, it is recommended that you choose to use English instead of the default in the Language option on installation.