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

All possible permutations of the text description portion of the text representation

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
NoteNote:

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.


11/18/2013