Release 8.1.7
Part Number A85337-01
 Library |
 Product |
 Contents |
 Index |
| Oracle Spatial User's Guide and Reference Release 8.1.7 Part Number A85337-01 |
|
This chapter describes how to load spatial data into a database, including storing the data in a table with a column of type SDO_GEOMETRY and creating a spatial index for it.
The following steps will enable you to query spatial data efficiently:
The process of loading data can be classified into two categories:
This process is used to load large volumes of data into the database and uses the SQL*Loader utility to load the data.
This process is used to insert relatively small amounts of data into the database using the INSERT statement in SQL.
Bulk loading can import large amounts of ASCII data into an Oracle database. Bulk loading is accomplished with the SQL*Loader utility. (For information about SQL*Loader, see Oracle8i Utilities.)
The following example assumes that a table named POLY_4PT was created as follows:
CREATE TABLE POLY_4PT (GID VARCHAR2(32), GEOMETRY MDSYS.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 roads for a region. Example 3-1 shows the control file for loading the roads and attributes.
LOAD DATA INFILE * TRUNCATE CONTINUEIF NEXT(1:1) = '#' INTO TABLE POLY_4PT FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( GID INTEGER EXTERNAL, GEOM COLUMN OBJECT ( SDO_GTYPE INTEGER EXTERNAL, SDO_ELEM_INFO VARRAY TERMINATED BY '|/' (X FLOAT EXTERNAL), SDO_ORDINATES VARRAY TERMINATED BY '|/' (Y 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|/
Example 3-2 shows a control file for loading a table with point 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|
Oracle Spatial uses standard Oracle8i tables that can be accessed or loaded with standard SQL syntax. This section contains examples of transactional inserts into columns of type SDO_GEOMETRY. Note that the INSERT statement in Oracle8i SQL has a limit of 999 arguments. Therefore, you cannot create a variable-length array of more than 999 elements using the SDO_GEOMETRY constructor inside a transactional INSERT statement; however, you can insert a geometry using a host variable, and the host variable can be built using the SDO_GEOMETRY constructor with more than 999 values in the SDO_ORDINATE_ARRAY specification. (The host variable is an OCI, PL/SQL, or Java program variable.)
To perform transactional insertions of geometries, you can create a procedure to insert a geometry, and then invoke that procedure on each geometry to be inserted. Example 3-3 creates a procedure to perform the insert operation.
CREATE OR REPLACE PROCEDURE INSERT_GEOM(GEOM MDSYS.SDO_GEOMETRY) IS BEGIN INSERT INTO TEST_1 VALUES (GEOM); COMMIT; END; /
Using the procedure created in Example 3-3, you can insert data by using a PL/SQL block, such as the one in Example 3-4, which loads a geometry into the variable named geom and then invokes the INSERT_GEOM procedure to insert that geometry.
DECLARE geom mdsys.sdo_geometry := mdsys.sdo_geometry (2003, null, null, mdsys.sdo_elem_info_array (1,1003,3), mdsys.sdo_ordinate_array (-109,37,-102,40)); BEGIN INSERT_GEOM(geom); COMMIT; END; /
The geometry to be stored can be a polygon with a hole, as shown in Figure 3-1.
The coordinate values for Element 1 and Element 2 (the hole), shown in Figure 3-1, are:
Element 1= [P1(6,15), P2(10,10), P3(20,10), P4(25,15), P5(25,35), P6(19,40), P7(11,40), P8(6,25), P1(6,15)] Element 2= [H1(12,15), H2(15,24)]
The following example assumes that a table named PARKS was created as follows:
CREATE TABLE PARKS (NAME VARCHAR2(32), SHAPE MDSYS.SDO_GEOMETRY);
The SQL statement for inserting the data for geometry OBJ_1 is:
INSERT INTO PARKS VALUES ('OBJ_1', MDSYS.SDO_GEOMETRY(2003, NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,3), MDSYS.SDO_ORDINATE_ARRAY(6,15, 10,10, 20,10, 25,15, 25,35, 19,40, 11,40, 6,25, 6,15, 12,15, 15,24)));
The SDO_GEOMETRY object type takes values and constructors for its attributes SDO_GTYPE, SDO_ELEM_INFO, and SDO_ORDINATES. The SDO_GTYPE is 2003, and the SDO_ELEM_INFO has 2 triplet values because there are 2 elements. Element 1 starts at offset 1, is of ETYPE 1003, and its interpretation value is 1 because the points are connected by straight line segments. Element 2 starts at offset 19, is of ETYPE 2003, and has an interpretation value of 3 (a rectangle). The SDO_ORDINATES varying length array has 22 values with SDO_ORDINATES(1...18) describing element 1 and SDO_ORDINATES(19...22) describing element 2.
Assume that two dimensions are named X and Y, their bounds are 0 to 100, and the tolerance for both dimensions is 0.005. The SQL statement for loading the
USER_SDO_GEOM_METADATA metadata view is:
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('PARKS', 'SHAPE', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.005), MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.005)), NULL);
A compound line string is a connected sequence of straight line segments and circular arcs. Figure 3-2 is an example of a compound line string.
In Figure 3-2, the coordinate values for points P1..P7 that describe the line string OBJ_2 are:
OBJ_2 = [P1(15,10), P2(25,10), P3(30,5), P4(38,5), P5(38,10),
P6(35,15), P7(25,20)]
The SQL statement for inserting this compound line string in a feature table defined as ROADS(GID Varchar2(32), Shape MDSYS.SDO_GEOMETRY) is:
INSERT INTO ROADS VALUES ('OBJ_2', MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2, 1,2,1, 9,2,2), MDSYS.SDO_ORDINATE_ARRAY(15,10, 25,10, 30,5, 38,5, 38,10, 35,15, 25,20)));
The SDO_GEOMETRY object type takes values and constructors for its attributes SDO_GTYPE, SDO_ELEM_INFO, and SDO_ORDINATES. The SDO_GTYPE is 2002, and the SDO_ELEM_INFO_ARRAY has 9 values because there are 2 subelements for the compound line string. The first subelement starts at offset 1, is of SDO_ETYPE 2, and its interpretation value is 1 because the points are connected by straight line segments. Similarly, subelement 2 has a starting offset of 9. That is, the first ordinate value is SDO_ORDINATES(9), is of SDO_ETYPE 2, and has an interpretation value of 2 because the points describe a circular arc. The SDO_ORDINATES_ARRAY varying length array has 14 values, with SDO_ORDINATES(1..10) describing subelement 1, and SDO_ORDINATES(9..14) describing subelement 2.
Assume that two dimensions are named X and Y, their bounds are 0 to 100, and tolerance for both dimensions is 0.005. The SQL statement to insert the metadata into the USER_SDO_GEOM_METADATA view is:
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('ROADS', 'SHAPE', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.005), MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.005)), NULL);
A compound polygon's boundary is a connected sequence of straight line segments and circular arcs, whose first point is equal to its last point. Figure 3-3 is an example of a compound polygon.
In Figure 3-3, the coordinate values for points P1 to P8 that describe the polygon OBJ_3 are:
OBJ_3 = [P1(20,30), P2(11,30), P3(7,22), P4(7,15), P5(11,10), P6(21,10),
P7(27,30), P8(25,27), P1(20,30)]
The following example assumes that a table named PARKS was created as follows:
CREATE TABLE PARKS (GID VARCHAR2(32), SHAPE MSSYS.SDO_GEOMETRY);
The SQL statement for inserting this compound polygon is:
INSERT INTO PARKS VALUES ('OBJ_3', MDSYS.SDO_GEOMETRY(2003, NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 13,2,2), MDSYS.SDO_ORDINATE_ARRAY(20,30, 11,30, 7,22, 7,15, 11,10, 21,10, 27,30, 25,27, 20,30)));
The SDO_GEOMETRY object type takes values and constructors for its attributes SDO_GTYPE, SDO_ELEM_INFO, and SDO_ORDINATES. The SDO_GTYPE is 2003, the SDO_ELEM_INFO has 3 triplet values. The first triplet (1,1005,2) identifies the element as a compound polygon (ETYPE 1005) with two subelements. The first subelement starts at offset 1, is of ETYPE 2, and its interpretation value is 1 because the points are connected by straight line segments. Subelement 2 has a starting offset of 13, is of ETYPE 2, and has an interpretation value of 2 because the points describe a circular arc. The SDO_ORDINATES varying length array has 18 values, with SDO_ORDINATES(1...14) describing subelement 1, and SDO_ORDINATES(13...18) describing subelement 2.
This example assumes the PARKS table was created as follows:
CREATE TABLE PARKS (GID VARCHAR2(32), SHAPE MSSYS.SDO_GEOMETRY);
Assume that two dimensions are named X and Y, their bounds are 0 to 100, and tolerance for both dimensions is 0.005. The SQL statement to insert the metadata into the USER_SDO_GEOM_METADATA view is:
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('PARKS', 'SHAPE', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.005), MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.005)), NULL);
A compound polygon's boundary is a connected sequence of straight line segments and circular arcs. Figure 3-4 is an example of a geometry that contains a compound polygon with a hole (or void).
In Figure 3-4, the coordinate values for points P1 to P8 (Element 1) and C1 to C3 (Element 2) that describe the geometry OBJ_4 are:
Element 1 = [P1(20,30), P2(11,30), P3(7,22), P4(7,15), P5(11,10), P6(21,10), P7(27,30), P8(25,27), P1(20,30)] Element 2 = [C1(10,17), C2(15,22), C3(20,17)]
The following example assumes that a table named PARKS was created as follows:
CREATE TABLE PARKS (GID VARCHAR2(32), SHAPE MSSYS.SDO_GEOMETRY);
The SQL statement for inserting this compound polygon with a hole is:
INSERT INTO Parks VALUES ('OBJ_4', MDSYS.SDO_GEOMETRY(2003, NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2, 1,2,1, 13,2,2, 19,2003,4), MDSYS.SDO_ORDINATE_ARRAY(20,30, 11,30, 7,22, 7,15, 11,10, 21,10, 27,30, 25,27, 20,30, 10,17, 15,22, 20,17)));
The SDO_GEOMETRY object type takes values and constructors for its attributes SDO_GTYPE, SDO_ELEM_INFO, and SDO_ORDINATES. The SDO_GTYPE is 2003, the SDO_ELEM_INFO has 4 triplet values. The first 3 triplet values represent element 1. The first triplet (1,1005,2) identifies this element as a compound element with two subelements. The values in SDO_ELEM_INFO(1...9) pertain to element 1, while SDO_ELEM_INFO(10...12) are for element 2.
The first subelement starts at offset 1, is of ETYPE 2, and its interpretation is 1 because the points are connected by straight line segments. Subelement 2 has a starting offset of 13, is of ETYPE 2, and has an interpretation value of 2 because the points describe a circular arc. The fourth triplet (19,2003,4) represents element 2. Element 2 starts at offset 19, is of ETYPE 2003, and its interpretation value is 4, indicating that it is a circle. The SDO_ORDINATES varying length array has 24 values, with SDO_ORDINATES(1...14) describing subelement 1, SDO_ORDINATES(13...18) describing subelement 2, and SDO_ORDINATES(19...24) describing element 2.
Assume that two dimensions are named X and Y, their bounds are 0 to 100, and tolerance for both dimensions is 0.005. The SQL statement to insert the metadata into the USER_SDO_GEOM_METADATA view is:
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('PARKS', 'SHAPE', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.005), MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.005)), NULL);
A point-only geometry can be inserted with the following statement:
INSERT INTO PARKS VALUES ('OBJ_PT', MDSYS.SDO_GEOMETRY(2001,NULL, MDSYS.SDO_POINT_TYPE(20,30,NULL), NULL, NULL) );
Once data has been loaded into the spatial tables through either bulk or transactional loading, a spatial index must be created on the tables for efficient access to the data. Each spatial index can be an R-tree index or a quadtree index. To decide which type of index to use for a spatial application, you must understand the concepts and guidelines discussed in Section 1.7.
With a quadtree index, the tessellation algorithm used by the CREATE INDEX statement and by index maintenance routines on insert or update operations is determined by the SDO_LEVEL and SDO_NUMTILES values, which are supplied in the PARAMETERS clause of the CREATE INDEX statement. They are interpreted as follows:
An explicit commit operation is executed after the tessellation of all the geometries in a geometry column.
By default, spatial index creation requires a sizable amount of rollback space. To reduce the amount of rollback space required you can supply the SDO_COMMIT_INTERVAL parameter in the CREATE INDEX statement. This will perform a database commit after every n geometries are indexed, where n is a user-defined value.
If the index creation does not complete for any reason, the index is invalid and must be deleted with the DROP INDEX <index_name> [FORCE] statement.
If you choose quadtree indexing for a spatial index, you should use fixed indexing for most applications, except for the rare circumstances where hybrid indexing should be considered. (These rare circumstances are explained in Section 1.7.2.3, and hybrid indexing is discussed in Section 3.2.3. However, you should also consider using R-tree indexing before deciding on hybrid indexing.)
The fixed-size tile algorithm is expressed as a level referring to the number of tessellations performed. To use fixed-size tile indexing, omit the SDO_NUMTILES parameter and set the SDO_LEVEL value to the desired tiling level. The relationship between the tiling level and the resulting size of the tiles depends on the domain of the layer.
The domain used for indexing is defined by the upper and lower boundaries of each dimension stored in the DIMINFO column of the USER_SDO_GEOM_METADATA view, which contains an entry for the table and geometry column to spatially index. A typical domain could be -180 to 180 degrees for longitude,Foot 1 and -90 to 90 degrees for latitude, as represented in Figure 3-5.
If the SDO_LEVEL column is set to 1, then the tiles created by the indexing mechanism are the same size as tiles at the first level of tessellation. Each tile would be 180 degrees by 90 degrees as shown in Figure 3-6.
The formula for the number of fixed-size tiles in a domain is 4n where n is the number of tessellations, stored in the SDO_LEVEL column. In reality, tiles are only generated where geometries exist, and not for the whole domain. Figure 3-7 shows fixed-size tiling at level 2. In this figure, each tile is 90 degrees by 45 degrees.
The size of a tile can be determined by applying the following formula to each dimension:
length = (upper_bound - lower_bound) / 2 ^ sdo_level
The length refers to the length of the tile along the specified dimension. Applying this formula to the tiling shown in Figure 3-7 yields the following sizes:
length for dimension X = (180 - (-180) ) / 2^2 = (360) / 4 = 90 length for dimension Y = (90 - (-90) ) / 2^2 = (180) / 4 = 45
At level 2, the tiles are 90 degrees by 45 degrees in size. As the number of levels increases, the tiles become smaller and smaller. Smaller tiles provide a more precise fit of the tiles over the geometry being indexed. However, because the number of tiles generated is unbounded, you must take into account the performance implications of using higher levels.
|
Note: The Spatial Index Advisor component of Oracle Enterprise Manager can be used to determine an appropriate level for indexing with fixed-size tiles. The SDO_TUNE.ESTIMATE_TILING_LEVEL function, described in Chapter 11, can also be used for this purpose; however, this function performs less analysis than the Spatial Index Advisor. |
Besides the performance aspects related to selecting a fixed-size tile, 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. Data modeling, an important part of any database design, is essential in a spatial database where the data often represents actual physical locations.
In Example 3-5, assume that data has been loaded into a table called ROADS, and the USER_SDO_GEOM_METADATA view has an entry for ROADS.SHAPE. You can use the following SQL statement to create a fixed index named ROADS_FIXED.
CREATE INDEX ROADS_FIXED ON ROADS(SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('SDO_LEVEL=8');
The SDO_LEVEL value is used while tessellating objects. Increasing the level results in smaller tiles and better geometry approximations.
This section describes hybrid indexing, which uses both fixed-size and variable-sized tiles as a spatial indexing mechanism. For each geometry, you will have a set of fixed-size tiles that fully covers the geometry, and a set of variable-sized tiles that fully covers the geometry. The terms hybrid indexing, hybrid tiling, and hybrid tessellation are used interchangeably in this section.
|
Note: With quadtree indexes, you should use fixed indexing for most applications, except for the rare circumstances where hybrid indexing should be considered. These rare circumstances are explained in Section 1.7.2.3. You should also consider using R-tree indexing (see Section 1.7) before deciding on hybrid indexing. |
To use hybrid tiling, the SDO_LEVEL and SDO_NUMTILES keywords in the PARAMETERS clause must contain valid values. Both SDO_LEVEL and SDO_NUMTILES must be greater than 1.
The SDO_NUMTILES value determines the number of variable tiles that will be used to fully cover a geometry being indexed. Typically this value is small. For points, SDO_NUMTILES is always one. For other element types, you might set SDO_NUMTILES to a value around 8. The larger the SDO_NUMTILES value, the better the tiles will approximate the geometry being covered. A larger SDO_NUMTILES value improves the selectivity of the primary filter, but it also increases the number of index entries per geometry (see Section 4.2.1 and Section 4.2.2 for a discussion of primary and secondary filters). The SDO_NUMTILES value should be larger for long, linear spatial entities, such as major highways or rivers, than for area-related spatial entities such as county or state boundaries.
The SDO_LEVEL value determines the size of the fixed tiles used to fully cover the geometry being indexed. Setting the proper SDO_LEVEL value may appear more like art than science. Performing some simple data analysis and testing puts the process back in the realm of science. One approach would be to use the SDO_TUNE.ESTIMATE_TILING_LEVEL function to determine an appropriate starting SDO_LEVEL value, and then compare the performance with slightly higher or lower values. This technique and others are described in Appendix A.
In Example 3-6, assume that data has been loaded into a table called ROADS, and the USER_SDO_GEOM_METADATA view has an entry for ROADS.SHAPE. (Assume also that no spatial index has already been created on the ROADS.SHAPE column.) You can use the following SQL statement to create a hybrid index named ROADS_HYBRID.
CREATE INDEX ROADS_HYBRID ON ROADS(SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('SDO_LEVEL=6 SDO_NUMTILES=12');
This section describes considerations and recommendations for parameters related to R-tree indexes. For basic information about all available parameters, see the CREATE INDEX statement description in Chapter 5.
The default value for SDO_FANOUT is best for most applications. However, a larger value of 60 for SDO_FANOUT is recommended for very large databases (more than 1 million rows).
The default value for SDO_RTR_PCTFREE is best for most applications. However, a value of 0 for SDO_RTR_PCTFREE is recommended if no updates will be performed to the geometry column.
You can create a spatial index on a table that is not in your schema. Assume that user B wants to create a spatial index on column geometry in table T1 under user A's schema. User B must perform the following steps:
GRANT select on T1 to B;
GRANT create table to A; CREATE INDEX qtree on B.T1(geometry) INDEXTYPE IS mdsys.spatial_index PARAMETERS('sdo_level=10 sdo_numtiles=4');
Foot 1
The transference of the domain onto a sphere or other projection is left up to an application, unless a coordinate system is specified, as explained in Appendix D.)
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|