Examples for querying spatial data
These examples show how to use the various functions to convert geometries from their original representation to another form.
Example 1: Converting geometries to well-known text (WKT) representation
Converting geometries to WKT representations allow geometries to be exchanged in ASCII text form. The WKT representations are CLOB values representing ASCII character strings.
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.
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;
Alternatively, you can use the ST_WellKnownText transform group to implicitly convert geometries to their well-known text representation when binding them out. The following sample code shows how to use the transform group.
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
SET CURRENT DEFAULT TRANSFORM GROUP = ST_WellKnownText;
EXEC SQL
SELECT id, geom
INTO :id, :wkt_buffer :wkt_buffer_ind
FROM sample_geometry
WHERE id = 100;
Example 2: Converting to well-known binary (WKB) representation
Converting geometries to WKB representations allow geometries to be exchanged in binary form. The WKB representation 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 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;
Alternatively, you can use the ST_WellKnownBinary transform group to implicitly convert geometries to their well-known binary representation when binding them out. The following sample code shows how to use this transform group.
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
SET CURRENT DEFAULT TRANSFORM GROUP = ST_WellKnownBinary;
EXEC SQL
SELECT id, geom
INTO :id, :wkb_buffer :wkb_buffer_ind
FROM sample_geometry
WHERE id = 200;
Example 3: Converting geometries to Geography Markup Language (GML) representation
Converting geometries to GML representations allow geometries to be exchanged in ASCII text form. GML representations are ASCII strings.
The ST_AsGML function converts a geometry value 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 have been reformatted for readability. The spacing in your results might vary according to your online display.
SELECT id, VARCHAR(db2gse.ST_AsGML(geom), 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>
Alternatively, you can use the ST_GML transform group to implicitly convert geometries to their HTML representation when binding them out.
SET CURRENT DEFAULT TRANSFORM GROUP = ST_GML
SELECT id, geom 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>
Example 4: Converting geometries to ESRI shape representations
Converting geometries to ESRI shape representations allow geometries to be exchanged in binary form. The ESRI Shape representation consists of binary data structures that must be managed by an application program written in a supported language.
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;
Alternatively, you can use the ST_Shape transform group to implicitly convert geometries to their shape representation when binding them out. The following sample code shows how to use the transform group.
EXEC SQL BEGIN DECLARE SECTION;
sqlint32 id = 0;
SQL TYPE IS BLOB(10000) shape_buffer;
short shape_buffer_ind = -1;
EXEC SQL END DECLARE SECTION;
EXEC SQL
SET CURRENT DEFAULT TRANSFORM GROUP = ST_Shape;
EXEC SQL
SELECT id, geom
FROM sample_geometry
WHERE id = 300;