10.1 SDO_GEOMETRY Objects in User-Defined Type Definitions
The SDO_GEOMETRY type can be embedded in a user-defined data type definition.
The procedure is very similar to that for using the SDO_GEOMETRY type for a spatial data column:
-
Create the user-defined data type.
-
Create a table with a column based on that data type.
-
Insert data into the table.
-
Update the USER_SDO_GEOM_METADATA view.
-
Create the spatial index on the geometry attribute.
-
Perform queries on the data.
For example, assume that you want to follow the cola markets scenario in the simplified example in Simple Example: Inserting_ Indexing_ and Querying Spatial Data, but want to incorporate the market name attribute and the geometry attribute in a single type. First, create the user-defined data type, as in the following example that creates an object type named MARKET_TYPE:
CREATE OR REPLACE TYPE market_type AS OBJECT (name VARCHAR2(32), shape SDO_GEOMETRY); /
Create a table that includes a column based on the user-defined type. The following example creates a table named COLA_MARKETS_2 that will contain the same information as the COLA_MARKETS table used in the example in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.
CREATE TABLE cola_markets_2 ( mkt_id NUMBER PRIMARY KEY, market MARKET_TYPE);
Insert data into the table, using the object type name as a constructor. For example:
INSERT INTO cola_markets_2 VALUES( 1, MARKET_TYPE('cola_a', SDO_GEOMETRY( 2003, -- two-dimensional polygon NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior) SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to -- define rectangle (lower left and upper right) ) ) );
Update the USER_SDO_GEOM_METADATA view, using dot-notation to specify the column name and spatial attribute. The following example specifies MARKET.SHAPE as the COLUMN_NAME (explained in COLUMN_NAME) in the metadata view.
INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'cola_markets_2', 'market.shape', SDO_DIM_ARRAY( -- 20X20 grid SDO_DIM_ELEMENT('X', 0, 20, 0.005), SDO_DIM_ELEMENT('Y', 0, 20, 0.005) ), NULL -- SRID );
Create the spatial index, specifying the column name and spatial attribute using dot-notation. For example.
CREATE INDEX cola_spatial_idx_2 ON cola_markets_2(market.shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;
Perform queries on the data, using dot-notation to refer to attributes of the user-defined type. The following simple query returns information associated with the cola market named cola_a
.
SELECT c.mkt_id, c.market.name, c.market.shape FROM cola_markets_2 c WHERE c.market.name = 'cola_a';
The following query returns information associated with all geometries that have any spatial interaction with a specified query window, namely, the rectangle with lower-left coordinates (4,6) and upper-right coordinates (8,8).
SELECT c.mkt_id, c.market.name, c.market.shape FROM cola_markets_2 c WHERE SDO_RELATE(c.market.shape, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,6, 8,8)), 'mask=anyinteract' = 'TRUE';
Parent topic: Extending Spatial Indexing Capabilities