Constructor functions

The ST_AsText function converts a geometry value stored in a table to a WKT string. The following example uses a simple command-line query to select the values that were previously inserted into the SAMPLE_GEOMETRY table. (This example returns SQL code 445, because the data has been truncated.)

SELECT id, VARCHAR(db2gse.ST_AsText(geom), 50) AS WKTGEOM 
FROM sample_geometry;

ID 		WKTGEOM
------ 	--------------------------------
   100   POINT ( 30.00000000 40.00000000)
   200   LINESTRING ( 50.00000000 50.00000000, 100.00000000 100.00000000)

The following example uses embedded SQL to select the values that were previously inserted into the SAMPLE_GEOMETRY table.

 EXEC SQL BEGIN DECLARE SECTION;
 sqlint32 id = 0;
 SQL TYPE IS CLOB(10000) wkt_buffer;
 short wkt_buffer_ind = -1;
 EXEC SQL END DECLARE SECTION;

 EXEC SQL
 SELECT id, db2gse.ST_AsText(geom)
 INTO :id, :wkt_buffer :wkt_buffer_ind
 FROM sample_geometry
 WHERE id = 100;

In addition, IBM Spatial Support for Db2 for z/OS provides other functions that convert geometries to and from well-known text representations. The following additional functions implement the Open Geospatial Consortium (OGC) standard Simple Features SQL and the ISO standard SQL multimedia and application packages - Part 3: Spatial:

• ST_GeomFromText
• ST_WKTToSQL

These BLOB values represent binary data structures that must be managed by an application program written in a programming language that Db2 supports and for which Db2 has a language binding.

The ST_AsBinary function converts a geometry value stored in a table to the well-known binary (WKB) representation, which can be fetched into a BLOB variable in program storage. The following example uses embedded SQL to select the values that were previously inserted into the SAMPLE_GEOMETRY table.

EXEC SQL BEGIN DECLARE SECTION;
   sqlint32 id = 0;    
   SQL TYPE IS BLOB(10000) wkb_buffer;
   short wkb_buffer_ind = -1;
EXEC SQL END DECLARE SECTION;

EXEC SQL
   SELECT id, db2gse.ST_AsBinary(geom)
   INTO :id, :wkb_buffer :wkb_buffer_ind
   FROM sample_geometry
   WHERE id = 200;

In addition, IBM Spatial Support for Db2 for z/OS provides other functions that convert geometries to and from well-known binary representations. The following additional functions implement the Open Geospatial Consortium (OGC) standard Simple Features SQL and the ISO standard SQL multimedia and application packages - Part 3: Spatial:

• ST_GeomFromWKB
• ST_WKBToSQL

The ST_AsShape function converts a geometry value stored in a table to the ESRI shape representation, which can be fetched into a BLOB variable in program storage. The following example uses embedded SQL to select the values that were previously inserted into the SAMPLE_GEOMETRY table.

EXEC SQL BEGIN DECLARE SECTION;
   sqlint32 id;
   SQL TYPE IS BLOB(10000) shape_buffer;
EXEC SQL END DECLARE SECTION;

EXEC SQL
   SELECT id, db2gse.ST_AsShape(geom)
   INTO :id, :shape_buffer
   FROM sample_geometry;

The ST_AsGML function converts a geometry value that is stored in a table to a GML text string. The following example selects the values that were previously inserted into the SAMPLE_GEOMETRY table. The results shown in the example are reformatted for readability. The spacing in your results might vary according to your online display.

SELECT id, VARCHAR(db2gse.ST_AsGML(geom, 'EPSG', 4269), 500) AS GMLGEOM 
FROM sample_geometry;

ID           GMLGEOM
-----------  ----------------------------------------------------------
        100 <gml:Point srsName="EPSG:4269">
            <gml:coord><gml:X>30</gml:X><gml:Y>40</gml:Y></gml:coord>
            </gml:Point>
        200 <gml:LineString srsName="EPSG:4269">
            <gml:coord><gml:X>50</gml:X><gml:Y>50</gml:Y></gml:coord>
            <gml:coord><gml:X>100</gml:X><gml:Y>100</gml:Y></gml:coord>
            </gml:LineString>