Release 8.0.3
A53264_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8(TM) Server Spatial Cartridge User's Guide and Reference Release 8.0.3 A53264_01 |
|
This chapter describes how to load spatial data into a database, including storing the data in a table and creating a spatial index for it.
There are two steps involved in loading raw data into a spatial database such that it can be queried efficiently:
Table 2-1 shows the format of the tables needed to store and index spatial data.
The process of loading data can be classified into two categories:
Bulk loading can be used to import large amounts of legacy or ASCII data into a spatial database. Bulk loading is accomplished using the SQL*Loader1.
Example 2-1 shows the format of the raw data and control file that would be required to load the data into the SDOGEOM table with the layer name ROADS. You can choose any format of ASCII data as long you can write an SQL*Loader control file to load that data into the tables.
Assume that the ASCII data consists of a file with delimited (non-fixed) columns, and separate table-delimited rows with the following format:
geometry rows: GID, ESEQ, ETYPE, SEQ, LON1, LAT1, LON2, LAT2
The coordinates in the geometry rows represent the end points of line segments, which taken together represent a polygon. Example 2-2 shows the control file for loading the data into the geometry table:
LOAD DATA INFILE * INTO TABLE ROADS_SDOGEOM FIELDS TERMINATED BY WHITESPACE TRAILING NULLCOLS (SDO_GID INTEGER EXTERNAL, SDO_ESEQ INTEGER EXTERNAL, SDO_SEQ INTEGER EXTERNAL, SDO_X1 FLOAT EXTERNAL, SDO_Y1 FLOAT EXTERNAL, SDO_X2 FLOAT EXTERNAL, SDO_Y2 FLOAT EXTERNAL) BEGINDATA 1 0 3 0 -122.401200 37.805200 -122.401900 37.805200 1 0 3 1 -122.401900 37.805200 -122.402400 37.805500 1 0 3 2 -122.402400 37.805500 -122.403100 37.806000 1 0 3 3 -122.403100 37.806000 -122.404400 37.806800 1 0 3 4 -122.404400 37.806800 -122.401200 37.805200 1 1 3 0 -122.405900 37.806600 -122.407549 37.806394 1 1 3 1 -122.407549 37.806394 -122.408300 37.806300 1 1 3 2 -122.408300 37.806300 -122.409100 37.806200 1 1 3 3 -122.409100 37.806200 -122.405900 37.806600 2 0 2 0 -122.410800 37.806000 -122.412300 37.805800 2 0 2 1 -122.412300 37.805800 -122.414100 37.805600 2 0 2 2 -122.414100 37.805600 -122.412300 37.805800 2 0 2 3 -122.412300 37.805800 -122.410800 37.806000 3 0 1 0 -122.567474 38.643564 3 0 1 1 -126.345345 39.345345
Note that table ROADS_SDOGEOM exists in the schema before attempting the load.
In Example 2-3, the data resides in a single flat file and the data set consists of point, line string, and polygon data. The data uses fixed position columns and overloaded table rows of the form:
SDO_GID SDO_ESEQ SDO_ETYPE SDO_SEQ SDO_X1 SDO_Y1 SDO_X2 SDO_Y2
The corresponding control file for this format of input data would be:
LOAD DATA INFILE * INTO TABLE NEW_SDOGEOM (SDO_GID POSITION (1:5) INTEGER EXTERNAL, SDO_ESEQ POSITION (7:10) INTEGER EXTERNAL, SDO_ETYPE POSITION (12:15) INTEGER EXTERNAL, SDO_SEQ POSITION (17:21) INTEGER EXTERNAL, SDO_X1 POSITION (23:35) FLOAT EXTERNAL, SDO_Y1 POSITION (37:48) FLOAT EXTERNAL, SDO_X2 POSITION (50:62) FLOAT EXTERNAL, SDO_Y2 POSITION (64:75) FLOAT EXTERNAL) BEGINDATA 1 0 3 0 -122.401200 37.805200 -122.401900 37.805200 1 0 3 1 -122.401900 37.805200 -122.402400 37.805500 1 0 3 2 -122.402400 37.805500 -122.403100 37.806000 1 0 3 3 -122.403100 37.806000 -122.404400 37.806800 1 0 3 4 -122.404400 37.806800 -122.401200 37.805200 1 1 3 0 -122.405900 37.806600 -122.407549 37.806394 1 1 3 1 -122.407549 37.806394 -122.408300 37.806300 1 1 3 2 -122.408300 37.806300 -122.409100 37.806200 1 1 3 3 -122.409100 37.806200 -122.405900 37.806600 2 0 2 0 -122.410800 37.806000 -122.412300 37.805800 2 0 2 1 -122.412300 37.805800 -122.414100 37.805600 2 0 2 2 -122.414100 37.805600 -122.412300 37.805800 2 0 2 3 -122.412300 37.805800 -122.410800 37.806000 3 0 1 0 -122.567474 38.643564 3 0 1 1 -126.345345 39.345345
Spatial Cartridge uses standard Oracle8 tables that can be accessed or loaded with standard SQL syntax. Example 2-4 loads data for a geometry (GID 17) consisting of a polygon with five sides that contains both a hole and point. Notice that the first coordinate of the polygon (5, 20) is repeated at the end to close the polygon.
INSERT INTO SAMPLE_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, SDO_X3, SDO_Y3, SDO_X4, SDO_Y4, SDO_X5, SDO_Y5) VALUES (17, 0, 3, 0, 5, 20, 5, 30, 10, 30, 10, 20, 5, 20); -- hole INSERT INTO SAMPLE_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, SDO_X3, SDO_Y3, SDO_X4, SDO_Y4, SDO_X5, SDO_Y5) VALUES (17, 1, 3, 0, 8, 21, 8, 24, 9, 24, 9, 21, 8, 21); -- point INSERT INTO SAMPLE_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1) VALUES (17, 6, 1, 0, 9, 29);
The SQL INSERT statement inserts one row of data per call. In Example 2-4, the table had enough columns to store the polygon in a single row. However, if your table had fewer columns (or your polygon had more points), you would have to break the insert into multiple lines to match the table structure. Repeat the SDO_GID, SDO_ESEQ, and SDO_ETYPE, and increment the SDO_SEQ for each line as shown in Example 2-5:
INSERT INTO SAMPLE2_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, SDO_X3, SDO_Y3, SDO_X4, SDO_Y4, SDO_X5, SDO_Y5) VALUES (18, 0, 3, 0, 1, 15, 1, 16, 2, 17, 3, 17, 4, 18); INSERT INTO SAMPLE2_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, SDO_X3, SDO_Y3, SDO_X4, SDO_Y4, SDO_X5, SDO_Y5) VALUES (18, 0, 3, 1, 4, 18, 5, 18, 6, 19, 7, 18, 6, 17); INSERT INTO SAMPLE2_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, SDO_X3, SDO_Y3, SDO_X4, SDO_Y4, SDO_X5, SDO_Y5) VALUES (18, 0, 3, 2, 6, 17, 7, 16, 7, 15, 6, 14, 7, 13); INSERT INTO SAMPLE2_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, SDO_X3, SDO_Y3, SDO_X4, SDO_Y4, SDO_X5, SDO_Y5) VALUES (18, 0, 3, 3, 7, 13, 6, 12, 5, 13, 4, 13, 3, 14); INSERT INTO SAMPLE2_SDOGEOM (SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, SDO_X3, SDO_Y3) VALUES (18, 0, 3, 4, 3, 14, 2, 14, 1, 15);
Spatial Cartridge provides two functions to facilitate inserting data into spatial tables. A benefit to using these functions is that the issue of row-wrapping when loading elements with multiple points is handled automatically by these functions.
There are two steps to incrementally add data to the spatial tables:
SDO_GEOM.INIT_ELEMENT() function, which initializes the element:
The SDO_GEOM.INIT_ELEMENT() function returns the sequence number of the element in the geometry. This sequence number is required as a parameter to the SDO_GEOM.ADD_NODES() procedure.
SDO_GEOM.ADD_NODES() procedure. This procedure takes the following parameters:
In Example 2-6, a simple polygon, geometry number 1234, consisting of five vertices needs to be stored. The first step is to call SDO_GEOM.INIT_ELEMENT() to initialize the element.
elem_value := sdo_geom.init_element('ROADS', 1234);
Next, call SDO_GEOM.ADD_NODES() to fill in the attributes of the polygon. The vertices can be added in either clockwise or counter-clockwise order.
sdo_geom.add_nodes('ROADS', 1234, elem_value, sdogeom.polygon_type, Ax, Ay, Bx, By, Cx, Cy, Dx, Dy, Ex, Ey, Ax, Ay));
Close the polygon by repeating the first vertex (Ax,Ay) as the last vertex.
In Example 2-7, assume that the geometry shown in Figure 2-2 needs to be stored. The geometry consists of a polygon with a hole in it. Note that both calls to the SDO_GEOM.ADD_NODES() procedure are made with the same GID (6789) because this is a single object even though it is composed of two elements.
val1 := sdo_geom.init_element('PARKS', 6789); sdo_geom.add_nodes('PARKS', 6789, val1, SDO_GEOM.POLYGON_TYPE, p1x, p1y, p2x, p2y,, p3x, p3y, p4x, p4y, p5x, p5y, p6x, p6y, p1x, p1y); val2 := sdo_geom.init_element('PARKS', 6789); sdo_geom.add_nodes('PARKS', 6789, val2, g1x, g1y, g2x, g2y, g3x, g3y , g4x, g4y, g1x, g1y);
Once data has been loaded into the spatial tables through either bulk or transactional loading, a spatial index needs to be created on the tables for efficient access to the data.
Create a table called <layername>_SDOINDEX with the following structure:
(SDO_GID integer, SDO_CODE raw(255))
For a bulk load, you can call the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure once to tessellate the geometry table and add the generated tiles to the spatial index table. The arguments to this procedure are the name of the layer, the level that the geometry should be tessellated to, and a flag to determine if the entire geometry table should be tessellated or just the objects that do not have corresponding entries in the spatial index table. See Chapter 5, "Administrative Procedures" for a complete description of the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure.
If data is updated in or deleted from a specific geometry table, you can call SDO_ADMIN.UPDATE_INDEX_FIXED() to update the index for one SDO_GID. The arguments to this procedure are the name of the layer, the SDO_GID of the designated geometry, the level the geometry should be tessellated to, and a replace flag. The replace flag signals whether or not to delete any existing tiles for the geometry prior to tessellating.
Spatial Cartridge uses two different tessellation schemes for creating cover tiles for a geometry. Most of the examples in this manual describe indexing with fixed-size tiles because that is generally the most efficient for most cases. However, in some cases an application might call for variable-sized tiles. Tessellating the geometry into fixed-size tiles might have benefits related to the type of data being stored, such as using tiles sized to represent 1-acre farm plots, city blocks, or individual pixels on a display.
The domain of the HHCODE used for indexing is defined by the upper and lower boundaries of each dimension stored in the <layername>_SDODIM table. A typical domain in a GIS application could be -90 to 90 degrees for latitude, and -180 to 180 degrees for longitude, as represented in Figure 2-3.
To generate fixed-size tiles, use the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure and pass it the level to which the tiles should be tessellated. For example, setting the tile_size parameter to 1 would specify one subdivision. Each tile would be 180 degrees by 90 degrees as shown in Figure 2-4:
The formula for the number of fixed-size tiles is 4n where n is the tile_size (or level) parameter passed to the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure. Figure 2-5 shows fixed-size tiling at level 2. In this figure, each tile is 90 degrees by 45 degrees.
Assume that data has been loaded into a layer called ROADS. To create a spatial index, create a table ROADS_SDOINDEX and invoke the following procedure:
sdo_admin.populate_index_fixed('ROADS', 10, false, false, false);
The number 10 in the parameter list specifies the level of tiles to which an object must be tessellated. This parameter can be used as a tuning parameter while tessellating objects. Increasing the level increases the number of tiles to provide a more precise fit of the tiles over the object.
After SDO_ADMIN.POPULATE_INDEX_FIXED() has been called to fill the spatial index, you should also create standard indexes on the <layername>SDOINDEX.SDO_CODE column.
If a geometry with an SDO_GID 5944 has been added to the spatial tables, update the index with the following procedure:
sdo_admin.update_index_fixed('ROADS', 5944, 10, FALSE, FALSE, FALSE);
SDO_ADMIN.POPULATE_INDEX() and SDO_ADMIN.POPULATE_INDEX_FIXED() behave similarly to the CREATE INDEX statement in SQL. An implicit commit is executed after the procedures are called. SDO_ADMIN.UPDATE_INDEX() and SDO_ADMIN.UPDATE_INDEX_FIXED() do not perform an implicit commit after they execute and therefore these transactions can be rolled back.
SDO_ADMIN.POPULATE_INDEX() and SDO_ADMIN.POPULATE_INDEX_FIXED()operate as a single transaction. To reduce the amount of rollback required to execute these functions, you can write a routine that loops and calls SDO_ADMIN.UPDATE_INDEX() or SDO_ADMIN.UPDATE_INDEX_FIXED(). See Section A.2, "cr_spatial_index.sql Script" for more information.
1
See the Oracle Server Utilities User's Guide for information on the SQL*Loader.
