Rem Rem $Header: sdo/admin/sdogmlsc.sql /main/2 2009/12/02 05:47:49 mhorhamm Exp $ Rem Rem sdogmlsc.sql Rem Rem Copyright (c) 2005, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdogmlsc.sql - Rem Rem DESCRIPTION Rem This file is used to register the 3 schemas required for GML 212 Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem sravada 05/12/05 - sravada_sdo_text_object Rem sravada 05/03/05 - Created Rem declare begin begin execute immediate 'CREATE TABLE SDO_XML_SCHEMAS ( id NUMBER PRIMARY KEY, description VARCHAR2(300), xmlSchema CLOB) '; exception when others then NULL; end; end; / GRANT SELECT ON SDO_XML_SCHEMAS TO PUBLIC; create or replace public synonym SDO_XML_SCHEMAS for MDSYS.SDO_XML_SCHEMAS; -- first delete all the GML related schemas declare usr varchar2(30); url varchar2(200); loc clob; cnt number; begin usr := 'MDSYS'; url := 'http://www.opengis.net/cartographicText.xsd'; execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if cnt <> 0 then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; url := 'http://www.opengis.net/gml/feature.xsd'; execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if cnt <> 0 then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; url := 'http://www.opengis.net/gml/geometry.xsd'; execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if cnt <> 0 then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; url := 'http://www.w3.org/1999/xlink/xlinks.xsd'; execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if cnt <> 0 then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; end; / -- register the schema DECLARE schemaclob CLOB; amt NUMBER; buf VARCHAR2(32767); pos NUMBER; BEGIN DELETE FROM SDO_XML_SCHEMAS WHERE id=1; INSERT INTO SDO_XML_SCHEMAS VALUES (1, 'GML:2.1.2 xlinks.xsd', empty_clob()) RETURNING xmlSchema into schemaclob; SELECT xmlSchema into schemaclob from SDO_XML_SCHEMAS WHERE id = 1 FOR UPDATE; DBMS_LOB.OPEN(schemaclob, DBMS_LOB.LOB_READWRITE); buf := ' xlinks.xsd v2.1.2 2002-07 This schema provides the XLink attributes for general use. The show attribute is used to communicate the desired presentation of the ending resource on traversal from the starting resource; its value should be treated as follows: new - load ending resource in a new window, frame, pane, or other presentation context replace - load the resource in the same window, frame, pane, or other presentation context embed - load ending resource in place of the presentation of the starting resource other - behavior is unconstrained; examine other markup in the link for hints none - behavior is unconstrained The actuate attribute is used to communicate the desired timing of traversal from the starting resource to the ending resource; its value should be treated as follows: onLoad - traverse to the ending resource immediately on loading the starting resource onRequest - traverse from the starting resource to the ending resource only on a post-loading event triggered for this purpose other - behavior is unconstrained; examine other markup in link for hints none - behavior is unconstrained '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(schemaclob, amt, pos, buf); DBMS_LOB.CLOSE(schemaclob); COMMIT; END; / SHOW ERRORS; declare usr varchar2(30); url varchar2(200); loc clob; cnt number; begin usr := 'MDSYS'; url := 'http://www.w3.org/1999/xlink/xlinks.xsd'; -- First check whether the schema has been registered already. -- If it has, de-register it. This might cause an error to be raised if -- there are dependent tables or schemas. User then needs to manually -- remove/evolve the depdendent objects and run this script again. execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if cnt <> 0 then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; select xmlSchema into loc from SDO_XML_SCHEMAS where id = 1; -- register the schema dbms_xmlschema.registerSchema(url, loc, FALSE, FALSE, FALSE, FALSE, FALSE, usr); end; / DECLARE schemaclob CLOB; amt NUMBER; buf VARCHAR2(32767); pos NUMBER; BEGIN DELETE FROM SDO_XML_SCHEMAS WHERE id=2; INSERT INTO SDO_XML_SCHEMAS VALUES (2, 'GML:2.1.2 geometry.xsd', empty_clob()) RETURNING xmlSchema into schemaclob; SELECT xmlSchema into schemaclob from SDO_XML_SCHEMAS WHERE id = 2 FOR UPDATE; DBMS_LOB.OPEN(schemaclob, DBMS_LOB.LOB_READWRITE); buf := ' geometry.xsd v2.1.2 2002-07 GML Geometry schema. Copyright (c) 2001,2002 OGC, All Rights Reserved. All geometry elements are derived from this abstract supertype; a geometry element may have an identifying attribute (gid). It may be associated with a spatial reference system. This abstract base type for geometry collections just makes the srsName attribute mandatory. These attributes can be attached to any element, thus allowing it to act as a pointer. The remoteSchema attribute allows an element that carries link attributes to indicate that the element is declared in a remote schema rather than by the schema that constrains the current document instance. An instance of this type (e.g. a geometryMember) can either enclose or point to a primitive geometry element. When serving as a simple link that references a remote geometry instance, the value of the gml:remoteSchema attribute can be used to locate a schema fragment that constrains the target instance. Restricts the geometry member to being a Point instance. Restricts the geometry member to being a LineString instance. Restricts the geometry member to being a Polygon instance. Restricts the outer or inner boundary of a polygon instance to being a LinearRing. A Point is defined by a single coordinate tuple. A LineString is defined by two or more coordinate tuples, with linear interpolation between them. A LinearRing is defined by four or more coordinate tuples, with linear interpolation between them; the first and last coordinates must be coincident. The Box structure defines an extent using a pair of coordinate tuples. A Polygon is defined by an outer boundary and zero or more inner boundaries which are in turn defined by LinearRings. A geometry collection must include one or more geometries, referenced through geometryMember elements. User-defined geometry collections that accept GML geometry classes as members must instantiate--or derive from--this type. A MultiPoint is defined by one or more Points, referenced through pointMember elements. A MultiLineString is defined by one or more LineStrings, referenced through lineStringMember elements. A MultiPolygon is defined by one or more Polygons, referenced through polygonMember elements. Represents a coordinate tuple in one, two, or three dimensions. Coordinates can be included in a single string, but there is no facility for validating string content. The value of the cs attribute is the separator for coordinate values, and the value of the ts attribute gives the tuple separator (a single space by default); the default values may be changed to reflect local usage. '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(schemaclob, amt, pos, buf); DBMS_LOB.CLOSE(schemaclob); COMMIT; END; / SHOW ERRORS; declare usr varchar2(30); url varchar2(200); loc clob; cnt number; begin usr := 'MDSYS'; url := 'http://www.opengis.net/gml/geometry.xsd'; -- First check whether the schema has been registered already. -- If it has, de-register it. This might cause an error to be raised if -- there are dependent tables or schemas. User then needs to manually -- remove/evolve the depdendent objects and run this script again. execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if cnt <> 0 then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; select xmlSchema into loc from SDO_XML_SCHEMAS where id = 2; -- register the schema dbms_xmlschema.registerSchema(url, loc, FALSE, FALSE, FALSE, FALSE, FALSE, usr); end; / DECLARE schemaclob CLOB; amt NUMBER; buf VARCHAR2(32767); pos NUMBER; BEGIN DELETE FROM SDO_XML_SCHEMAS WHERE id=3; INSERT INTO SDO_XML_SCHEMAS VALUES (3, 'GML:2.1.2 feature.xsd', empty_clob()) RETURNING xmlSchema into schemaclob; SELECT xmlSchema into schemaclob from SDO_XML_SCHEMAS WHERE id = 3 FOR UPDATE; DBMS_LOB.OPEN(schemaclob, DBMS_LOB.LOB_READWRITE); buf := ' feature.xsd v2.1.2 2002-07 GML Feature schema. Copyright (c) 2002 OGC, All Rights Reserved. An abstract feature provides a set of common properties. A concrete feature type must derive from this type and specify additional properties in an application schema. A feature may optionally possess an identifying attribute (fid). This abstract base type just makes the boundedBy element mandatory for a feature collection. A feature collection contains zero or more featureMember elements. A simple geometry property encapsulates a geometry element. Alternatively, it can function as a pointer (simple-type link) that refers to a remote geometry element. An instance of this type (e.g. a featureMember) can either enclose or point to a feature (or feature collection); this type can be restricted in an application schema to allow only specified features as valid participants in the association. When serving as a simple link that references a remote feature instance, the value of the gml:remoteSchema attribute can be used to locate a schema fragment that constrains the target instance. Bounding shapes--a Box or a null element are currently allowed. Encapsulates a single point to represent position, location, or centerOf properties. Encapsulates a single polygon to represent coverage or extentOf properties. Encapsulates a single LineString to represent centerLineOf or edgeOf properties. Encapsulates a MultiPoint element to represent the following discontiguous geometric properties: multiLocation, multiPosition, multiCenterOf. Encapsulates a MultiLineString element to represent the following discontiguous geometric properties: multiEdgeOf, multiCenterLineOf. Encapsulates a MultiPolygon to represent the following discontiguous geometric properties: multiCoverage, multiExtentOf. Encapsulates a MultiGeometry element. If a bounding shape is not provided for a feature collection, explain why. Allowable values are: innapplicable - the features do not have geometry unknown - the boundingBox cannot be computed unavailable - there may be a boundingBox but it is not divulged missing - there are no features '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(schemaclob, amt, pos, buf); DBMS_LOB.CLOSE(schemaclob); COMMIT; END; / SHOW ERRORS; declare usr varchar2(30); url varchar2(200); loc clob; cnt number; begin usr := 'MDSYS'; url := 'http://www.opengis.net/gml/feature.xsd'; -- First check whether the schema has been registered already. -- If it has, de-register it. This might cause an error to be raised if -- there are dependent tables or schemas. User then needs to manually -- remove/evolve the depdendent objects and run this script again. execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if cnt <> 0 then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; select xmlSchema into loc from SDO_XML_SCHEMAS where id = 3; -- register the schema dbms_xmlschema.registerSchema(url, loc, FALSE, FALSE, FALSE, FALSE, FALSE, usr); end; / commit; -- register the schema DECLARE schemaclob CLOB; amt NUMBER; buf VARCHAR2(32767); pos NUMBER; BEGIN DELETE FROM SDO_XML_SCHEMAS WHERE id=4; INSERT INTO SDO_XML_SCHEMAS VALUES (4, 'EPSG sdoepsggrid.xsd', empty_clob()) RETURNING xmlSchema into schemaclob; SELECT xmlSchema into schemaclob from SDO_XML_SCHEMAS WHERE id = 4 FOR UPDATE; DBMS_LOB.OPEN(schemaclob, DBMS_LOB.LOB_READWRITE); buf := ' Root element describing the grid or set of grids used for a particular transformation Root element describing the grid or set of grids used for a particular transformation This represents a flat list of grids. Hierarchies can be imposed on the grids in a separate structure within each Grid. Represents a single grid, of which a grid file might have several Combines several coordinates (usually 2) to one vector Coordinate (based on source coordinate reference system) Represents a single grid, of which a grid file might have several Grid position, size, and resolution/density The grid offset data One single node in the grid, describing the offset at on point Coordinate offset This represents the precision of the offset. The precision is represented as the maximum absolute error, given in the same unit of measure as the offset itself. If the local precision within the grid is unknown, the global maximum possible error shall be quoted. Data with unbounded (unknown) possible error would be worthless, thus some quote of precision is mandatory. If the original data source does not explicitly specify precision, but is reputed to be of high quality, an absolute error of 0 (perfect precision) shall be quoted. Grid position, size, and resolution/density Lower left corner of the grid (coordinates based on source coordinate reference system) Upper right corner of the grid (coordinates based on source coordinate reference system) Grid resolution/density (unit of measure based on source coordinate reference system) One single horizontal line within the grid One single node in the grid, describing the offset at one point Represents a single coordinate; the unit depends on the elsewhere specified source coordinate reference system Represents a coordinate reference system (identified by EPSG id) Represents a transformation (identified by EPSG id) Defines a single transformation, with source and target CRS, using this grid file The source coordinate reference system The transformation using this grid file The target coordinate reference system Defines the set of transformations using this grid file Specifies a hierarchy that the grid is member of, and its parent within that hierarchy Specifies the hierarchy URI Specifies the parent node IDREF Defines creation and update date and time One single (horizontal) row within the grid (all rows have the same number of nodes) The unit type (length or angle) The EPSG unit ID How many base units are equal to one of these units? The base unit of length is meter, the base unit of angle is radians. '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(schemaclob, amt, pos, buf); DBMS_LOB.CLOSE(schemaclob); COMMIT; END; / SHOW ERRORS; declare usr varchar2(30); url varchar2(200); loc clob; cnt number; begin usr := 'MDSYS'; url := 'http://www.oracle.com/2004/spatial/epsg/gridfile/schema/sdoepsggrid.xsd'; execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if(cnt <> 0) then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; select xmlSchema into loc from SDO_XML_SCHEMAS where id = 4; DBMS_XMLSCHEMA.registerSchema( SCHEMAURL => url, SCHEMADOC => loc, LOCAL => FALSE, OWNER => 'MDSYS'); end; / commit; -- register the schema DECLARE schemaclob CLOB; amt NUMBER; buf VARCHAR2(32767); pos NUMBER; BEGIN DELETE FROM SDO_XML_SCHEMAS WHERE id=5; INSERT INTO SDO_XML_SCHEMAS VALUES (5, 'EPSG sdo3d.xsd', empty_clob()) RETURNING xmlSchema into schemaclob; SELECT xmlSchema into schemaclob from SDO_XML_SCHEMAS WHERE id = 5 FOR UPDATE; DBMS_LOB.OPEN(schemaclob, DBMS_LOB.LOB_READWRITE); buf := ' Declares a 3D style, with color, texture, and model style Declares a theme, with default style(s) and a reference to the theme with the next (lower) LOD level Declares a scene, with multiple themes Declares a view frame, specifying a scene and a view point Version 08-07-15 Stored in DB as VARCHAR2 or SDO_ORDINATE_ARRAY This describes a texture for a geometry. For now, it only represents a single texture, as opposed to multiple textures being superimposed. Also, alpha maps and bump maps are not yet mentioned. They will be added, subsequently. Another aspect is that this schema currently only maps a single texture image per geometry. To map a separate facade to each wall of a building, the facades simply get aggregated into a single bitmap file. The texture coordinates link facades to walls. Model object is applied to an oriented point. The model object has SRID null (euclidean). It is applied to the oriented point, such that the euclidean origin coincides with the point, its z-axis coincides with the orientation vector of the point, and scaling is performed, such that the orientation vector represents one unit in the model object euclidean CRS. Version 08-07-15 Stored in DB as BLOB Stored in DB as SDO_ORDINATE_ARRAY. Label Strings are not required, in this xsd, since we assume a texture coordinate array for the entire geometry. The database can supply tools to update texture coordinate arrays, using label strings. This declares Level Of Detail information, as well as a reference to the next less detailed LOD theme within a chain. There may be a chain of themes with an increasingly detailed representation of the same concept. In a more detailed theme, there is at least one feature for each feature in the less detailed theme. A simple city block in theme 1 may split into several low-detail buildings in theme 2 (separate features). Each of these may map to a single high-detail representation in theme 3. The link is provided by a foreign key from the more detailed theme to the less detailed one. The least detailed theme has no such foreign key. This refers to optional attribute columns This describes optional external representations, such as CityGML This refers to an optional GML column / foreign key This refers to an optional CityGML column / foreign key This refers to an optional KML column / foreign key This refers to an optional X3D column / foreign key This refers to an optional BIM column / foreign key Name of value column in target table Foreign key for joining consumer table with target table The location of the eye The point where the eye is looking The up vector specifying the frustum''s up direction The frustum establishes a view model with the eye at the apex of a symmetric view frustum Fade in in ms Pause in ms Fade out in ms Declares a view point The frustum establishes a view model with the eye at the apex of a symmetric view frustum The vertical line on the left edge of the near clipping plane mapped to the left edge of the graphics window The vertical line on the right edge of the near clipping plane mapped to the right edge of the graphics window The horizontal line on the bottom edge of the near clipping plane mapped to the bottom edge of the graphics window The horizontal line on the top edge of the near The distance to the frustum''s near clipping plane. This value must be positive, (the value -near is the location of the near clip plane). The distance to the frustum''s far clipping plane. This value must be positive, and must be greater than near. This refers to style descriptions, such as color, texture, model style, etc. A scene may have several themes. There may be a chain of increasingly detailed themes, representing the same concept. Only the most detailed admissable theme has to be referenced, here. Any less-detailed themes will automatically be rendered, as appropriate. Any more detailed themes are inadmissable for rendering in this scene. '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(schemaclob, amt, pos, buf); DBMS_LOB.CLOSE(schemaclob); COMMIT; END; / SHOW ERRORS; declare usr varchar2(30); url varchar2(200); loc clob; cnt number; begin usr := 'MDSYS'; url := 'http://www.oracle.com/2009/spatial/vis3d/schema/sdo3d.xsd'; execute immediate 'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2' into cnt using usr, url; if(cnt <> 0) then dbms_xmlschema.deleteSchema(url, dbms_xmlschema.DELETE_CASCADE); end if; select xmlSchema into loc from SDO_XML_SCHEMAS where id = 5; DBMS_XMLSCHEMA.registerSchema( SCHEMAURL => url, SCHEMADOC => loc, LOCAL => FALSE, OWNER => 'MDSYS'); end; / commit;