4.1 Bulk Loading
Bulk loading can import large amounts of data into an Oracle database.
Bulk loading is accomplished with the SQL*Loader utility. (For information about SQL*Loader, see Oracle Database Utilities.)
Parent topic: Loading Spatial Data
4.1.1 Bulk Loading SDO_GEOMETRY Objects
Example 4-1 is the SQL*Loader control file for loading four geometries. When this control file is used with SQL*Loader, it loads the same cola market geometries that are inserted using SQL statements in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.
Example 4-1 Control File for a Bulk Load of Cola Market Geometries
LOAD DATA INFILE * TRUNCATE CONTINUEIF NEXT(1:1) = '#' INTO TABLE COLA_MARKETS FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( mkt_id INTEGER EXTERNAL, name CHAR, shape COLUMN OBJECT ( SDO_GTYPE INTEGER EXTERNAL, SDO_ELEM_INFO VARRAY TERMINATED BY '|/' (elements FLOAT EXTERNAL), SDO_ORDINATES VARRAY TERMINATED BY '|/' (ordinates FLOAT EXTERNAL) ) ) begindata 1|cola_a| #2003|1|1003|3|/ #1|1|5|7|/ 2|cola_b| #2003|1|1003|1|/ #5|1|8|1|8|6|5|7|5|1|/ 3|cola_c| #2003|1|1003|1|/ #3|3|6|3|6|5|4|5|3|3|/ 4|cola_d| #2003|1|1003|4|/ #8|7|10|9|8|11|/
Notes on Example 4-1:
-
The
EXTERNAL
keyword in the definitionmkt_id INTEGER EXTERNAL
means that each value to be inserted into the MKT_ID column (1, 2, 3, and 4 in this example) is an integer in human-readable form, not binary format. -
In the data after
begindata
, each MKT_ID value is preceded by one space, because theCONTINUEIF NEXT(1:1) = '#'
specification causes the first position of each data line to be ignored unless it is the number sign (#) continuation character.
Example 4-2 Control File for a Bulk Load of Polygons
Example 4-2 assumes that a table named POLY_4PT was created as follows:
CREATE TABLE POLY_4PT (GID VARCHAR2(32), GEOMETRY SDO_GEOMETRY);
Assume that the ASCII data consists of a file with delimited columns and separate rows fixed by the limits of the table with the following format:
geometry rows: GID, GEOMETRY
The coordinates in the GEOMETRY column represent polygons. Example 4-2 shows the control file for loading the data.
LOAD DATA INFILE * TRUNCATE CONTINUEIF NEXT(1:1) = '#' INTO TABLE POLY_4PT FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( GID INTEGER EXTERNAL, GEOMETRY COLUMN OBJECT ( SDO_GTYPE INTEGER EXTERNAL, SDO_ELEM_INFO VARRAY TERMINATED BY '|/' (elements FLOAT EXTERNAL), SDO_ORDINATES VARRAY TERMINATED BY '|/' (ordinates FLOAT EXTERNAL) ) ) begindata 1|2003|1|1003|1|/ #-122.4215|37.7862|-122.422|37.7869|-122.421|37.789|-122.42|37.7866| #-122.4215|37.7862|/ 2|2003|1|1003|1|/ #-122.4019|37.8052|-122.4027|37.8055|-122.4031|37.806|-122.4012|37.8052| #-122.4019|37.8052|/ 3|2003|1|1003|1|/ #-122.426|37.803|-122.4242|37.8053|-122.42355|37.8044|-122.4235|37.8025| #-122.426|37.803|/
Parent topic: Bulk Loading
4.1.2 Bulk Loading Point-Only Data in SDO_GEOMETRY Objects
Example 4-3 shows a control file for loading a table with point data. (The point coordinates happen to be in San Francisco, California, and reflect the Longitude/Latitude (WGS 84) coordinate system.)
Example 4-3 Control File for a Bulk Load of Point-Only Data
LOAD DATA INFILE * TRUNCATE CONTINUEIF NEXT(1:1) = '#' INTO TABLE POINT FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( GID INTEGER EXTERNAL, GEOMETRY COLUMN OBJECT ( SDO_GTYPE INTEGER EXTERNAL, SDO_POINT COLUMN OBJECT (X FLOAT EXTERNAL, Y FLOAT EXTERNAL) ) ) BEGINDATA 1| 2001| -122.4215| 37.7862| 2| 2001| -122.4019| 37.8052| 3| 2001| -122.426| 37.803| 4| 2001| -122.4171| 37.8034| 5| 2001| -122.416151| 37.8027228|
Parent topic: Bulk Loading