Oracle7 Spatial Data Option User's Guide and Reference

Library

Product

Contents

Index

Loading Spatial Data

2.1 Load Model
2.2 Loading Process
2.3 Index Creation

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.

2.1 Load Model

There are two steps involved in loading raw data into a spatial database such that it can be queried efficiently:

Loading the data into spatial tables.
Creating or updating the index on the spatial tables.

Table 2-1 shows the format of the tables needed to store and index spatial data.

Figure 2¯1 Geometry Layer Tables

2.2 Loading Process

The process of loading data can be classified into two categories:

Bulk loading of data: This process is used to load large volumes of data into the database and uses the SQL*Loader to load the data.
Transactional inserts: This process is used to insert relatively small amounts of data into the database and is analogous to the insert command in SQL.

2.2.1 Bulk Loading

Bulk loading can be used to import large amounts of legacy or raw data into a spatial database. Bulk loading is accomplished using the SQL*Loader ¹.

Example 2¯1

The following example 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 raw data as long you can write an SQL*Loader control file to load that data into the tables.

Assume that the raw 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.

The following shows the control file for loading the data into the geometry table:

Example 2¯2

LOAD DATA INFILE *

REPLACE

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 tables ROADS_SDOGEOM need to exist in the schema before attempting the load.

Example 2¯3

In this example, 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 *

REPLACE

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

2.2.2 Transactional Insert Using SQL

Spatial Data Option uses standard Oracle7 tables which can be accessed or loaded with standard SQL syntax. The following example 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 the previous example, 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 follows:

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

2.2.3 Transactional Insert Using Spatial Geometry Functions

Spatial Data Option 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:

Initialize the element that needs to be stored. Note that this process does not fill in any coordinate information for the element. Two parameters are passed to the SDO_GEOM.INIT_ELEMENT() function which initializes the element:
- the name of the layer (for example, ROADS)
- a GID which is a unique identifier for the geometry
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.
Fill in the coordinate information for the element using the SDO_GEOM.ADD_NODES() procedure. This procedure takes the following parameters:

Example 2¯4

In this example, a simple polygon, geometry number 1234, consisting of 5 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.

Example 2¯5

In this example, assume that the geometry shown in the following figure 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.

Figure 2¯2 Polygon With a Hole

The following code shows how to load the spatial data for the polygon shown in

Figure 2-2

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

2.3 Index Creation

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(254))

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, a factor for creating fixed-size tiles that the geometry should be tessellated into, 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 the designated GID. The arguments to this procedure are the name of the layer, the GID of the designated geometry, the size of tiles the geometry should be tessellated into, and a replace flag. The replace flag signals whether or not to delete any existing tiles for the geometry prior to tessellating.

2.3.1 Indexing With Fixed-Size Tiles

Spatial Data Option 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-size 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 one-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 would be -90 to 90 degrees for latitude, and -180 to 180 degrees for longitude.

Figure 2¯3 Fixed-Size Tiling

To generate fixed-size tiles, use the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure and pass it the number of times the layer 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:

Figure 2¯4 Fixed-Size Tiling at Level 1

The formula for the number of fixed-size tiles is 4n where n is the tile_size parameter passed to the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure. Figure 2-5 shows fixed-size tiling at level 2. In this example, each tile is 90 degrees by 45 degrees.

Figure 2¯5 Fixed-Size Tiling at Level 2

Example 2¯6

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 an object must be tessellated into. 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.CODE column.

If a geometry with a 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);

If a large number of geometries have been added to the database, the SDO_ADMIN.POPULATE_INDEX_FIXED() procedure should be used instead of the SDO_ADMIN.UPDATE_INDEX_FIXED() procedure. This is often a simpler alternative because it does not require you to specify each SDO_GID for which the index should be updated. It reads the tables and automatically creates index entries as needed.

Prev Next

Library

Product

Contents

Index