Show Navigation | Hide Navigation

You are here:
Geodatabases and ArcSDE > Geodatabase data storage and schema > Inside a geodatabase in Oracle

ST_Geometry storage in Oracle
Release 9.3 Last modified October 12, 2010	Print all topics in : "Inside a geodatabase in Oracle"

Related Topics

The ST_Geometry storage type

Note: This topic was updated for 9.3.1.

ArcSDE for Oracle offers a geometry storage type that provides International Organization for Standardization (ISO)- and Open Geospatial Consortium, Inc. (OGC)-compliant structured query language (SQL) access to the geodatabase. This storage extends the capabilities of the Oracle database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of Oracle database resources; to be compatible with Oracle database features such as replication and partitioning; and to provide rapid access to spatial data. It allows you to integrate spatial data with other types of business data, so your multiuser database gains the advantage of adding a geographic component to your analyses and data products. Keeping your spatial data together with other business objects also simplifies multiuser access, management, and security of your data because you will have to manage fewer data storage resources.

Beginning with ArcGIS 9.3, new ArcSDE geodatabases for Oracle use the ST_Geometry spatial type for geometry storage by default. It implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. For an explanation of the ST_Geometry spatial type, refer to the How ST_Geometry stores spatial data section of this topic.

Using the ST_Geometry spatial type in an ArcSDE geodatabase for Oracle, you can access your spatial data through SQL functions that implement the ISO SQL/MM Spatial Standard and to the Simple Feature Specification of the OGC. You can use SQL commands to store, retrieve, and manipulate spatial features as you would any other type of data. You can use a long list of standards-based functions with SQL commands and stored procedures to retrieve and analyze spatial data. Having SQL access to the data makes it possible for you to use other applications to access data that was created in an ArcSDE geodatabase for Oracle.

To use SQL to access a geodatabase in Oracle that uses the spatial type, you must configure the Oracle Listener. See Configuring Oracle Net Services to use ST_Geometry SQL functions for details.

Beginning with ArcGIS 9.3, new ArcSDE geodatabases for Oracle require that all ST functions and operators be qualified with the SDE schema name. For example, if executing a query that uses the ST_Union operator, the operator must be typed as "sde.ST_Union". Geodatabases upgraded from ArcSDE 9.2 or earlier do not require this.

This topic describes

For information on how to work with tables that use ST_Geometry storage using SQL, see

NOTE: To access spatial features with SQL, the ST_Geometry libraries have to be installed on the same server as the Oracle DBMS. If you plan to run your Oracle DBMS on a server separate from ArcSDE, be sure the operating system of your Oracle server is supported for ArcSDE as well.

How ST_Geometry stores spatial data

The following is the Oracle description of ST_Geometry:

Name	Type
ENTITY	NUMBER(38)
NUMPTS	NUMBER(38)
MINX	FLOAT(64)
MINY	FLOAT(64)
MAXX	FLOAT(64)
MAXY	FLOAT(64)
MINZ	FLOAT(64)
MAXZ	FLOAT(64)
MINM	FLOAT(64)
MAXM	FLOAT(64)
AREA	FLOAT(64)
LEN	FLOAT(64)
SRID	NUMBER(38)
POINTS	BLOB

The attributes of the spatial type represent the following information:

Entity—The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or polygon), the value of which is a bit mask derived from the st_geom_util stored procedure
Numpts—The number of points defining the geometry; for multipart geometries, includes the separators between each part, one point for each separator
Minx, miny, maxx, maxy—The spatial envelope of the geometry
Area—The area of the geometry
Len—The perimeter length of the geometry
SRID—Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the ST_Spatial_References table
Points—Contains the byte stream of the point coordinates that define the geometry

Like other object types, the ST_Geometry data type contains a constructor method and functions. A constructor method is a function that returns a new instance (object) of the data type and sets up the values of its attributes.

The name of the constructor is the same as the type (ST_Geometry). When you instantiate an object of the ST_Geometry type, you invoke the constructor method. For example:

create table hazardous_sites (name        varchar2(128),
                              location    st_geometry);

The following are ST_Geometry accessor functions take a single ST_Geometry as input and return the requested property value as a number.

The ST_Area member function returns the area of a geometry.
ST_Len returns the length of a geometry.
ST_Entity returns a number containing a bit mask that describes the entity type.
ST_NumPoints returns the number of points (vertices) that define a geometry.
ST_MinM, ST_MinX, ST_MinY, ST_MinZ returns the minimum desired coordinate of a geometry.
ST_MaxM, ST_MaxX, ST_MaxY, ST_MaxZ return the maximum desired coordinate of a geometry.
ST_SRID returns the spatial reference identifier for a geometry.
Get_release is a static member function used internally for spatial type administration (i.e., upgrades and patches).

For example, the following query returns the name and area of the individual states in the United States.

SELECT name, st_area(geometry)
FROM us_states
ORDER BY name;

ST_LineString, ST_MultiLineString, ST_MultiPoint, ST_MultiPolygon, ST_Point, and ST_Polygon are all subtypes (or subclasses) of ST_Geometry. ST_Geometry and its subtypes share common attributes and functions. The constructor definition for ST_LineString, ST_MultiLineString, ST_MultiPoint, ST_MultiPolygon, ST_Point, and ST_Polygon is the same. The name of the constructor is the same as the type it contructs.

Since ST_Point is a finite object (a single point value), it can also be created using one of the following methods.

This method utilizes coordinate points and an SRID.

METHOD
 FINAL CONSTRUCTOR FUNCTION ST_POINT RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?
 PT_X                           NUMBER                  IN
 PT_Y                           NUMBER                  IN
 SRID                           NUMBER                  IN
SQL> insert into sample_pt values (ST_Point (10, 20, 1) );

This method allows a user to specify coordinate points and an elevation value for each point.

METHOD
 FINAL CONSTRUCTOR FUNCTION ST_POINT RETURNS SELF AS RESULT
Argument Name                  Type                    In/Out Default?

 PT_X                           NUMBER                  IN
 PT_Y                           NUMBER                  IN
 PT_Z                           NUMBER                  IN
 SRID                           NUMBER                  IN
SQL> insert into sample_pt values (ST_Point (10, 20, 5, 1) );

This last method for ST_Point additionally allows a measure value to be specified as part of the point object created.

METHOD
 FINAL CONSTRUCTOR FUNCTION ST_POINT RETURNS SELF AS RESULT
Argument Name                  Type                    In/Out Default?
 PT_X                           NUMBER                  IN
 PT_Y                           NUMBER                  IN
 PT_Z                           NUMBER                  IN
 MEASURE                        NUMBER                  IN
 SRID                           NUMBER                  IN
SQL> insert into sample_pt values (ST_Point (10, 20, 5, 401, 1) );

SP_GRID_INFO
SP_Grid_Info is used as the data type for the field GRID in the table ST_Geometry_Index. It contains the grid-level information for spatial indexes.

SQL> desc sp_grid_info
 Name                                      Null?    Type
 GRID1                                              NUMBER
 GRID2                                              NUMBER
 GRID3                                              NUMBER

Schema user
To install ArcSDE and use the ST_Geometry type and domain index in the Oracle DBMS, the ArcSDE administrator user must be granted the proper system privileges to instantiate types, operators, and stored procedures. See User permissions for geodatabases in Oracle for information on permissions needed.

Metadata schema

The spatial type for Oracle types and metadata tables are owned by the SDE schema. The schema definition is the base table description for metadata tables used to define and describe the type column/table, spatial index, and spatial references information. The term type refers to the ST_Geometry spatial type. Spatial index refers to the ST_Spatial_Index domain index. All type and domain index type definitions, packages, and metadata tables are created in the SDE schema.

Views will also be prefixed ST_ including the OpenGIS views on GEOMETRY_COLUMNS and SPATIAL_REFERENCES.

Because the definitions for ST_Geometry are owned by the SDE user, you should never delete the SDE user from the database if you still have tables in the database that contain ST_Geometry columns. Doing so will cause those tables to be inaccessible.

As mentioned in Oracle's Application Developers Guide, when a user is dropped from the database, one of the drop statements executed is DROP TYPE with the FORCE option. This statement removes all types owned by that user, so that user can be removed from the database. DROP TYPE FORCE causes types to be dropped even if they have dependent types or tables associated with them. Once that happens, the associated tables are marked invalid, rendering the data in the tables inaccessible.

For a description of each table, see the tables listed in System tables of a geodatabase stored in Oracle. The tables are as follows:

ST_COORDINATE_SYSTEMS
ST_GEOMETRY_COLUMNS
ST_GEOMETRY_INDEX
ST_SPATIAL_REFERENCES

Creating feature classes in Oracle with ST_Geometry storage

Beginning with ArcGIS 9.3, when you first install the ArcSDE component, the ST_Geometry schema is the default storage type. The default settings for ArcSDE storage are defined in the DBTUNE table by the GEOMETRY_STORAGE parameters.

If you want to store most of your feature class data in the ST_Geometry format, leave the GEOMETRY_STORAGE parameter of the DEFAULTS keyword set to ST_GEOMETRY.

If you initially created your geodatabase prior to ArcGIS 9.3, you must alter your DBTUNE table's settings to create feature classes with ST_GEOMETRY columns by default by setting the GEOMETRY_STORAGE parameter under the DEFAULTS keyword to ST_GEOMETRY.

You should also alter the ST_GEOM_LOB_STORAGE parameter under the DEFAULTS keyword list. When added to the DEFAULTS keyword, however, the LOB segment name should not be included in this parameter's definition. If it is included, unless you alter the value for the name, feature class creation will fail when a second feature class is created, because each LOB segment name must be unique for a given schema. The following ST_GEOM_LOB_STORAGE parameter example contains no object names, thereby avoiding name collisions within the same schema:

ST_GEOM_LOB_STORAGE  " STORE AS (
  ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE")

NOTE: Use the sdedbtune administration command to alter DBTUNE settings. For details on using the sdedbtune command, consult the ArcSDE Administration Command Reference.

ArcSDE for Oracle supports a number of different geometry storage schemas—these different schemas can all be used together in the same database. While there can be only one default geometry schema, individual tables can be created using different geometry schemas.

If you want to store just some of your feature classes with spatial type storage, you can specify the keyword ST_GEOMETRY when you create the feature class. If you do this, that particular feature class will be created with an ST_Geometry column. In the dbtune file, the ST_GEOMETRY keyword appears as follows:

##ST_GEOMETRY
GEOMETRY_STORAGE    "ST_GEOMETRY"
ATTRIBUTE_BINARY    "BLOB"
RASTER_STORAGE	    "BLOB"
ST_GEOM_LOB_STORAGE  " STORE AS (
#               TABLESPACE <tablespace_name>
                ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
#               INDEX (TABLESPACE <tablespace_name>)"
UI_TEXT             "User Interface text description for ST_GEOMETRY"
COMMENT             "Any general comment for ST_GEOMETRY keyword"
END

Examples of valid values for the ST_GEOM_LOB_STORAGE parameter include the following:

##ST_GEOMETRY
GEOMETRY_STORAGE    "ST_GEOMETRY"
ATTRIBUTE_BINARY    "BLOB"
RASTER_STORAGE	    "BLOB"
ST_GEOM_LOB_STORAGE  " STORE AS (TABLESPACE TERRA_NDX ENABLE STORAGE IN ROW CHUNK 8K
 RETENTION CACHE INDEX (TABLESPACE LOBIDX)"
UI_TEXT             "User Interface text description for ST_GEOMETRY"
COMMENT             "Any general comment for ST_GEOMETRY keyword"
END

##ST_GEOMETRY
GEOMETRY_STORAGE    "ST_GEOMETRY"
ATTRIBUTE_BINARY    "BLOB"
RASTER_STORAGE	    "BLOB"
ST_GEOM_LOB_STORAGE  " STORE AS (ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE)"
UI_TEXT             "User Interface text description for ST_GEOMETRY"
COMMENT             "Any general comment for ST_GEOMETRY keyword"
END

NOTE: As mentioned earlier in this section, if you define the LOB and LOB index table space names, you must alter these values prior to each feature class creation. If you do not, subsequent feature class creations will fail because each segment name must be unique.

For more examples and further information on creating and maintaining Oracle LOB segments, search the ESRI support site for "LOB segment." Several knowledge base articles exist on the subject.

Top of page

Using existing ST_Geometry tables

ArcSDE can successfully use tables containing ST_Geometry columns created using SQL (sometimes referred to as third-party tables) as long as the tables meet certain prerequisites:

It must be owned by the user registering the table.
It must have a single ST_Geometry column.
It must have no other columns of a user-defined type.
It must have a single type of geometry (points, lines, or polygons). The geometries can be multipart.
Geometry should be valid; otherwise, accessing these geometries may have unexpected results.

Performing registration of third-party tables containing ST_Geometry columns

The ArcSDE administration command sdelayer –o register registers a spatial table as a feature class. To register tables containing an ST_Geometry column, the prerequisites described in the previous section must be met.

The tables you register may be empty or they may already contain data.

Registering tables that already contain data
When you register a table that already contains data, the data has a spatial reference system assigned to it. That means when you register the table with ArcSDE, you do not have to specify the spatial reference ID (SRID) with the –R option. ArcSDE checks the defined SRID of the first record in the table and uses that.

Be aware, though, that the SRID must exist in the ST_SPATIAL_REFERENCES table for it to be accepted. If the spatial reference system you need is not already in the ST_SPATIAL_REFERENCES table, the recommended way to get it there is to open ArcCatalog, connect to the geodatabase, and create a new feature class that uses the spatial reference system you want and ST_Geometry storage. When you do this, the ST_SPATIAL_REFERENCES table is populated with the SRID you need and ArcCatalog calculates the x,y, z-, and m- offsets and units and the layer extent for you. It is also possible to use SQL to insert the spatial reference, but when you do so, you must be sure to calculate the correct x,y, z-, and m- offsets and units yourself.

In the following example, a table, TOWERS, containing records of point geometries (–e p) in a spatial column, SHAPE, is registered. Since the spatial reference is already known, it does not have to be specified when registering the table with sdelayer. Using the –C option, a unique, not-NULL column, FID, is added to the table to be used as a row ID column.

sdelayer –o register –l towers,shape –e p –C fid,SDE –u brooke –p pwd19

For tables that contain a large number of records, registration may take less time if you register the row ID column as user maintained.

sdelayer –o register –l towers,shape –e p –C fid,USER –u brooke –p pwd19

However, if you register the feature identifier column as user maintained and subsequently register the feature class with the geodatabase, ArcGIS will add an additional feature identifier column, object_ID. ArcGIS will maintain the values of this column. If the table contains a large number of records, adding this additional object_ID column may take some time.

Registering spatial tables that do not contain data
If the table does not contain data and does not have an assigned SRID, you have to specify an SRID when the table is registered. This is done with the –R option. The SRID specified at this time must already be present in the SPATIAL_REFERENCES table in the geodatabase and corresponds to the SRID defined by ArcSDE.

As mentioned in the previous section, to populate the SPATIAL_REFERENCES table with the spatial reference system you want to use, open ArcCatalog, connect to the geodatabase, and create a new feature class that uses the spatial reference system you want and ST_Geometry storage. Then query the SPATIAL_REFERENCES system to discover the SRID that was assigned to the spatial reference system you specified for the feature class. Use this SRID when registering the spatial table.

In the following example, an empty table, DRAINAGE, is registered with ArcSDE. The table already contains an integer, unique, not-NULL column, ID, that will be used for the row ID column. The SRID is specified with the –R option.

sdelayer –o register –l drainage,line –e l+ –C id,SDE –R 6 –u hortence –p topsecret

If the table does not contain data and does not have an assigned SRID, and you do not specify one when the table is registered with ArcSDE, it will be assigned the default SRID value of 0. This SRID exists for sample and documentation purposes only and is not recommended for use with production data. The specifications for this SRID are as follows:

FalseX	-400
FalseY	-400
XYUnits	1000000000
FalseZ	-100000
ZUnits	10000
FalseM	-100000
MUnits	10000
XYCluster_Tol	0.000000008983153
ZCluster_Tol	.001
MCluster_Tol	.001
Projection	GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]], PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]
Auth_srid	4326
Auth_name	EPSG

For further information on using the sdelayer command, consult the ArcSDE Administration Command Reference provided with the ArcSDE component of ArcGIS Server Enterprise.

The sdelayer command only adds the table to the ArcSDE system tables. To be able to use ArcGIS Desktop geodatabase functionality such as topology, versioning, and networks, you must also register the table with the geodatabase. See Registering tables to be used by ArcGIS Desktop for more information on registering tables with the geodatabase.

Converting geometry to the spatial type for Oracle

The following is a description of spatial data formats and how you can convert between them and the spatial type for Oracle.

OGC well-known text representation

The spatial type has several functions that generate geometries from text descriptions. The functions include

ST_GeomFromText—Creates an ST_Geometry from a text representation of any geometry type
ST_PointFromText—Creates an ST_Point from a point text representation
ST_LineFromText—Creates an ST_LineString from a linestring text representation
ST_PolyFromText—Creates an ST_Polygon from a polygon text representation
ST_MPointFromText—Creates an ST_MultiPoint from a multipoint representation
ST_MLineFromText—Creates an ST_MultiLineString from a multilinestring representation
ST_MPolyFromText—Creates an ST_MultiPolygon from a multipolygon representation

The text representation is an ASCII text-formatted string that allows geometry to be exchanged in ASCII text form. You can use these functions in a third- or fourth-generation language (3GL or 4GL) program because they don't require the definition of any special program structures.

The ST_AsText function converts an existing geometry into a text representation.

Using the well-known text representation in a C program
The well-known text representation of geometry can be incorporated into a C program. The structure for such an implementation is defined below. The notation {}* indicates zero or more repetitions of the tokens within the braces. The braces do not appear in the output token list.

<Geometry Tagged Text> :=
| <Point Tagged Text> 
| <LineString Tagged Text>
| <Polygon Tagged Text>
| <MultiPoint Tagged Text>
| <MultiLineString Tagged Text>
| <MultiPolygon Tagged Text> 
<Point Tagged Text> := 
POINT <Point Text> 
<LineString Tagged Text> := 
LINESTRING <LineString Text> 
<Polygon Tagged Text> := 
POLYGON <Polygon Text> 
<MultiPoint Tagged Text> :=  
MULTIPOINT <Multipoint Text> 
<MultiLineString Tagged Text> := 
MULTILINESTRING <MultiLineString Text> 
<MultiPolygon Tagged Text> := 
MULTIPOLYGON <MultiPolygon Text> 
<Point Text> := EMPTY 
|    <Point> 
| Z  <PointZ> 
| M  <PointM>
| ZM <PointZM>
<Point> :=  <x>  <y> 
<x> := double precision literal
<y> := double precision literal
<PointZ> :=  <x>  <y>  <z> 	
<x> := double precision literal
<y> := double precision literal
<z> := double precision literal
<PointM> :=  <x>  <y>  <m> 
<x> := double precision literal
<y> := double precision literal
<m> := double precision literal
<PointZM> :=  <x>  <y>  <z>  <m> 
<x> := double precision literal
<y> := double precision literal
<z> := double precision literal
<m> := double precision literal
<LineString Text> := EMPTY
|    ( <Point Text >   {,  <Point Text> }*  )
| Z  ( <PointZ Text >  {,  <PointZ Text> }*  )
| M  ( <PointM Text >  {,  <PointM Text> }*  )
| ZM ( <PointZM Text > {,  <PointZM Text> }*  )
<Polygon Text> := EMPTY 
| ( <LineString Text > {,< LineString Text > }*)
<Multipoint Text> := EMPTY 
| ( <Point Text >   {,  <Point Text > }*  )
<MultiLineString Text> := EMPTY 
| ( <LineString Text > {,< LineString Text>}*  )
<MultiPolygon Text> := EMPTY 
| ( < Polygon Text > {,  < Polygon Text > }*  )

Using well-known text representation in a SQL editor
Since the well-known text representation is text, it can conveniently be typed into a SQL script or directly into a SQL editor. The text is converted to and from a geometry by a function. Functions that convert text to geometry have the following syntax:

function ('<text description>',<SRID>)

For example:

ST_PointFromText('point zm(10.01 20.04 3.2 9.5)', 1)

The spatial reference identifier, SRID—the primary key to the SPATIAL_REFERENCES table—identifies the possible spatial reference systems within an Oracle instance. An SRID is assigned to a spatial column when it is created. Before a geometry can be inserted into a spatial column, its SRID must match the SRID of the spatial column.

The '<text description>' is made up of three basic components enclosed in single quotes:

'<geometry type> [coordinate type] [coordinate list]'

The geometry type is defined as one of the following: point, linestring, polygon, multipoint, multilinestring, or multipolygon.

The coordinate type specifies whether or not the geometry has z-coordinates and/or measures. Leave this argument blank if the geometry has neither; otherwise, set the coordinate type to Z for geometries containing z-coordinates, M for geometries with measures, and ZM for geometries that have both.

The coordinate list defines the double-precision vertices of the geometry. Coordinate lists are comma-delimited and enclosed by parentheses. Geometries having multiple components require sets of parentheses to enclose each component part. If the geometry is empty, the EMPTY keyword replaces the coordinates.

The following examples provide a complete list of all possible permutations of the text description portion of the text representation:

Geometry type	Text description	Comment
ST_Point	'point empty'	empty point
ST_Point	'point z empty'	empty point with z-coordinate
ST_Point	'point m empty'	empty point with measure
ST_Point	'point zm empty'	empty point with z-coordinate and measure
ST_Point	'point ( 10.05 10.28 )'	point
ST_Point	'point z( 10.05 10.28 2.51 )'	point with z-coordinate
ST_Point	'point m( 10.05 10.28 4.72 )'	point with measure
ST_Point	'point zm(10.05 10.28 2.51 4.72 )'	point with z-coordinate and measure
ST_LineString	'linestring empty'	empty linestring
ST_LineString	'linestring z empty'	empty linestring with z-coordinates
ST_LineString	'linestring m empty'	empty linestring with measures
ST_LineString	'linestring zm empty'	empty linestring with z-coordinates and measures
ST_LineString	'linestring (10.05 10.28 , 20.95 20.89 )'	linestring
ST_LineString	'linestring z(10.05 10.28 3.09, 20.95 31.98 4.72, 21.98 29.80 3.51 )'	linestring with z-coordinates
ST_LineString	'linestring m(10.05 10.28 5.84, 20.95 31.98 9.01, 21.98 29.80 12.84 )'	linestring with measures
ST_LineString	'linestring zm(10.05 10.28 3.09 5.84, 20.95 31.98 4.72 9.01, 21.98 29.80 3.51 12.84)'	linestring with z-coordinates and measures
ST_Polygon	'polygon empty'	empty polygon
ST_Polygon	'polygon z empty'	empty polygon with z-coordinates
ST_Polygon	'polygon m empty'	empty polygon with measures
ST_Polygon	'polygon zm empty'	empty polygon with z-coordinates and measures
ST_Polygon	'polygon ((10 10, 10 20, 20 20, 20 15, 10 10))'	polygon
ST_Polygon	'polygon z((10 10 3, 10 20 3, 20 20 3, 20 15 4, 10 10 3))'	polygon with z-coordinates
ST_Polygon	'polygon m((10 10 8, 10 20 9, 20 20 9, 20 15 9, 10 10 8 ))'	polygon with measures
ST_Polygon	'polygon zm((10 10 3 8, 10 20 3 9, 20 20 3 9, 20 15 4 9, 10 10 3 8 ))'	polygon with z-coordinates and measures
ST_MultiPoint	'multipoint empty'	empty multipoint
ST_MultiPoint	'multipoint z empty'	empty multipoint with z-coordinates
ST_MultiPoint	'multipoint m empty'	empty multipoint with measures
ST_MultiPoint	'multipoint zm empty'	empty multipoint with z-coordinates and measures
ST_MultiPoint	'multipoint (10 10, 20 20)'	multipoint with two points
ST_MultiPoint	'multipoint z(10 10 2, 20 20 3)'	multipoint with z-coordinates
ST_MultiPoint	'multipoint m(10 10 4, 20 20 5)'	multipoint with measures
ST_MultiPoint	'multipoint zm(10 10 2 4, 20 20 3 5)'	multipoint with z-coordinates and measures
ST_MultiLineString	'multilinestring empty'	empty multilinestring
ST_MultiLineString	'multilinestring z empty'	empty multilinestring with z-coordinates
ST_MultiLineString	'multilinestring m empty'	empty multilinestring with measures
ST_MultiLineString	'multilinestring zm empty'	empty multilinestring with z-coordinates and measures
ST_MultiLineString	'multilinestring ((10.05 10.28 , 20.95 20.89 ),( 20.95 20.89, 31.92 21.45))'	multilinestring
ST_MultiLineString	'multilinestring z((10.05 10.28 3.4, 20.95 20.89 4.5),( 20.95 20.89 4.5, 31.92 21.45 3.6))'	multilinestring with z-coordinates
ST_MultiLineString	'multilinestring m((10.05 10.28 8.4, 20.95 20.89 9.5), (20.95 20.89 9.5, 31.92 21.45 8.6))'	multilinestring with measures
ST_MultiLineString	'multilinestring zm((10.05 10.28 3.4 8.4, 20.95 20.89 4.5 9.5), (20.95 20.89 4.5 9.5, 31.92 21.45 3.6 8.6))'	multilinestring with z-coordinates and measures
ST_MultiPolygon	'multipolygon empty'	empty multipolygon
ST_MultiPolygon	'multipolygon z empty'	empty multipolygon with z-coordinates
ST_MultiPolygon	'multipolygon m empty'	empty multipolygon with measures
ST_MultiPolygon	'multipolygon zm empty'	empty
ST_MultiPolygon	'multipolygon (((10 10, 10 20, 20 20, 20 15 , 10 10), (50 40, 50 50, 60 50, 60 40, 50 40)))'	multipolygon
ST_MultiPolygon	'multipolygon z(((10 10 7, 10 20 8, 20 20 7, 20 15 5, 10 10 7), (50 40 6, 50 50 6, 60 50 5, 60 40 6, 50 40 6)))'	multipolygon with z-coordinates
ST_MultiPolygon	'multipolygon m(((10 10 2, 10 20 3, 20 20 4, 20 15 5, 10 10 2), (50 40 7, 50 50 3, 60 50 4, 60 40 5, 50 40 7)))'	multipolygon with measures
ST_MultiPolygon	'multipolygon zm(((10 10 7 2, 10 20 8 3, 20 20 7 4, 20 15 5 5, 10 10 7 2), (50 40 6 7, 50 50 6 3, 60 50 5 4, 60 40 6 5, 50 40 6 7)))'	multipolygon with z-coordinates and measures

OGC well-known binary representation

The well-known binary representation for OGC geometry (WKBGeometry) provides a portable representation of a geometry value as a contiguous stream of bytes. It permits geometry values to be exchanged between an ODBC client and a SQL database in binary form.

The well-known binary representation for geometry is obtained by serializing a geometry instance as a sequence of numeric types drawn from the set {Unsigned Integer, Double}, then serializing each numeric type as a sequence of bytes using one of two well-defined standard binary representations for numeric types—NDR and XDR. The specific binary encoding used for a geometry byte stream is described by a one-byte tag that precedes the serialized bytes. The only difference between the two encodings of geometry is byte order. The XDR encoding is big endian, while the NDR encoding is little endian.

The spatial type for Oracle has several functions that generate geometries from well known binary representations. The functions include

ST_GeomFromWKB—Creates an ST_Geometry from a WKB representation of any geometry type
ST_PointFromWKB—Creates an ST_Point from a point WKB representation
ST_LineFromWKB—Creates an ST_LineString from a linestring WKB representation
ST_PolyFromWKB—Creates an ST_Polygon from a polygon WKB representation
ST_MPointFromWKB—Creates an ST_MultiPoint from a multipoint WKB representation
ST_MLineFromWKB—Creates an ST_MultiLineString from a multilinestring WKB representation
ST_MPolyFromWKB—Creates an ST_MultiPolygon from a multipolygon WKB representation

The well-known binary representation is a contiguous stream of bytes. It permits geometry to be exchanged between an ODBC client and a SQL database in binary form. Because these geometry functions require the definition of C structures to map the binary representation, they are intended for use within a 3GL program and aren't suited to a 4GL environment.

The ST_AsBinary function converts an existing geometry value into well-known binary representation.

For more information on the well-known binary representation, see The OGC Well-Known Binary representation for geometry.

Where to find OGC documentation

The OGC is a nonprofit organization working to formalize open interface specifications for geoprocessing products and services. Its Web site—www.opengeospatial.org—will provide you with information on adopted and proposed specifications, discussion papers on technology issues, and white papers on open standards in general. OGC also maintains another site for its OGCNetwork—www.ogcnetwork.org. OGCNetwork is an online forum that brings together OGC members and the public to engage in discussion, testing, and demonstration of open geospatial specification-based capabilities. Additional OGC documentation sources include the following:

Simple features specification

www.opengeospatial.org/docs/99-049.pdf

Conformance testing

www.opengeospatial.org/resource/testing?highlight=testing

OGC conformance test scripts

www.opengeospatial.org/resources/?page=testing&view=sfsql

Where to find Oracle documentation

For Oracle documentation, you can visit Oracle's Web site at www.oracle.com/index.html for general information about Oracle products and services. To obtain documentation for Oracle 10g database software, try the Oracle Database Documentation Library, found at www.oracle.com/pls/db102/portal.portal_db?selected=1 (requires a login).

Working with Oracle data types and extensions

Application Developers Guide—Fundamentals
Application Developer's Guide—Large Objects
Application Developer's Guide—Object-Relational Features
Data Cartridge Developer's Guide
Oracle Call Interface Programmer's Guide

LOB Performance Guidelines

http://www.oracle.com/technology/products/database/application_development/pdf/lob_performance_guidelines.pdf

Errors/Troubleshooting

http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=1

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10744/toc.htm

http://forums.oracle.com/forums

ST_Geometry storage in Oracle