Examples for querying spatial data

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

Converting geometry objects to WKT formats allow geometries to be exchanged in ASCII text form. The WKT formats 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(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, ST_AsText(geom)
 INTO :id, :wkt_buffer :wkt_buffer_ind
 FROM sample_geometry
 WHERE id = 100;

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 stored in a table to the well-known binary (WKB) format, 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, ST_AsBinary(geom)
   INTO :id, :wkb_buffer :wkb_buffer_ind
   FROM sample_geometry
   WHERE id = 200;

Example 3: Converting geometry objects to Geography Markup Language (GML) format

Converting geometry objects to GML formats allow geometries to be exchanged in ASCII text form. GML formats 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(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>

Example 4: Converting geometry objects to ESRI shape formats

Converting geometry objects to ESRI shape formats allows geometries to be exchanged in binary form. The ESRI shape format 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, ST_AsShape(geom)
   INTO :id, :shape_buffer
   FROM sample_geometry;