ST_Geometry generated from well-known text and well-known binary data
Converting geometry to the ST_Geometry type in Oracle
The following is a description of spatial data formats and how to convert between them and the spatial type for Oracle.
OGC well-known text representation
The well-known text (WKT) representation is a formatted ASCII text 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 do not require the definition of any special program structures.
The spatial type has several functions that generate geometries from text descriptions. The functions include the following:
- ST_GeomFromText—Creates an ST_Geometry from a WKT representation of any geometry type
- ST_PointFromText—Creates an ST_Point from a point WKT representation
- ST_LineFromText—Creates an ST_LineString from a linestring WKT representation
- ST_PolyFromText—Creates an ST_Polygon from a polygon WKT representation
- ST_MPointFromText—Creates an ST_MultiPoint from a multipoint WKT representation
- ST_MLineFromText—Creates an ST_MultiLineString from a multilinestring WKT representation
- ST_MPolyFromText—Creates an ST_MultiPolygon from a multipolygon WKT representation
The ST_AsText function converts an existing geometry into a WKT representation.
Using the well-known text representation in a C program
The WKT representation of geometry can be incorporated into C programs. 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 ST_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 geometry is part of the Open Geospatial Consortium's (OGC) Simple Features specification that implements a simple storage model for point, line, and polygon features using x,y coordinates. It 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 database in binary form. It is not compressed.
The ST_Geometry type for Oracle has several functions that generate geometries from well-known binary (WKB) representations. The functions include the following:
- 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
Note:These geometry functions require the definition of C structures to map the binary representation. They are intended for use within a 3GL program and are not 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.