User's Guide and Reference

The OGC well-known text representations

DB2 Spatial Extender has several functions that generate geometries from text descriptions:

ST_GeomFromText

Creates a geometry from a text representation of any geometry type.

ST_PointFromText

Creates a point from a point text representation.

ST_LineFromText

Creates a linestring from a linestring text representation.

ST_PolyFromText

Creates a polygon from a polygon text representation.

ST_MPointFromText

Creates a multipoint from a multipoint text representations.

ST_MLineFromText

Creates a multilinestring from a multilinestring text representation.

ST_MPolyFromText

Creates a multipolygon from a multipolygon text representation.

The text representation is an ASCII text format 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 definitions of any special program structures. The ST_AsText function converts an existing geometry into a text representation.

Each geometry type has a well-known text representation, which can be used both to construct new instances of the type and to convert existing instances to textual form for alphanumeric display.

The well-known text representation of a geometry is defined as follows: the notation {}* denotes 0 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 > }*  )
 

The basic function syntax is:

function (<text description>,<SRID>)
 

The SRID, the spatial reference identifier, and primary key to the SPATIAL_REFERENCES table, identifies the geometry's spatial reference system that is stored in the SPATIAL_REFERENCES table. Before a geometry is 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 that are enclosed in single quotation marks, for example:

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

where:

geometry type

Is one of the following: point, linestring, polygon, multipoint, multilinestring, or multipolygon.

coordinate type

Specifies whether or not the geometry has Z coordinates 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.

coordinate list

Defines the vertices of the geometry. Coordinate lists are comma delimited and enclosed by parentheses. Geometries with multiple components require sets of parentheses to enclose each component part. If the geometry is empty, the EMPTY keyword replaces the coordinate.

Table 65 shows a complete list of examples of all possible text representations.

Table 65. Geometry types and their text representations

Geometry type	Text Description	Comment
point	point empty	empty point
point	point z empty	empty point with z coordinate
point	point m empty	empty point with measure
point	point zm empty	empty point with z coordinate and measure
point	point ( 10.05 10.28 )	point
point	point z ( 10.05 10.28 2.51 )	point with z coordinate
point	point m ( 10.05 10.28 4.72 )	point with measure
point	point zm ( 10.05 10.28 2.51 4.72 )	point with z coordinate and measure
linestring	linestring empty	empty linestring
linestring	linestring z empty	empty linestring with z coordinates
linestring	linestring m empty	empty linestring with measures
linestring	linestring zm empty	empty linestring with z coordinates and measures
linestring	linestring ( 10.05 10.28 , 20.95 20.89 )	linestring
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
linestring	linestring m ( 10.05 10.28 5.84, 20.95 31.98 9.01, 21.98 29.80 12.84 )	linestring with measures
linestring	linestring zm ( )	linestring with z coordinates and measures
polygon	polygon empty	empty polygon
polygon	polygon z empty	empty polygon with z coordinates
polygon	polygon m empty	empty polygon with measures
polygon	polygon zm empty	empty polygon with z coordinates and measures
polygon	polygon (( 10 10, 10 20, 20 20, 20 15, 10 10))	polygon
polygon	polygon z (( ))	polygon with z coordinates
polygon	polygon m (( ))	polygon with measures
polygon	polygon zm (( ))	polygon with z coordinates and measures
multipoint	multipoint empty	empty multipoint
multipoint	multipoint z empty	empty multipoint with z coordinates
multipoint	multipoint m empty	empty multipoint with measures
multipoint	multipoint zm empty	empty multipoint with z coordinates with measures
multipoint	multipoint empty	empty multipoint
multipoint	multipoint (10 10, 20 20)	multipoint with two points
multipoint	multipoint z (10 10 2, 20 20 3)	multipoint with z coordinates
multipoint	multipoint m (10 10 4, 20 20 5)	multipoint with measures
multipoint	multipoint zm (10 10 2 4, 20 20 3 5)	multipoint with z coordinates and measures
multilinestring	multilinestring empty	empty multilinestring
multilinestring	multilinestring z empty	empty multilinestring with z coordinates
multilinestring	multilinestring m empty	empty multilinestring with measures
multilinestring	multilinestring zm empty	empty multilinestring with z coordinates and measures
multilinestring	multilinestring (( ))	multilinestring
multilinestring	multilinestring z (( ))	multilinestring with z coordinates
multilinestring	multilinestring m (( ))	multilinestring with measures
multilinestring	multilinestring zm (( ))	multilinestring with z coordinates and measures
multipolygon	multipolygon empty	empty multipolygon
multipolygon	multipolygon z empty	empty multipolygon with z coordinates
multipolygon	multipolygon m empty	empty multipolygon with measures
multipolygon	multipolygon z	empty multipolygon with z coordinates and measures
multipolygon	multipolygon ((( )))	multipolygon
multipolygon	multipolygon z ((( )))	multipolygon with z coordinates
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
multipolygon	multipolygon zm ((( )))	multipolygon with z coordinates and measures