Rem Rem $Header: sdo/admin/sdogrt.sql /main/9 2009/06/30 14:07:04 hzhu Exp $ Rem Rem sdogeortypes.sql Rem Rem Copyright (c) 2001, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem grotypes.sql - GeoRaster Object TYPES Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem hzhu 06/30/09 - Bug 8645577 (a comment issue) Rem hzhu 05/12/09 - Add constructors for SDO_GEOR_GCP & Rem SDO_GEOR_GCPGEOREFTYPE Rem hzhu 03/13/08 - Add Geoferencing related types Rem wexu 04/13/07 - add constructor for sdo_geor_srs Rem sravada 07/29/04 - add exception handlers Rem qxie 10/08/02 - add new types and misc changes Rem qxie 04/17/02 - move number and string array types Rem qxie 03/25/02 - updated Rem qxie 12/12/01 - GeoRaster Component Object Type Definitions Rem qxie 12/12/01 - Created Rem -- ----------------------------------------------------------------------------- -- Type SDO_GEORASTER -- -- Description: -- -- This is a generic Oracle GEORASTER type that is used to represent all -- possible georaster data types: 2-D Images, 2-D GRIDs, 3-D GRIDS -- Multi-Band and/or Multi-Time Images, etc. -- -- Members: -- rasterType - A GeoRaster Type identifier. It is an SDO specific -- numeric value that has a one-to-one mapping to -- values identifying supported Oracle GeoRaster types -- spatialExtent - Stores the Spatial Extent of the GeoRaster object -- It is also called footprint. The type of it is -- SDO_GEOMETRY so it could be rectangular or any -- shape. But must be a polygon type. -- rasterDataTable - the name of the raster data table (RDT) of this -- GeoRaster object. The table stores all cell values (pixels) -- except for blank images. -- rasterID - the GeoRaster object ID used in RDT to identify the cell -- data of the object. -- metadata - Stores the Oracle GeoRaster object metadata -- (a subset of its whole metadata, see data model) -- Comments: -- -- A GeoRaster object consists of the above attributes, RDT and may include -- the following data or tables: VAT tables, GCP table, Display LUT tables. -- They are stored in or their table names are stored in the "metadata" object. -- -- For one GeoRaster table, all "spatialExtent" objects should have the -- same SRID if a spatial index is to be built on them. -- -- ----------------------------------------------------------------------------- CREATE OR REPLACE TYPE SDO_GEORASTER TIMESTAMP '2001-12-06:13:00:00' OID '9476CC705B2A1B06E0340800209D3EE1' AS OBJECT ( rasterType NUMBER, spatialExtent MDSYS.SDO_GEOMETRY, rasterDataTable VARCHAR2(32), rasterID NUMBER, metadata SYS.XMLType ) / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_RASTER -- -- Description: -- -- This object type represents a single block of cells of the GeoRaster object. -- The object table of this type is used to stored the physical cell blocks. -- If the GeoRaster object is not blocked the whole object is stored as one block -- -- Members: -- -- rasterID - the GeoRaster object ID. This ID tells which GeoRaster -- object tis raster blob belongs to. -- pyramidLevel - Represents the level of the block within the Pyramid -- 0 means the original level (original data) -- positive number means decreased pyramid level -- negative number means increased pyramid level -- bandBlockNumber - the block number of this block along band dimension -- rowBlockNumber - the block number of this block along row dimension -- columnBlockNumber - the block number of this block along column dimension -- blockMBR - The boundary of each block stored as a geomtery type. -- It's a special geometry type with all coordinates being -- integers. It is and must be a rectangular type, i.e. MBR. -- It's defined in the cell coordinate system. -- rasterBlock - the real cell data of this block. -- -- Comments: -- -- Users should not directly edit on the objects of the object table of -- this type, i.e., the RDT of a geoRaster object. But they can directly -- query on this RDT table. -- -- The (bandBlockNumber, rowBlockNumber, columnBlockNumber) are the block numbers -- along each dimension in the cell space (not model space). The actual size of -- blocks is stored in metadata. This is designed for regular alligned blocking -- scheme only. -- -- the blockMBR has to be validated before any other operations -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate ' CREATE OR REPLACE TYPE SDO_RASTER TIMESTAMP ''2001-12-06:13:00:00'' OID ''9476CC705B2D1B06E0340800209D3EE1'' AS OBJECT ( rasterID NUMBER, pyramidLevel NUMBER, bandBlockNumber NUMBER, rowBlockNumber NUMBER, columnBlockNumber NUMBER, blockMBR MDSYS.SDO_GEOMETRY, rasterBlock BLOB) '; exception when others then NULL; end; end; / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_RASTERSET -- -- Description: -- -- This nested-table type is used as the return type of table functions -- which query the raster data blocks (1 or many, whole or subset) -- -- Members: -- -- Comments: -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_RASTERSET TIMESTAMP ''2001-12-06:13:00:00'' OID ''9476CC705B311B06E0340800209D3EE1'' AS TABLE of SDO_RASTER '; exception when others then NULL; end; end; / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_GEOR_METADATA -- -- Description: -- -- The SDO_GEOR_METEDATA is a XMLType which internally can contain any -- XML document. So it's good to represent the various GeoRaster metadata -- which are not included in SDO_GEORASTER.metadata and different for -- different original data sources. -- -- Members: -- -- metaType - The type of the metadata, or metadata schema. -- It represents the georaster source type, or say, -- source file format -- metadata - The metadata object defined by different XML schema -- The schemas are defined in the SDO_GEOR_XMLSCHEMA_TABLE -- -- Comments: -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_GEOR_METADATA TIMESTAMP ''2001-12-06:13:00:00'' OID ''9476CC705B2E1B06E0340800209D3EE1'' AS OBJECT ( metaType VARCHAR2(1024), metadata SYS.XMLType) ' ; exception when others then NULL; end; end; / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_GEOR_HISTOGRAM -- -- Description: -- -- This data type is used to hold a histogram of a GeoRaster object -- or a GeoRaster layer -- -- Members: -- cellValue - the cell value or projected cell value if there are -- scaling and/or BIN functions -- count - the number of cells corresponding to each cellValue -- -- Comments: -- -- the number of cellValues must equal to the number of cellCounts -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_GEOR_HISTOGRAM TIMESTAMP ''2001-12-06:13:00:00'' OID ''AC82441E38D9231BE0340003BA0F2AD3'' AS OBJECT ( cellValue SDO_NUMBER_ARRAY, count SDO_NUMBER_ARRAY) '; exception when others then NULL; end; end; / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_GEOR_GRAYSCALE -- -- Description: -- -- This data type is used to hold the gray scales of the grayScale of -- a GeoRaster object or a GeoRaster layer -- -- It is also used to hold the contrast table of a GeoRaster object or -- a GeoRaster layer -- -- Members: -- cellValue - the cell value or projected cell value if there are -- scaling and/or BIN functions -- gray - the grayscaled value corresponding to each cellValue -- -- Comments: -- -- the number of cellValues must equal to the number of grayValues -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_GEOR_GRAYSCALE TIMESTAMP ''2001-12-06:13:00:00'' OID ''AC82441E38DA231BE0340003BA0F2AD3'' AS OBJECT ( cellValue SDO_NUMBER_ARRAY, gray SDO_NUMBER_ARRAY) '; exception when others then NULL; end; end; / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_GEOR_COLORMAP -- -- Description: -- -- This data type is used to hold the colormap of a pseuodcolor -- GeoRaster object or a GeoRaster pseudocolor layer -- -- Members: -- cellValue - the cell value or projected cell value if there are -- scaling and/or BIN functions -- red - RED component value corresponding to each cellValue -- green - GREEN component value corresponding to each cellValue -- blue - BLUE component value corresponding to each cellValue -- alpha - ALPHA component value corresponding to each cellValue -- -- Comments: -- -- the number arrays must have the same length -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_GEOR_COLORMAP TIMESTAMP ''2001-12-06:13:00:00'' OID ''AC82441E38DB231BE0340003BA0F2AD3'' AS OBJECT ( cellValue SDO_NUMBER_ARRAY, red SDO_NUMBER_ARRAY, green SDO_NUMBER_ARRAY, blue SDO_NUMBER_ARRAY, alpha SDO_NUMBER_ARRAY) '; exception when others then NULL; end; end; / show errors; -------------------------------------------------------------------------------- -- Type SDO_GEOR_GCP -- -- Description: -- -- This object type defines the ground control point used in GeoRaster georeferencing -- -- Members: -- pointID - The unique ID of the control point. -- description - Information regarding the control point -- pointType - Control or Check Point -- cellDimension - The dimension of the point's image coordinates, 2 (col,row) or 3 (col, row, vertical) -- cellCoordinates - The control point's cell coordinates, (row, column, (vertical)) -- modelDimension - The dimension of the point's model coordinates, 2 (X,Y) or 3 (X,Y, Z) -- modelCoordinates - The control point's model coordinates, (X,Y) or (X,Y, Z) -- accuracy - Accuracy of the control point, (xMS, yMS) or (xMS, yRMS, zRMS) -- status - The status of the control. -- Comments: -- -------------------------------------------------------------------------------- declare begin begin execute immediate ' CREATE OR REPLACE TYPE SDO_GEOR_GCP TIMESTAMP ''2008-03-12:16:00:00'' OID ''48582B6E8602AD22E040578C67047362'' AS OBJECT ( pointID VARCHAR2(32), description VARCHAR2(256), -- can not use binary_integer pointType NUMBER, cellDimension NUMBER, cellCoordinates SDO_NUMBER_ARRAY, modelDimension NUMBER, modelCoordinates SDO_NUMBER_ARRAY, accuracy SDO_NUMBER_ARRAY, status NUMBER, CONSTRUCTOR FUNCTION SDO_GEOR_GCP(SELF IN OUT NOCOPY SDO_GEOR_GCP) RETURN SELF AS RESULT ) '; exception when others then NULL; end; end; / show errors; CREATE OR REPLACE TYPE BODY SDO_GEOR_GCP AS CONSTRUCTOR FUNCTION SDO_GEOR_GCP(SELF IN OUT NOCOPY SDO_GEOR_GCP) RETURN SELF AS RESULT IS BEGIN RETURN; END; END; / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_GEOR_GCP_COLLECTION -- -- Description: -- -- This VARRAY type is used to hold the list of the control points in the model -- -- Members: -- -- Comments: -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_GEOR_GCP_COLLECTION TIMESTAMP ''2008-03-12:16:00:00'' OID ''4856DAAB1325DB0AE040578C67047065'' AS VARRAY(1048576) OF SDO_GEOR_GCP'; exception when others then NULL; end; end; / show errors; ------------------------------------------------------------------------------- -- Type SDO_GEOR_GCPGEOREFTYPE -- -- Description: -- -- This object type stores information of a georeference model that include -- the georeference polynomial type, control points for solving the model parameters, -- and solution accuracy, etc. -- -- Members: -- -- FFMethodType - the type of georeference polynomial type. -- numberGCP - number of ground control points inputted for the model -- GCPs - list of the ground control points -- solutionAccuracy - overall RMS and dimensional row/col/x/y/zRMS -- -- Comments: -- -- solutionAccuracy is only for output, and useful for getGCPGeorefModel() -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate ' CREATE OR REPLACE TYPE SDO_GEOR_GCPGEOREFTYPE TIMESTAMP ''2008-03-12:16:00:00'' OID ''4856DAAB1328DB0AE040578C67047065'' AS OBJECT ( FFMethodType VARCHAR2(32), numberGCP NUMBER, GCPs SDO_GEOR_GCP_COLLECTION, solutionAccuracy SDO_NUMBER_ARRAY, CONSTRUCTOR FUNCTION SDO_GEOR_GCPGEOREFTYPE(SELF IN OUT NOCOPY SDO_GEOR_GCPGEOREFTYPE) RETURN SELF AS RESULT ) '; exception when others then NULL; end; end; / show errors; CREATE OR REPLACE TYPE BODY SDO_GEOR_GCPGEOREFTYPE AS CONSTRUCTOR FUNCTION SDO_GEOR_GCPGEOREFTYPE(SELF IN OUT NOCOPY SDO_GEOR_GCPGEOREFTYPE) RETURN SELF AS RESULT IS BEGIN SELF.FFMethodType := 'Affine'; RETURN; END; END; / show errors; -- ----------------------------------------------------------------------------- -- Type SDO_GEOR_SRS -- -- Description: -- -- This opject type is used when users query the GeoRaster Spatial -- Reference System information DIRECTLY using a SQL query and want the -- returned value to be accessible DIRECTLY in SQL or PL/SQL. The original -- SRS information is stored in XML documents (SDO_GEORASTER.metadata). -- It is also used to set or modify the GeoRaster SRS. -- -- It contains the polynomial functional fitting model -- -- Members: -- -- isReferenced - TRUE if the GeoRaster object is georeferenced -- isRectified - TRUE if the GeoRaster object is rectified -- isOrthoRectified - TRUE if the GeoRaster object is ortho-rectified -- srid - The SRID of its Model/Ground Coordinate System -- spatialResolution - Only its first three numbers are used, for XYZ -- spatialTolerance - Same as the tolerance number of geometry metadata -- coordLocation - The coordinate represents either UPPERLEFT corner -- or CENTER of the cells in the Model CS Space -- rowNumerator - pType, nVars, order, nCoefficients and all coefficients -- of the numerator of the row polynomial -- rowDenominator - pType, nVars, order, nCoefficients and all coefficients -- of the denominator of the row polynomial -- colDenominator - pType, nVars, order, nCoefficients and all coefficients -- of the numerator of the column polynomial -- colNumerator - pType, nVars, order, nCoefficients and all coefficients -- of the denominator of the column polynomial -- others are explained in the user manual -- -- Comments: -- -- For complete SRS data users can retrieve the XML doc -- -- Users can get spatial extent (footprint) from the GeoRaster object directly -- -- ----------------------------------------------------------------------------- declare begin begin execute immediate ' CREATE OR REPLACE TYPE SDO_GEOR_SRS TIMESTAMP ''2001-12-06:13:00:00'' OID ''9476CC705B301B06E0340800209D3EE1'' AS OBJECT ( isReferenced VARCHAR2(5), isRectified VARCHAR2(5), isOrthoRectified VARCHAR2(5), srid NUMBER, spatialResolution SDO_NUMBER_ARRAY, spatialTolerance NUMBER, coordLocation NUMBER, rowOff NUMBER, columnOff NUMBER, xOff NUMBER, yOff NUMBER, zOff NUMBER, rowScale NUMBER, columnScale NUMBER, xScale NUMBER, yScale NUMBER, zScale NUMBER, rowRMS NUMBER, columnRMS NUMBER, totalRMS NUMBER, rowNumerator SDO_NUMBER_ARRAY, rowDenominator SDO_NUMBER_ARRAY, columnNumerator SDO_NUMBER_ARRAY, columnDenominator SDO_NUMBER_ARRAY) '; exception when others then NULL; end; begin execute immediate 'ALTER TYPE SDO_GEOR_SRS ADD CONSTRUCTOR FUNCTION SDO_GEOR_SRS(SELF IN OUT NOCOPY SDO_GEOR_SRS) RETURN SELF AS RESULT'; exception when others then NULL; end; begin execute immediate 'ALTER TYPE SDO_GEOR_SRS ADD attribute ( xRMS NUMBER, yRMS NUMBER, zRMS NUMBER, modelTotalRMS NUMBER, GCPgeoreferenceModel SDO_GEOR_GCPGEOREFTYPE)'; exception when others then NULL; end; begin execute immediate 'ALTER TYPE SDO_GEOR_SRS ADD CONSTRUCTOR FUNCTION SDO_GEOR_SRS( isReferenced VARCHAR2, isRectified VARCHAR2, isOrthoRectified VARCHAR2, srid NUMBER, spatialResolution SDO_NUMBER_ARRAY, spatialTolerance NUMBER, coordLocation NUMBER, rowOff NUMBER, columnOff NUMBER, xOff NUMBER, yOff NUMBER, zOff NUMBER, rowScale NUMBER, columnScale NUMBER, xScale NUMBER, yScale NUMBER, zScale NUMBER, rowRMS NUMBER, columnRMS NUMBER, totalRMS NUMBER, rowNumerator SDO_NUMBER_ARRAY, rowDenominator SDO_NUMBER_ARRAY, columnNumerator SDO_NUMBER_ARRAY, columnDenominator SDO_NUMBER_ARRAY) RETURN SELF AS RESULT'; exception when others then NULL; end; end; / show errors; CREATE OR REPLACE TYPE BODY SDO_GEOR_SRS AS CONSTRUCTOR FUNCTION SDO_GEOR_SRS(SELF IN OUT NOCOPY SDO_GEOR_SRS) RETURN SELF AS RESULT IS BEGIN SELF.isReferenced := 'FALSE'; RETURN; END; CONSTRUCTOR FUNCTION SDO_GEOR_SRS( isReferenced VARCHAR2, isRectified VARCHAR2, isOrthoRectified VARCHAR2, srid NUMBER, spatialResolution SDO_NUMBER_ARRAY, spatialTolerance NUMBER, coordLocation NUMBER, rowOff NUMBER, columnOff NUMBER, xOff NUMBER, yOff NUMBER, zOff NUMBER, rowScale NUMBER, columnScale NUMBER, xScale NUMBER, yScale NUMBER, zScale NUMBER, rowRMS NUMBER, columnRMS NUMBER, totalRMS NUMBER, rowNumerator SDO_NUMBER_ARRAY, rowDenominator SDO_NUMBER_ARRAY, columnNumerator SDO_NUMBER_ARRAY, columnDenominator SDO_NUMBER_ARRAY) RETURN SELF AS RESULT IS BEGIN SELF.isReferenced :=isReferenced; SELF.isRectified :=isRectified; SELF.isOrthoRectified :=isOrthoRectified; SELF.srid :=srid; SELF.spatialResolution :=spatialResolution; SELF.spatialTolerance :=spatialTolerance; SELF.coordLocation :=coordLocation; SELF.rowOff :=rowOff; SELF.columnOff :=columnOff; SELF.xOff :=xOff; SELF.yOff :=yOff; SELF.zOff :=zOff; SELF.rowScale :=rowScale; SELF.columnScale :=columnScale; SELF.xScale :=xScale; SELF.yScale :=yScale; SELF.zScale :=zScale; SELF.rowRMS :=rowRMS; SELF.columnRMS :=columnRMS; SELF.totalRMS :=totalRMS; SELF.rowNumerator :=rowNumerator; SELF.rowDenominator :=rowDenominator; SELF.columnNumerator :=columnNumerator; SELF.columnDenominator :=columnDenominator; RETURN; END; END; /