Examples for querying spatial data

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;