Conversion Functions

﻿
﻿ conversion functions transform a specified operand to a different data type.
ST_ASBINARY
Alias of ST_ASWKB.  
Returns the well-known binary (WKB) representation of the specified geography. 
Syntax
SQL
ST_ASBINARY(geo [, XDR_or_NDR ] )
ST_ASBINARY(geo [, XDR_or_NDR ] )
﻿
Argument
Data Type
Description
geo
POINT, LINESTRING, or POLYGON
The geospatial object used to calculate the dimensions. 
XDR_or_NDR
CHAR
Optional. This argument determines whether the resulting WKB is in little endian (NDR) or big endian (XDR) format. 
If you specify this argument, it must be either XDR or NDR. Otherwise, it defaults to NDR.
Example
SQL
SELECT ST_ASBINARY(ST_POINT(2, 2), 'NDR');
SELECT ST_ASBINARY(ST_POINT(2, 2), 'NDR');
﻿
Output: 010100000000000000000000400000000000000040
ST_ASGEOJSON
Returns the GeoJSON representation of the specified geography using the IETF standards. 
SQL
 ST_ASGEOJSON( geo [, planar_conversion ] )
 ST_ASGEOJSON( geo [, planar_conversion ] )
﻿
Argument
Data Type
Description
geo
POINT, LINESTRING, or POLYGON
The geospatial object used to create a GeoJSON representation.  
Note that the GeoJSON specification has certain requirements on what constitutes a LINESTRING and POLYGON . 
In keeping with this specification, a POLYGON instance that represents a LINESTRING results in a LINESTRING GeoJSON instance. 
Similarly, a POLYGON instance that represents a  POINT results in a POINT GeoJSON instance. 
A LINESTRING instance that represents a POINT results in a POINT GeoJSON instance. 
Unlike the GeoJSON specification, a specified POLYGON does not need to follow the Right-Hand Rule. If a specified POLYGON contains any holes that are not valid POLYGON objects, then the function omits those holes in the resulting GeoJSON instance.
planar_conversion
BOOLEAN
Optional. A Boolean value that determines if the geodesic edges of the specified geography are converted to planar representation before the function applies the GeoJSON format. IETF standards state that all GeoJSON objects are to be interpreted as explicitly planar. This argument allows preservation of the original geodesic shape.
If planar_conversion is specified TRUE, points will be added such that the resulting GeoJSON represents a planar shape within 10m of the original geospatial object. Additionally, if the original geospatial object crossed the international dateline, it will be split at the point where it crosses. This will generate a GeoJSON object of either MultiLineString or MultiPolygon  type, depending on the original geospatial object's type.
If you do not specify this argument, the value defaults to FALSE.
Examples
This example converts a POINT value into a GeoJSON representation. 
SQL
SELECT ST_ASGEOJSON(ST_POINT(2, 2), false);
SELECT ST_ASGEOJSON(ST_POINT(2, 2), false);
﻿
Output: 
Text
{"type":"Point","coordinates":[2,2]}
{"type":"Point","coordinates":[2,2]}
﻿
This example converts an empty POINT value. 
SQL
SELECT ST_ASGEOJSON(ST_POINTFROMTEXT('POINT EMPTY'), false);
SELECT ST_ASGEOJSON(ST_POINTFROMTEXT('POINT EMPTY'), false);
﻿
Output: 
Text
{"type":"Point","coordinates":[]}
{"type":"Point","coordinates":[]}
﻿
This example converts a LINESTRING value into a GeoJSON representation. 
SQL
SELECT ST_ASGEOJSON(ST_LINESTRING(ST_POINT(1.234, 2),ST_POINT(3.456, 5));
SELECT ST_ASGEOJSON(ST_LINESTRING(ST_POINT(1.234, 2),ST_POINT(3.456, 5));
﻿
Output: 
Text
{"type":"LineString","coordinates":[[1.234,2],[3.456,5]]}
{"type":"LineString","coordinates":[[1.234,2],[3.456,5]]}
﻿
This example again converts a LINESTRING value. 
SQL
SELECT ST_ASGEOJSON(ST_LINESTRING(ARRAY[ST_POINT(1.234, 2)]);
SELECT ST_ASGEOJSON(ST_LINESTRING(ARRAY[ST_POINT(1.234, 2)]);
﻿
Output: 
Text
{"type":"Point","coordinates":[1.234,2]}
{"type":"Point","coordinates":[1.234,2]}
﻿
This example converts an empty LINESTRING value into a GeoJSON representation. 
SQL
SELECT ST_ASGEOJSON(ST_LINEFROMTEXT('LINESTRING EMPTY'), false);
SELECT ST_ASGEOJSON(ST_LINEFROMTEXT('LINESTRING EMPTY'), false);
﻿
Output: 
Text
{"type":"LineString","coordinates":[]}
{"type":"LineString","coordinates":[]}
﻿
This example converts a POLYGON value into a GeoJSON representation. 
SQL
SELECT ST_ASGEOJSON(
    ST_POLYGON(
        ARRAY [ST_POINT(1.234, 2),ST_POINT(3.456, 5),ST_POINT(5.678,         2),ST_POINT(1.234, 2)],
        ARRAY [ARRAY[ST_POINT(2,2.5),ST_POINT(2.1,2.6),ST_POINT(2.2,2.5),ST_POINT(2,2.5)],
        ARRAY [ST_POINT(3,2.5),ST_POINT(3.1,2.6),ST_POINT(3.2,2.5),ST_POINT(3,2.5)]]), false);
SELECT ST_ASGEOJSON(
    ST_POLYGON(
        ARRAY [ST_POINT(1.234, 2),ST_POINT(3.456, 5),ST_POINT(5.678,         2),ST_POINT(1.234, 2)],
        ARRAY [ARRAY[ST_POINT(2,2.5),ST_POINT(2.1,2.6),ST_POINT(2.2,2.5),ST_POINT(2,2.5)],
        ARRAY [ST_POINT(3,2.5),ST_POINT(3.1,2.6),ST_POINT(3.2,2.5),ST_POINT(3,2.5)]]), false);
﻿
Output: 
Text
{"type":"Polygon","coordinates":[[[1.234,2],[3.456,5],[5.678,2],[1.234,2]],[[2,2.5],[2.1,2.6],[2.2,2.5],[2,2.5]],[[3,2.5],[3.1,2.6],[3.2,2.5],[3,2.5]]]}
{"type":"Polygon","coordinates":[[[1.234,2],[3.456,5],[5.678,2],[1.234,2]],[[2,2.5],[2.1,2.6],[2.2,2.5],[2,2.5]],[[3,2.5],[3.1,2.6],[3.2,2.5],[3,2.5]]]}
﻿
This example again converts a POLYGON value. 
SQL
SELECT ST_ASGEOJSON(
    ST_POLYGON(
        ARRAY [ST_POINT(1.234, 2),ST_POINT(3.456, 5),ST_POINT(5.678, 2),ST_POINT(1.234, 2)],
        ARRAY [ARRAY[ST_POINT(2,2.5),ST_POINT(2.1,2.6),ST_POINT(2.2,2.5),ST_POINT(3,2.5)],
        ARRAY [ST_POINT(3,2.5),ST_POINT(3.2,2.5),ST_POINT(3,2.5)]]), false);
SELECT ST_ASGEOJSON(
    ST_POLYGON(
        ARRAY [ST_POINT(1.234, 2),ST_POINT(3.456, 5),ST_POINT(5.678, 2),ST_POINT(1.234, 2)],
        ARRAY [ARRAY[ST_POINT(2,2.5),ST_POINT(2.1,2.6),ST_POINT(2.2,2.5),ST_POINT(3,2.5)],
        ARRAY [ST_POINT(3,2.5),ST_POINT(3.2,2.5),ST_POINT(3,2.5)]]), false);
﻿
Output: 
Text
{"type":"Polygon","coordinates":[[[1.234,2],[3.456,5],[5.678,2],[1.234,2]]]}
{"type":"Polygon","coordinates":[[[1.234,2],[3.456,5],[5.678,2],[1.234,2]]]}
﻿
This example converts a two-point POLYGON, which results in a LINESTRING type in the GeoJSON representation. 
SQL
SELECT ST_ASGEOJSON(
        ST_POLYGON(
            ST_LINESTRING(ST_POINT(1.234, 2), ST_POINT(3.456, 5))),
        false);
SELECT ST_ASGEOJSON(
        ST_POLYGON(
            ST_LINESTRING(ST_POINT(1.234, 2), ST_POINT(3.456, 5))),
        false);
﻿
Output: 
Text
{"type":"LineString","coordinates":[[1.234,2],[3.456,5]]}
{"type":"LineString","coordinates":[[1.234,2],[3.456,5]]}
﻿
This example converts a single-point POLYGON, which results in a POINT type in the GeoJSON representation. 
SQL
SELECT ST_ASGEOJSON(ST_POLYGON(ST_POINT(1.234, 2)), false);
SELECT ST_ASGEOJSON(ST_POLYGON(ST_POINT(1.234, 2)), false);
﻿
Output: 
Text
{"type":"Point","coordinates":[1.234,2]}
{"type":"Point","coordinates":[1.234,2]}
﻿
This example converts an empty POLYGON value into a GeoJSON representation. 
SQL
SELECT ST_ASGEOJSON(ST_POLYGONFROMTEXT('POLYGON EMPTY'), false);
SELECT ST_ASGEOJSON(ST_POLYGONFROMTEXT('POLYGON EMPTY'), false);
﻿
Output: 
Text
{"type":"Polygon","coordinates":[]}
{"type":"Polygon","coordinates":[]}
﻿
ST_ASLATLONTEXT
Returns a string that represents geographic coordinates of a specified POINT in the specified format. 
SQL
ST_ASLATLONTEXT(point, [ output_format ] )
ST_ASLATLONTEXT(point, [ output_format ] )
﻿
Argument
Data Type
Description
point
POINT
A POINT value used to calculate geographic coordinates. 
﻿
If point is NULL, then this function returns NULL.
output_format
CHAR
Optional. A string that includes option flags to set the format and decimal precision for the output of the coordinates. 
Option flags must be uppercase to be recognized. Accepted option flags are: 
D - Represents coordinate degrees. If you specify a format string for output_format, this value is required. If M is omitted, this option flag sets the decimal precision. 
M - Represents minutes of latitude or longitude distance. If S is omitted, this option flag sets the decimal precision. 
S - Represents seconds of latitude or longitude distance.
C - Represents the cardinal direction. If you specify this value, the appropriate cardinal direction is represented as N/S/E/W. If you omit this value, coordinates that are south or west are represented as negative values while east or north remain positive values. 
For all option flags besides C, you can repeat the option characters and use them with decimal points to set the format and decimal precision. The number of characters after the decimal point indicates the precision, while the number of characters before the decimal indicates the total width of the formatted number, including the decimal POINT.
If the formatted number is shorter than the requested width, the system pads the value with leading spaces. 
Characters other than D, M, S, C, and . are passed through as string characters. 
If you do not specify output_format or leave it as empty, the value defaults to the format D°M''S.SSS"C .
Example
SQL
SELECT ST_ASLATLONTEXT(ST_POINT(10.2342342,-2.32498), 'D.D degrees MM.MM minutes C');
SELECT ST_ASLATLONTEXT(ST_POINT(10.2342342,-2.32498), 'D.D degrees MM.MM minutes C');
﻿
Output: '2.0 degrees  19.50 minutes S 10.0 degrees  14.05 minutes E'
ST_ASTEXT
Alias of ST_ASWKT and ST_EWKT. 
Returns the well-known text (WKT) representation of the specified geography. 
If you use ST_ASEWKT, the database prepends the resultant string with SRID=4326;.
SQL
ST_ASTEXT(geo)
ST_ASTEXT(geo)
﻿
Argument
Data Type
Description
geo
POINT, LINESTRING, or POLYGON
A geospatial object that is to be returned as a WKT value. 
If you specify a LINESTRING or POLYGON that contains a single POINT, the function returns the WKT representation as a POINT. 
If you specify a POLYGON that has an exterior that is not closed, the function returns the WKT representation as a LINESTRING.
Example
SQL
SELECT ST_ASTEXT(ST_POLYGON(ST_LINESTRING('LINESTRING(1 2, 1 3, 1 3, 1 2)')));
SELECT ST_ASTEXT(ST_POLYGON(ST_LINESTRING('LINESTRING(1 2, 1 3, 1 3, 1 2)')));
﻿
Output: POLYGON((1 2, 1 3, 1 3, 1 2))
ST_ASEWKT
For usage, see ST_ASTEXT﻿. 
ST_ASWKT
Alias of ST_ASTEXT﻿.
ST_ASWKB
Alias of ST_ASBINARY﻿. 
ST_GEOHASH
Returns a string that represents the geohash of the input POINT.  This function also accepts an optional second argument to specify the length of the geohash result. 
If you specify any argument as NULL, then the function returns NULL.
SQL
ST_GEOHASH(point [, precision ] )
ST_GEOHASH(point [, precision ] )
﻿
Argument
Data Type
Description
point
POINT
A POINT value that is to be converted into a geohash. 
precision
INTEGER
Optional. A numeric value that specifies the geohash precision, which determines the length of the output string. 
This number must be in the range of 1 to 20. If you do not specify this value, precision defaults to 20. 
Example
SQL
SELECT ST_GEOHASH(ST_POINT(-126, 48));
SELECT ST_GEOHASH(ST_POINT(-126, 48));
﻿
Output: c0w3hc0w3hf1s70w3hf1s70w3
Related Links
﻿Geospatial Data Types﻿﻿
﻿Attribute Functions﻿﻿

Argument	Data Type	Description
geo	POINT, LINESTRING, or POLYGON	The geospatial object used to calculate the dimensions.
XDR_or_NDR	CHAR	Optional. This argument determines whether the resulting WKB is in little endian (NDR) or big endian (XDR) format. If you specify this argument, it must be either XDR or NDR. Otherwise, it defaults to NDR.

Argument	Data Type	Description
geo	POINT, LINESTRING, or POLYGON	The geospatial object used to create a GeoJSON representation. Note that the GeoJSON specification has certain requirements on what constitutes a LINESTRING and POLYGON . In keeping with this specification, a POLYGON instance that represents a LINESTRING results in a LINESTRING GeoJSON instance. Similarly, a POLYGON instance that represents a POINT results in a POINT GeoJSON instance. A LINESTRING instance that represents a POINT results in a POINT GeoJSON instance. Unlike the GeoJSON specification, a specified POLYGON does not need to follow the Right-Hand Rule. If a specified POLYGON contains any holes that are not valid POLYGON objects, then the function omits those holes in the resulting GeoJSON instance.
planar_conversion	BOOLEAN	Optional. A Boolean value that determines if the geodesic edges of the specified geography are converted to planar representation before the function applies the GeoJSON format. IETF standards state that all GeoJSON objects are to be interpreted as explicitly planar. This argument allows preservation of the original geodesic shape. If planar_conversion is specified TRUE, points will be added such that the resulting GeoJSON represents a planar shape within 10m of the original geospatial object. Additionally, if the original geospatial object crossed the international dateline, it will be split at the point where it crosses. This will generate a GeoJSON object of either MultiLineString or MultiPolygon type, depending on the original geospatial object's type. If you do not specify this argument, the value defaults to FALSE.

Argument	Data Type	Description
point	POINT	A POINT value used to calculate geographic coordinates. If point is NULL, then this function returns NULL.
output_format	CHAR	Optional. A string that includes option flags to set the format and decimal precision for the output of the coordinates. Option flags must be uppercase to be recognized. Accepted option flags are: D - Represents coordinate degrees. If you specify a format string for output_format, this value is required. If M is omitted, this option flag sets the decimal precision. M - Represents minutes of latitude or longitude distance. If S is omitted, this option flag sets the decimal precision. S - Represents seconds of latitude or longitude distance. C - Represents the cardinal direction. If you specify this value, the appropriate cardinal direction is represented as N/S/E/W. If you omit this value, coordinates that are south or west are represented as negative values while east or north remain positive values. For all option flags besides C, you can repeat the option characters and use them with decimal points to set the format and decimal precision. The number of characters after the decimal point indicates the precision, while the number of characters before the decimal indicates the total width of the formatted number, including the decimal POINT. If the formatted number is shorter than the requested width, the system pads the value with leading spaces. Characters other than D, M, S, C, and . are passed through as string characters. If you do not specify output_format or leave it as empty, the value defaults to the format D°M''S.SSS"C .

Argument	Data Type	Description
geo	POINT, LINESTRING, or POLYGON	A geospatial object that is to be returned as a WKT value. If you specify a LINESTRING or POLYGON that contains a single POINT, the function returns the WKT representation as a POINT. If you specify a POLYGON that has an exterior that is not closed, the function returns the WKT representation as a LINESTRING.

Argument	Data Type	Description
point	POINT	A POINT value that is to be converted into a geohash.
precision	INTEGER	Optional. A numeric value that specifies the geohash precision, which determines the length of the output string. This number must be in the range of 1 to 20. If you do not specify this value, precision defaults to 20.

Updated 16 May 2024

Did this page help you?

Attribute Functions

Linestring Constructors