Conversion Functions

{{ocientgeo}} 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 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 select st asbinary(st point(2, 2), 'ndr'); output 010100000000000000000000400000000000000040 st asgeojson returns the geojson representation of the specified geography using the https //datatracker ietf org/doc/html/rfc7946 st asgeojson( geo \[, planar conversion ] ) argument data type description geo point , linestring , or polygon the geospatial object used to create a geojson representation ℹ️ t he geojson specification has certain requirements for 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 type of the geospatial object if you do not specify this argument, the value defaults to false examples this example converts a point value into a geojson representation select st asgeojson(st point(2, 2), false); output {"type" "point","coordinates" \[2,2]} this example converts an empty point value select st asgeojson(st pointfromtext('point empty'), false); output {"type" "point","coordinates" \[]} this example converts a linestring value into a geojson representation select st asgeojson(st linestring(st point(1 234, 2),st point(3 456, 5)); output {"type" "linestring","coordinates" \[\[1 234,2],\[3 456,5]]} this example again converts a linestring value select st asgeojson(st linestring(array\[st point(1 234, 2)]); output {"type" "point","coordinates" \[1 234,2]} this example converts an empty linestring value into a geojson representation select st asgeojson(st linefromtext('linestring empty'), false); output {"type" "linestring","coordinates" \[]} this example converts a polygon value into a geojson representation 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 {"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 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 {"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 select st asgeojson( st polygon( st linestring(st point(1 234, 2), st point(3 456, 5))), false); output {"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 select st asgeojson(st polygon(st point(1 234, 2)), false); output {"type" "point","coordinates" \[1 234,2]} this example converts an empty polygon value into a geojson representation select st asgeojson(st polygonfromtext('polygon empty'), false); output {"type" "polygon","coordinates" \[]} st aslatlontext returns a string that represents geographic coordinates of a specified point in the specified 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 poin t 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 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; 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 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 /#stastext st aswkt alias of /#stastext st aswkb alias of /#stasbinary 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 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 select st geohash(st point( 126, 48)); output c0w3hc0w3hf1s70w3hf1s70w3 related links docid\ ogtviwl gtbgv0chhrh 3 docid\ b7ofhdvlpchajicwqdpfb