Release 8.1.5
A67295-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Spatial User's Guide and Reference Release 8.1.5 A67295-01 |
|
This appendix provides supplemental information to aid in setup, maintenance, and tuning of a spatial database. The scripts and tuning suggestions provided are intended as guidelines that can be adapted to the specific needs of your database.
This section describes how to select the best model to fit your needs. Basically, the object-relational model is preferable in cases where replication and distributed databases are not required.
The following are some of the benefits to using the new object-relational model, as described in Part I of this guide:
The following are some of the benefits to using the relational model, as described in Part II of this guide:
When Oracle introduces replication and distributed support for objects in a future release, there will be no benefits to using the relational model.
The following information can be used as a guideline for tuning a spatial database. Unless otherwise specified, the following sections refer to both the object-relational and relational models.
Data modeling is very important when designing a spatial database. You should group geometries into layers based on the similarity of their attributes. Assume your data model uses line strings to represent both roads and rivers. The attributes of a road and the attributes of a river are different. Therefore, these geometries should be modeled in two different layers.
In practice, however, if the user of your application will always ask to see both the roads and rivers in a particular region (area of interest), then it may be appropriate to model roads and rivers in the same layer with a common set of attributes.
It is equally important to understand how the data in the various layers will be queried. If the user of your application is interested in querying the data based on a relationship between the layers, then you should index the layers with the same fixed-size tiling level. For example, a query such as, "Which roads cross rivers?" can achieve better performance if the roads and rivers layers are tiled at the same level.
The following example explains how tiling is used in Spatial.
Assume you want all the roads (line strings) that overlap a county boundary (polygon) in a spatial database containing 10 million roads. Ignoring Spatial features for a moment, in purely mathematical terms, the problem translates into comparing all the line segments that make up each road, to the line segments and area of the county boundary to see if there is any intersection. This geometry-to-geometry comparison is very expensive.
Spatial simplifies this calculation by approximating each geometry with tiles. The primary filter in Spatial translates the problem to show all the roads that have a tile equal to a tile that approximates the polygon. The result of this is a superset of the final answer.
The secondary filter (a true geometry-to-geometry comparison) can now be applied to the candidates that returned from the Spatial primary filter, instead of to every road in the database.
Picking the correct tile size for fixed-size tiling is one of the most important factors in attaining good performance. If the tile size you select is too small, you could end up generating thousands of tiles per geometry. Also, the process of tiling a query window may become very time consuming.
At the same time, you do not want to choose tiles that are too big. This would defeat the purpose of the Spatial primary filter. If the tiles are too big, then too many geometries are returned from the primary filter and are sent to the more costly secondary filter.
Keep in mind that the tile size you choose should also depend on if the query window (area of interest) is already defined in the database. If the query window is defined in the database, (that is, if the spatial tables and spatial indexes already exist), then you should choose a smaller tile size. Assume the State layer and the Highway layer are already defined in the database. You could perform a spatial join query such as, "which interstate highways go through the state?" without incurring the overhead of tiling because the query window is already defined in the database. If, on the other hand, you are creating the query window dynamically, you have to factor in the time it takes to define and index the query window. In this case, you should choose a larger tile size to reduce the time it takes to define and index the query window.
Oracle recommends running the SDO_TUNE.ESTIMATE_TILING_LEVEL() function on your data set to get an initial tiling level estimate. This may not be your final answer, but it will be a good level to start your analysis. In general, it is also recommended that you take a random sample of your data and check the query performance at different levels of tiling. This would give an indication of what is the best tiling level for the total data set.
Properly choosing rollback segments and tablespaces is important for getting good performance from Spatial. Therefore, it is very important to read the Oracle8i Administrator's Guide and understand the concepts of tablespaces and rollbacks.
Here are some general guidelines to consider:
The following guidelines refer to only the relational model:
SDO_ADMIN.POPULATE_INDEX_FIXED(), SDO_ADMIN.UPDATE_INDEX_FIXED(), SDO_ADMIN.POPULATE_INDEX(), or SDO_ADMIN.UPDATE_INDEX() procedure.
To select an appropriate tiling level, it may help to visualize the tiles covering your geometries. Through visualization, you can determine how many tiles are used for each object, the size of the tiles, and how well the edges of your geometry are covered. The basic algorithm is:
Two Spatial internal functions have been made visible in order to describe the tiles. These functions were part of a previous release of Oracle Spatial Data Option, and are currently reserved for internal use only. The functions are not recommended for general use, except for this visualization example. Use the following syntax for the internal functions:
hhcellbndry (sdo_code || sdo_meta, sdo_dimnum, sdo_lb, sdo_ub,
hhlength(sdo_code || sdo_meta) {'MIN' | 'MAX'})
In the following examples, the dimension boundaries were assumed to be -180 to 180, and -90 and 90. Also, an index named TEST_INDEX_HL2N6 and a table named TEST are used in the examples.
The SQL queries shown in Example A-1 and Example A-2 can be used to decode all the index entries in a <layername>_SDOINDEX table. The examples return the coordinates of the lower-left and upper-right corners of each tile.
SELECT HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 1,-180.0, 180.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MIN') min_x, HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 1,-180.0, 180.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MAX') max_x, HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 2, -90.0, 90.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MIN') min_y, HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 2, -90.0, 90.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MAX') max_y FROM (SELECT distinct sdo_groupcode, sdo_fixed_meta FROM TEST_INDEX_HL2N6$ a, SDO_INDEX_METADATA b WHERE b.sdo_table_name = 'TEST');
SELECT HHCELLBNDRY(sdo_code || sdo_meta, 1,-180.0, 180.0, HHLENGTH(sdo_code || sdo_meta), 'MIN') min_x, HHCELLBNDRY(sdo_code || sdo_meta, 1,-180.0, 180.0, HHLENGTH(sdo_code || sdo_meta), 'MAX') max_x, HHCELLBNDRY(sdo_code || sdo_meta, 2, -90.0, 90.0, HHLENGTH(sdo_code || sdo_meta), 'MIN') min_y, HHCELLBNDRY(sdo_code || sdo_meta, 2, -90.0, 90.0, HHLENGTH(sdo_code || sdo_meta), 'MAX') max_y FROM (SELECT distinct sdo_code, sdo_meta FROM TEST_INDEX_HL2N6$ a);
The SQL queries shown in Example A-3 and Example A-4 can be used to decode the index entries for a specific geometry stored in a <layername>_SDOINDEX table.
SELECT HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 1,-180.0, 180.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MIN') min_x, HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 1,-180.0, 180.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MAX') max_x, HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 2, -90.0, 90.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MIN') min_y, HHCELLBNDRY(sdo_groupcode || sdo_fixed_meta, 2, -90.0, 90.0, HHLENGTH(sdo_groupcode || sdo_fixed_meta), 'MAX') max_y FROM (SELECT distinct sdo_groupcode, sdo_fixed_meta FROM TEST_INDEX_HL2N6$ a, SDO_INDEX_METADATA b WHERE b.sdo_table_name = 'TEST' AND a.sdo_rowid = 'AAAA59AAFAADzAZAAA');
SELECT HHCELLBNDRY(sdo_code || sdo_meta, 1,-180.0, 180.0, HHLENGTH(sdo_code || sdo_meta), 'MIN') min_x, HHCELLBNDRY(sdo_code || sdo_meta, 1,-180.0, 180.0, HHLENGTH(sdo_code || sdo_meta), 'MAX') max_x, HHCELLBNDRY(sdo_code || sdo_meta, 2, -90.0, 90.0, HHLENGTH(sdo_code || sdo_meta), 'MIN') min_y, HHCELLBNDRY(sdo_code || sdo_meta, 2, -90.0, 90.0, HHLENGTH(sdo_code || sdo_meta), 'MAX') max_y FROM TEST_INDEX_HL2N6$ WHERE sdo_rowid = 'AAAA59AAFAADzAZAAA';
The spatial index is represented internally as a linear quadtree. The structure used to represent the linear quadtree is composed of two components: a data component and a metadata component. The data component of the linear quadtree is stored in the SDO_CODE column, and the metadata component is stored in the SDO_META column.
The SDO_META column is not required for spatial queries. However, by combining the SDO_META column with the SDO_CODE column, the tiles of any geometry or of the entire data set can be decoded. This capability allows the tiles to be visualized.
Two Spatial internal functions have been made visible in order to describe the tiles. These functions were part of a previous release of Oracle Spatial Data Option, and are currently reserved for internal use only. The functions are not recommended for general use, except for this visualization example. Use the following syntax for the internal functions:
hhcellbndry (sdo_code || sdo_meta, sdo_dimnum, sdo_lb, sdo_ub,
hhlength(sdo_code || sdo_meta) {'MIN' | 'MAX'})
In the following examples, the dimension boundaries were assumed to be -180 to 180, and -90 and 90. The dimensional information is stored in the <layername>_SDODIM table.
If you used SDO_ADMIN.UPDATE_INDEX_FIXED() or SDO_ADMIN.POPULATE_INDEX_FIXED() to generate your spatial index, replace "sdo_code || sdo_meta" with sdo_tile in the SQL statements that follow.
The SQL query shown in Example A-5 can be used to decode all the index entries in a <layername>_SDOINDEX table. The example returns the coordinates of the lower-left and upper-right corners of each tile.
SELECT hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_x, hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_x, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_y, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_y FROM (SELECT DISTINCT sdo_code, sdo_meta FROM <layername>_sdoindex);
The SQL query shown Example A-6 in can be used to decode the index entries for a specific geometry stored in a <layername>_SDOINDEX table:
SELECT hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_x, hhcellbndry (sdo_code || sdo_meta, 1, -180.000000000, 180.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_x, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MIN') min_y, hhcellbndry (sdo_code || sdo_meta, 2, -90.000000000, 90.000000000,
hhlength (sdo_code || sdo_meta), 'MAX') max_y FROM <layername>_sdoindex WHERE sdo_gid = <geometry id>;
See Section A.4.2.3 for another method of viewing tiles.
When the Oracle database server processes SQL statements that require sorting, such as statements containing an ORDER BY or DISTINCT clause, the Oracle server stores the result set in a temporary storage area. The result set is then sorted. If the SORT_AREA_SIZE is insufficient for holding the result set in memory, then some data may be written to disk and an entry is written in the redo log.
The RELATE( ) secondary filter issues SQL statements internally that contain DISTINCT and ORDER BY clauses. If the SORT_AREA_SIZE initialization parameter is too small for processing the secondary filters, then some sorting may occur on disk, which causes entries to be written in the redo log. This may affect performance. For better performance, increase the SORT_AREA_SIZE parameter to force sorting to occur in memory.
Point data, unlike line and polygon data, has the unique characteristic of containing one tile per point. This section describes how to improve the performance of queries on point data.
When querying point data with a rectangular query window, you can take advantage of the nature of these geometries to improve performance.
A rectangle can be defined by its lower-left and upper-right coordinates (Xmin, Ymin and Xmax, Ymax). A point has a single set of coordinates (Px, Py). When your area-of-interest is a rectangle, instead of using the SDO_GEOM.RELATE()function in the secondary filter, you can use simple SQL comparison operators as follows:
SELECT sdo_gid, sdo_x1, sdo_y1 FROM cities_sdogeom, (SELECT a.sdo_gid gid1 FROM cities_sdoindex a, window_sdoindex b WHERE b.sdo_gid = [area of interest id] AND a.sdo_code = b.sdo_code) WHERE sdo_gid = gid1 AND sdo_x1 BETWEEN Xmin AND Xmax AND sdo_y1 BETWEEN Ymin AND Ymax;
The DISTINCT clause is not necessary in the primary filter of the query because a point contains only a single tile in the spatial index.
Because a point is always referenced by only one tile in a spatial index, for additional performance, you can place the columns normally found in the <layername>_SDOINDEX table in the <layername>_SDOGEOM table. This will save you the cost of joining the <layername>_SDOINDEX and <layername>_SDOGEOM tables.
You still need to create an updatable view for the <layername>_SDOINDEX table that selects the appropriate columns from the <layername>_SDOGEOM table. This is because functions such as SDO_ADMIN.UPDATE_INDEX_FIXED() and SDO_ADMIN.POPULATE_INDEX_FIXED() expect a <layername>_SDOINDEX table to exist. Create the view using "instead of" triggers for insert, delete, and update such that the appropriate columns in the <layername>_SDO_GEOM table are updated. The following example shows how to use "instead of" triggers:
CREATE OR REPLACE TRIGGER mytrig INSTEAD OF INSERT ON points_sdoindex REFERENCING new AS n FOR EACH ROW BEGIN UPDATE points_sdogeom SET points_sdogeom.sdo_code = :n.sdo_gid; END; CREATE OR REPLACE TRIGGER mydeltrig INSTEAD OF DELETE ON points_sdoindex REFERENCING old AS n FOR EACH ROW BEGIN UPDATE points_sdogeom SET points_sdogeom.sdo_code = NULL WHERE points_sdogeom.sdo_gid = :n.sdo_gid; END;
The following example shows a window query of a layer containing point data when the window layer contains one rectangle:
SELECT sdo_gid, sdo_x1, sdo_y1 FROM points_sdogeom a, window_sdoindex b WHERE b.sdo_gid = [area of interest id] AND a.sdo_code = b.sdo_code) AND sdo_x1 BETWEEN Xmin AND Xmax AND sdo_y1 BETWEEN Ymin AND Ymax;
Because point data is always indexed using a single tile, it is well suited for partitioning. The following script shows an example of using the Oracle8i partitioning feature with Spatial point data:
ORACLE_HOME/MD/demo/examples/scripts/partition_points.sql
There are some helpful hints you can place in your spatial join queries to improve performance. The remainder of this section describes some of the hints you can use. For more information on hints, see Oracle8i Tuning.
A spatial join takes place between two layers. When the two layers being joined are line or polygon layers, the spatial join query contains two DISTINCT clauses: one in the inner SELECT clause and the other in the outer SELECT clause. The Oracle optimizer ignores the inner DISTINCT clause to save on the cost of sorting. However, if the inner DISTINCT clause is ignored, the secondary filter gets called many more times than it needs to be. This can have a significant impact on performance because the secondary filter is an expensive operation. Use the NO_MERGE hint to prevent the optimizer from ignoring the inner DISTINCT clause.
In a spatial join, all the tiles from one layer are compared to all the tiles from another layer. The Oracle database server performs a full table scan on one <layername>_SDOINDEX table, (preferably the smaller of the two), and an index lookup on the other <layername>_SDOINDEX table. Use the INDEX and USE_NL hints to force the optimizer to perform the full table scan on the smaller of the two <layername>_SDOINDEX tables being compared.
The following example shows a spatial join between line (road) and polygon (county) data. The query answers the question, "Which counties intersect major roads?"
SELECT /*+ cost ordered use_nl(COUNTY_sdogeom) index (COUNTY_sdogeom NAME_OF_SDO_GID_INDEX) */ COUNTY_sdogeom.SDO_GID, COUNTY_sdogeom.SDO_ESEQ, COUNTY_sdogeom.SDO_SEQ, COUNTY_sdogeom.SDO_X1,COUNTY_sdogeom.SDO_Y1, COUNTY_sdogeom.SDO_X2,COUNTY_sdogeom.SDO_Y2, COUNTY_sdogeom.SDO_X3,COUNTY_sdogeom.SDO_Y3, COUNTY_sdogeom.SDO_X4,COUNTY_sdogeom.SDO_Y4, COUNTY_sdogeom.SDO_X5,COUNTY_sdogeom.SDO_Y5, COUNTY_sdogeom.SDO_X6,COUNTY_sdogeom.SDO_Y6, COUNTY_sdogeom.SDO_X7,COUNTY_sdogeom.SDO_Y7, COUNTY_sdogeom.SDO_X8,COUNTY_sdogeom.SDO_Y8 FROM (SELECT DISTINCT gid_a gid1 FROM (SELECT /*+ index (a NAME_OF_SDO_CODE_INDEX) index (b NAME_OF_SDO_CODE_INDEX) use_nl (a b) no_merge */ DISTINCT a.sdo_gid gid_a, b.sdo_gid gid_b FROM COUNTY_SDOINDEX a, MAJOR_ROAD_SDOINDEX b WHERE a.sdo_code = b.sdo_code) WHERE sdo_geom.relate('COUNTY', gid_a, 'ANYINTERACT', 'MAJOR_ROAD',gid_b) <> 'FALSE'), COUNTY_sdogeom WHERE COUNTY_sdogeom.sdo_gid = gid1;
The following example shows a spatial join between line (road) and point (street address) data. The query answers the question, "which addresses are on a major road?"
SELECT /*+ cost ordered use_nl (STREET_ADDRESS_sdogeom) index (STREET_ADDRESS_sdogeom NAME_OF_SDO_GID_INDEX) */ STREET_ADDRESS_sdogeom.SDO_GID, STREET_ADDRESS_sdogeom.SDO_X1, STREET_ADDRESS_sdogeom.SDO_Y1 FROM (SELECT DISTINCT gid_a gid1 FROM (SELECT /*+ index (a NAME_OF_SDO_CODE_INDEX) index (b NAME_OF_SDO_CODE_INDEX) use_nl (a b) */ a.sdo_gid gid_a, b.sdo_gid gid_b FROM STREET_ADDRESS_SDOINDEX a, MAJOR_ROAD_SDOINDEX b WHERE a.sdo_code = b.sdo_code) WHERE sdo_geom.relate('STREET_ADDRESS', gid_a, 'ANYINTERACT', 'MAJOR_ROAD',gid_b) <> 'FALSE'), COUNTY_sdogeom WHERE COUNTY_sdogeom.sdo_gid = gid1;
The inner DISTINCT clause is not necessary for spatial joins where one of the layers contains point data. Therefore, the NO_MERGE hint is not necessary. This is because points contain only one tile in the spatial index.
The following example shows a spatial join between polygon (county) and point (street address) data. The query generates a report that displays how many addresses are associated with each county.
If you can assume that each street address is associated with a single county, you can significantly speed up this query. Because points contain only a single tile in the spatial index, any street address tile that matches only one county tile in the primary filter does not need to go through the expensive secondary filter.
SELECT county_gid, count(street_gid) FROM (SELECT poly.sdo_gid county_gid, street.sdo_gid street_gid FROM STREET_ADDRESS_sdoindex street, (SELECT sdo_code county_sdo_code, count(sdo_gid) interacts FROM CENSUS_COUNTY_sdoindex GROUP by sdo_code ) counts, CENSUS_COUNTY_sdoindex poly WHERE street.sdo_code = counts.county_sdo_code AND poly.sdo_code = street.sdo_code AND (counts.interacts = 1 OR sdo_geom.relate('STREET_ADDRESS', street.sdo_gid, 'ANYINTERACT', 'CENSUS_COUNTY',poly.sdo_gid) <> 'FALSE' ) ) GROUP BY county_gid;
The relational spatial model supports three geometry types: points, lines, and polygons. If your data contains another type, such as a circle or arc, then you must choose the supported type that best approximates your desired type (or upgrade to the object-relational model.) For example, in the relational model, a circle can be defined as a multisided polygon. Obviously, the more coordinates in the element, the better the approximation will be.
Although customized types are not supported, you do not have to lose your knowledge of the type. After storing the approximated element, create another element in that geometry with ETYPE=0. Spatial ignores elements of ETYPE=0. You can then write your own routines to handle your specialized geometry type.
The Oracle8i partitioning feature lets you spread out your spatial data and create spatial indexes in a very controlled manner. Such control allows a database administrator to isolate data that may be causing I/O performance issues. Note that this optimization works only for the relational implementation.
The most obvious way to partition relational spatial data is to base the partitions on the geometry ID (GID) column. Select the full list of available GIDs in a given layer and sort them to produce an ordered list. Next, examine the list to determine whether or not the GIDs would provide a good set of balanced partitions. In cases where one or two GIDs dominate the layer, partitioning by GID will not yield a balanced distribution. In such cases, you may want to consider adding a new alphanumeric column to the layer, and use this column to create balanced partitions. Although this requires an extra effort, it may result in significant performance improvements.
For more information, including examples and sample parsing times, see the online text file: ORACLE_HOME/md/demo/examples/scripts/parallel.doc.
On a multiprocessor system, you can use parallel execution to improve both loading times and spatial index creation times. Note that this optimization works only for the relational implementation.
When using partitioned tables, as described in Section A.2.9, you can achieve further performance gains by loading and indexing geometries in parallel. The partitioned tables can be loaded by selecting from non-partitioned source tables, or using the SQL*Loader utility. Parallel threads (one for each partition,) can be submitted to load the partitioned table. For information on parallel loading, see the description of the SQL*Loader utility in Oracle8i Utilities.
You can also create spatial indexes in parallel by creating a number of views or layers. Create each layer with a range of GIDs, with corresponding <layername>_SDOLAYER and <layername>_SDODIM tables. For example, the following statements create the necessary views for the first 300 GIDs in a table:
CREATE VIEW a_sdogeom AS SELECT * FROM a_sdogeom
WHERE sdo_gid BETWEEN 1 and 100; CREATE VIEW a_sdodim AS SELECT * FROM a_sdodim; CREATE VIEW a_sdolayer AS SELECT * FROM a_sdolayer;
Next, create the index table as a partitioned table. Create a partition for each range of GIDs for which you created a view.
CREATE INDEX a_sdoindex (sdo_gid NUMBER, sdo_code RAW(255), sdo_meta RAW(255)) INITRANS 4 STORAGE (initial 2M next 1M pctincrease 0 freelist groups 12 freelists 19) PARTITION BY RANGE (sdo_gid) (PARTITION a_idx1 VALUES LESS THAN (300) TABLESPACE sdo_data . . . );
To create the index, submit SDO_ADMIN.POPULATE_INDEX() commands for each of the partitions. The threads will independently build their corresponding indexes, with significant performance improvements over the non-partitioned, single-threaded model.
For more information, including examples and sample parsing times, see the online text file: ORACLE_HOME/md/demo/examples/scripts/parallel.doc.
Spatial provides sample SQL script files to show how to use dynamic SQL in a PL/SQL block to create layer tables for spatially indexed data. The scripts are available after installation in the ORACLE_HOME/md/admin directory.
The following sections describe the cr_spatial_index.sql and crlayer.sql scripts.
The cr_spatial_index.sql script file shows an example of updating the spatial index for a layer, and executing a commit after every 50 GIDs have been entered.
The procedures 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 procedures, you can write a routine similar to that in cr_spatial_index.sql. This script loops and calls SDO_ADMIN.UPDATE_INDEX_FIXED() for each GID, committing after every 50 GIDs.
-- cr_spatial_index.sql -- -- Note: if geometries do not span more than 1 row, you can remove -- the DISTINCT qualifier from the SELECT statement. -- declare cursor c1 is SELECT DISTINCT sdo_gid from POLYGON_SDOGEOM; gid number; i number; begin i := 0; for r in c1 loop begin gid:= r.sdo_gid; sdo_admin.update_index_fixed('POLYGON', gid, 15, FALSE, FALSE, FALSE); exception when others then dbms_output.put_line('error for gid'||to_char(gid)||': '||SQLERRM ); end; i:= i + 1; if i = 50 then commit; i:= 0; end if; end loop; commit; end; /
When you call the SDO_ADMIN.UPDATE_INDEX_FIXED() procedure for a large data set, you may get a "snapshot too old" error message from the Oracle server. You can avoid this error by creating more or larger rollback segments. You can also try to increase the number of GIDs before committing the transaction.
|
Note: The cr_spatial_index.sql script is not available in your ORACLE_HOME/md/admin directory after installation. You must create this script yourself. |
The crlayer.sql script file is a template used to create all the tables for a layer and populate the metadata in the <layername>_SDODIM and <layername>_SDOLAYER tables.
The following sections describe sample programs and related products that, while not required for the storage or maintenance of spatial data, can make those tasks simpler.
Oracle8i interMedia Locator is a related product that supports online internet-based geocoding facilities for locator applications and proximity queries.
Geocoding is the process for converting a non-standardized street address or postal code into a standardized address (optionally certified by the USPS), with latitude and longitude information. In addition, census information such as block groups, postal carrier routes, and block codes can be retrieved as a result of this process.
The interMedia Locator option provides an interface to the online geocoding service provided by Qualitative Marketing Service, Inc. (QMS). You can use PL/SQL stored procedure functions to geocode an address, and record and fetch all the information into two predefined objects from the QMS Web site. The first object is of type SDO_GEOMETRY, and it contains the spatial longitude and latitude information stored as point data. The second object returned is GEOCODE_RESULT which contains text fields of a standardized address and other fields mentioned previously such as postal carrier route or block code.
For more information about this online service, see the following Web site:
http://www.centrus-software.com/oracle
For more information about interMedia Locator, see Oracle8i interMedia Locator User's Guide and Reference.
interMedia Locator is a subset of Oracle8i Spatial and, therefore, is completely compatible with Spatial objects. The index uses the same set of metadata tables, for instance. One difference is that interMedia Locator locates only points, while Spatial supports multiple geometry types.
The LOCATOR_WITHIN_DISTANCE() operator is similar to the SDO_GEOM.WITHIN_DISTANCE( ) operator.
The interMedia Locator version of the WITHIN_DISTANCE operator takes a new parameter in the last string: units=[mile,meter,ft]. This allows you to search by units. The functionality in the Spatial version is only an estimation on the surface of the earth, and not exact distance or driving distance.
Sample scripts are available in the following directory after you install Oracle8i interMedia Locator:
$ORACLE_HOME/md/demo/geocoder
To migrate data between products, type ocimig, and prompts will guide you through the process, which is similar to using SQL*Loader or the export/import utilities.
A sample geometry viewer, sdodemo, is available for UNIX systems using a Motif interface. This viewer displays geometries stored using the relational model.
The following steps are required to set up and run the Motif application:
setenv MD_VIEWER <full_pathname>/sdo_motif_demo/src
setenv XENVIRONMENT $MD_VIEWER/app-defaults/resource_file
alias sdodemo $MD_VIEWER/bin/demo
$ORACLE_HOME/md/admin/sdowin.sql
$ORACLE_HOME/md/admin/prvtwin.plb
$MD_VIEWER/sql_scripts/my_window.sql
$MD_VIEWER/sql_scripts/my_win.sql
If you are using a UNIX operating system other than Solaris, you need to recompile the viewer. A makefile is included only for Sun Solaris systems. You may need to make some system-specific modifications.
cd $MD_VIEWER
make -f makefile8.sun clean
make -f makefile8.sun
When you run the sample sdodemo program, you will be prompted for an Oracle user name, password, and alias if the database resides on a remote machine.
Two windows will pop up, one where geometries are drawn, and a second with several buttons. Click the CHOOSE LAYER button and select a layer.
The extent of the map will initially be the values stored in the <layername>_SDODIM table for the current layer. You can then click the ZOOM TO EXTENT button, and the map extent will be set to the true extent of your data. Note that the time it takes to perform ZOOM TO EXTENT depends on the amount of data in your <layername>_SDOGEOM table.
The text for all queries is displayed in the UNIX shell where you are running the sdodemo program.
There are three radio buttons at the top of the control panel. These buttons determine which query is executed when you click the PERFORM QUERY button:
To perform a spatial query:
You can look at individual geometries by clicking the SHOW GIDS button. You can also click the SHOW ALL TILES button to look at index tiles. This can help you tune your spatial index. See Section A.2.4 for another method of drawing tiles.
The Spatial Visualizer is a sample program used to demonstrate two things. First, it is an example of using dynamic linking libraries to wrap Oracle Call Interface (OCI) and Spatial functions into C++ classes. Second, the program provides a simple visualizer that can display Spatial objects.
To compile the Spatial Visualizer sample program, first unzip the following file into your work directory: ORACLE_HOME/md/demos/NT/DEMO_Visualizer.zip. This creates the following subdirectories:
Next, make sure your Visual C++ IDE has the correct directory settings for using OCI and common header files. To ensure this, click Tools... Options... Directories, and then perform the following tasks:
Consider the following when using this sample program:
