Returning geospatial data in well-known formats

Edit online

To return geospatial data in an industry-standard spatial data format, use the ST_ASTEXT or ST_ASBINARY scalar functions.

Example 1: Converting geometry objects to well-known text (WKT) format

Converting geometry objects to WKT formats allow geometries to be exchanged in text form.

The ST_ASTEXT function converts a geometry value to a WKT string. The following example selects values from the SAMPLE_GEOMETRY table. The query returns CLOB values containing UTF-8 character strings.


SELECT id, QSYS2.ST_ASTEXT(geom) AS geometry_wkt 
  FROM sample_geometry;

ID       GEOMETRY_WKT
------   --------------------------------
   100   POINT (-110.39999999999999 44.449999999999996) 
   200   POLYGON ((-111.1259 45.1207, -111.1719 45.0544, -111.0974 44.1269, 
                   -109.98949999999999 44.1307, -109.8106 44.7134, -110.0009 45.0659, 
                   -111.1259 45.1207))

Example 2: Converting geometry objects to Well-known binary (WKB) format

Converting geometry objects to WKB formats allow geometries to be exchanged in binary form. The WKB format consists of binary data structures that must be BLOB values. These BLOB values represent binary data structures that must be managed by a database application program written in a supported programming language and for which there is a language binding.

The ST_ASBINARY function converts a geometry value to the WKB format. The following example selects values from the SAMPLE_GEOMETRY table.

SELECT id, QSYS2.ST_ASBINARY(geom) AS geometry_wkb
  FROM sample_geometry;

ID       GEOMETRY_WKB
------   --------------------------------
   100   0000000001C05B9999999999994046399999999999 
   200   00000000030000000100000007C05BC80EBEDFA44040468F7318FC5048C05BCB0068DB8BAC
         404686F694467382C05BC63BCD35A8584046103E425AEE63C05B7F53F7CED916404610BAC7
         10CB29C05B73E0DED288CE40465B50B0F27BB3C05B800EBEDFA4404046886F69446738C05B
         C80EBEDFA44040468F7318FC5048