ST_AsText method

geometry-expression.ST_AsText([ format])

Name	Type	Description
format	VARCHAR(128)	A string defining the output text format to use when converting the geometry-expression to a text representation. If not specified, the st_geometry_astext_format option is used to choose the text representation. See st_geometry_astext_format option.

LONG VARCHAR Returns the text representation of the geometry-expression.

The ST_AsText method returns a text string representing the geometry. A number of different text formats are supported (with associated options) and the desired format is selected using the optional format parameter. If the format parameter is not specified, the st_geometry_astext_format option is used to select the output format to use. See st_geometry_astext_format option.

The format string defines an output format and parameters to the format. The format string has one of the following formats:

format-name

format-name(parameter1=value1;parameter2=value2;...)

parameter1=value1;parameter2=value2;...

The first format specifies the format name and no parameters. All format parameters use their default values. The second format specifies the format name and a list of named parameter values. Parameters that are not supplied use their default values. The last format specifies only parameter values, and the format name defaults to 'WKT'.

The following format names may be used:

WKT The Well-Known Text format defined by SQL/MM and the OGC.
EWKT The Extended Well Known Text format. This format includes the geometry's SRID as a prefix.
GML The Geography Markup Language format defined by ISO 19136 and the OGC.
KML Keyhole Markup Language format defined by OGC.
GeoJSON The GeoJSON format uses JavaScript Object Notation (JSON) as defined by http://geojson.org/geojson-spec.html.
SVG The Scalable Vector Graphics (SVG) 1.1 format defined by the World Wide Web Consortium (W3C).

The following format parameters can be specified:

Format Name	Parameter Name	Default Value	Allowed Values	Description
WKT	Version	1.2	1.1 The WKT defined by OGC SFS 1.1. This format does not contain Z and M values. If the geometry contains Z or M values, they are removed in the output. 1.2 The WKT defined by OGC SFS 1.2. This matches version 1.1 on 2D data and extends the format to support Z and M values. PostGIS The WKT format used by some other vendors; Z and M values are included in a fashion that does not match OGC 1.2.	The version parameter controls the version of the WKT specification used.
GML	Version	3	2 Version 2 of the GML specification. 3 Version 3.2 of the GML specification	The version parameter controls the version of the GML specification used.
GML	Namespace	none	local Provides a default namespace attribute for the given element (in this case Point) and its sub elements. global Provides a dedicated ("gml") prefix for the given element and its sub elements. This is useful when the query is used within an aggregate operation, such that, some top level element defines the namespace for the "gml" prefix. none Provides no namespace or prefix for the given element (in this case Point) and its sub elements	The namespace parameter specifies the output format convention for namespace.
GML	SRSNameFormat	short	short Uses a short format for the spatial reference system name, for example EPSG:4326 long Uses a long format for the spatial reference system name, for example urn:x-ogc:def:crs:EPSG:4326. none Spatial reference system name attribute is not included for the geometry.	The SRSNameFormat parameter specifies the format for the srsName attribute.
GML	SRSDimension	No	Yes or No	The SRSDimension parameter specifies the number of coordinate values for the given geometry. This only applies to GML(version=3).
GML	SRSFillAll	No	Yes or No	The SRSFillAll parameter specifies whether or not SRS attributes should be propagated to child geometry elements. As an example a MultiGeometry or MultiPolygon would propagate the attributes to its child geometries.
GML	UseDeprecated	No	Yes or No	The UseDeprecated parameter only applies to GML(version=3). It is used to output older GML representations where possible. As an example a Surface may be output as a Polygon if the geometry contains no CircularStrings.
GML	Attribute	Automatically generated optional attributes	One or more attributes may be specified for the top level geometry element only	Any legal XML attributes may be specified.
GML	SubElement	Automatically generated GML sub elements	One or more sub elements may be specified for the top level geometry element only	Any legal XML elements may be specified.
KML	Version	2	2	KML version 2.2 is supported.
KML	Attribute	Automatically generated optional attributes	One or more attributes may be specified for the top level geometry element only	Any legal XML attributes may be specified.
KML	Namespace	none	local Provides the default namespace attribute http://www.opengis.net/kml/2.2 for the given geometry element (in this case Point) and its sub elements. global Provides a dedicated ("kml") prefix for the given element and its sub elements. This is useful when the query is used within an aggregate operation, such that, some top level element defines the namespace for the "kml" prefix. none Provides no namespace or prefix for the given element (in this case Point) and its sub elements	The namespace parameter specifies the output format convention for namespace.
KML	SubElement	Automatically generated KML sub elements	One or more sub elements may be specified for the top level geometry element only	Any legal XML elements may be specified. As an example extrude, tessellate and altitudeMode elements may be specified.
GeoJSON	Version	1	1	The version of the GeoJSON specification to follow. At present, only 1.0 is supported.
SVG	Approximate	Yes	Yes or No	The Approximate parameter specifies whether or not to reduce the size of the output SVG document with a slight reduction in visible detail. The SVG data is approximated by not including points which are within the line width of the last point. With multiple megabyte geometries this can result in compression rates of 80% or more.
SVG	Attribute	Automatically generated optional attributes	One or more SVG attributes that can be applied to SVG shape elements	By default, optional SVG shape attributes such as fill, stroke and stroke-width are generated. If the Attributes parameter is specified, then no optional SVG shape attributes are generated, and the Attribute value is used instead. Ignored if PathDataOnly=Yes is specified. The maximum length of the Attribute value is about 1000 bytes.
SVG	DecimalDigits	Based on the number of decimal digits in the spatial reference system's snap to grid grid-size. The maximum default value is 5 and the minimum is 0.	integer	The DecimalDigits parameter limits the number of digits after the decimal place for coordinates generated in the SVG output. Specifying a negative number of digits indicates that the full precision of coordinates should be included in the SVG output.
SVG	PathDataOnly	No (a complete SVG document is generated)	Yes or No	The PathDataOnly parameter specifies whether or not only data for the SVG Path Element should be generated. The PathDataOnly example below demonstrates how PathDataOnly=Yes can be used to build a complete SVG document that can be displayed. By default a complete SVG document is generated. The path data returned by PathDataOnly=Yes can be used to build more flexible SVG documents containing other elements, such as text.
SVG	RandomFill	Yes	Yes or No	The RandomFill parameter specifies whether or not polygons should be filled by a randomly generated color. The sequence of colors used does not follow a well-defined sequence, and typically changes each time SVG output is generated. No indicates that only an outline of each polygon is drawn. The RandomFill parameter is ignored if the Attribute or PathDataOnly=Yes parameter is specified.
SVG	Relative	Yes	Yes or No	The Relative parameter specifies if coordinates should be output in relative (offset) or absolute formats. Relative coordinate data is typically more compact than absolute coordinate data.

Note

When converting a geometry value to VARCHAR or NVARCHAR, the server uses the ST_AsText method. The st_geometry_astext_format option defines the format that is used for the conversion. See st_geometry_astext_format option.

Note

By default, ST_AsText uses the original format for a geometry, if it is available. Otherwise, the internal format is used. For more information about internal and original formats, see STORAGE FORMAT clause, CREATE SPATIAL REFERENCE SYSTEM statement.

SQL/MM (ISO/IEC 13249-3: 2006) 5.1.35

Assuming that the st_geometry_astext_format option has the value 'WKT', the following returns the result Point ZM (1 2 3 4). See st_geometry_astext_format option.

SELECT NEW ST_Point( 1.0, 2.0, 3.0, 4.0, 4326 ).ST_AsText()

Assuming that the st_geometry_astext_format option has the value 'WKT', the following returns the result Point ZM (1 2 3 4). The ST_AsText method is implicitly invoked when converting geometries to VARCHAR or NVARCHAR types. See st_geometry_astext_format option.

SELECT CAST( NEW ST_Point( 1.0, 2.0, 3.0, 4.0, 4326 ) as long varchar)

The following returns the result Point (1 2). The Z and M values are not output because they are not supported in version 1.1.0 of the OGC specification for WKT.

SELECT NEW ST_Point( 1.0, 2.0, 3.0, 4.0, 4326 ).ST_AsText('WKT(Version=1.1)')

The following returns the result SRID=4326;Point ZM (1 2 3 4). The SRID is included in the result as a prefix.

SELECT NEW ST_Point( 1.0, 2.0, 3.0, 4.0, 4326 ).ST_AsText('EWKT')

The following example returns the result <Point srsName="EPSG:4326"><pos>1 2 3 4</pos></Point>.

SELECT NEW ST_Point( 1.0, 2.0, 3.0, 4.0, 4326 ).ST_AsText('GML')

The following returns '{"type":"Point", "coordinates":[1,2]} '.

SELECT NEW ST_Point( 1.0, 2.0, 3.0, 4.0, 4326 ).ST_AsText('GeoJSON')

The following returns a complete SVG document with polygons filled with random colors.

SELECT NEW ST_Polygon( 'Polygon(( 0 0, 0 20, 60 10, 0 0 ))' )
		    .ST_AsText( 'SVG' )

ST_AsText method

Syntax

Parameters

Returns

Remarks

Note

Note

See also

Standards and compatibility

Example