16.20 - FNC_GeomGetWKTClob - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Purpose

Returns the Well-Known Text (WKT) representation of the Geometry as a CLOB in the Latin character set.

Syntax

void
FNC_GeomGetWKTClob(GEO_HANDLE  geoHandle,
                   LOB_LOCATOR *geoClob,
                   int *srid)
GEO_HANDLE geoHandle
A handle to an ST_Geometry type that is defined to be an input parameter to an external routine.
LOB_LOCATOR geoClob
LOB locator for the CLOB representation of the ST_Geometry type.
int* srid
Spatial Reference System Identifier of the Geometry.

Usage Notes

FNC_GeomGetWKTClob is used to get a LOB locator for the CLOB representation of the ST_Geometry type. The ST_Geometry Handle geoHandle is passed as input. The LOB locator value geoClob is returned from the function. The user can then use LOB FNC routines to read from the locator.

The FNC_GetGeomeryInfo function can be used to determine if the ST_Geometry instance is a LOB-based value. If the instance is not a LOB (i.e. numLobs = 0), then use the non-LOB FNC_GeomGetWKT function.

If the FNC_GeomGetWKTClob function is used on an inline ST_Geometry value, a 7579 error is generated: FNC_GeomGetWKTClob is only valid for LOB-based ST_Geometry values. Use FNC_GeomSetWKT() instead.

Example: Using FNC_GeomGetWKTClob to retrieve a ST_Geometry WKT

The following function takes an ST_Geometry parameter and retrieves the Well-Known Text format for the geometry. It returns the string back as a CLOB.

REPLACE FUNCTION getwkt_udf(p1 ST_Geometry)
RETURNS CLOB AS LOCATOR
SPECIFIC getwkt_udf
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
LANGUAGE C
EXTERNAL NAME 'CS!getwkt_udf!getwkt.c!F!getwkt_udf'; 

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>

void getwkt_udf(GEO_HANDLE  *geo_in, 
                LOB_RESULT_LOCATOR *outClob,
                int         *indicator_geo_in,
                int         *indicator_outClob,
                char        sqlstate[6],
                SQL_TEXT    extname[129],
                SQL_TEXT    specific_name[129],
                SQL_TEXT    error_message[257])
{
    LOB_LOCATOR geoClob_in;
    BYTE buffer[64000];
    LOB_CONTEXT_ID id;
    FNC_LobLength_t actlen;
    int trunc_err = 0;
    FNC_LobLength_t clob_length;
    int srid = -1;
    int max_length;
    int numLobs;

    /* If input is null set return value to null. */
    if (*indicator_geo_in == -1 )
    {
        *indicator_outClob = -1;
        return;
    }
    /* Get the Geometry Info */
     FNC_GetGeometryInfo(*geo_in,&max_length, &numLobs);

    /* 
     * Copy geo_in ST_Geometry value to outClob value.
     *
     */
    if(numLobs == 1)
    {
        /* Get the LOB_LOCATOR and srid of the input ST_Geometry value. */ 
 FNC_GeomGetWKTClob(*geo_in, &geoClob_in, &srid); 

       FNC_LobOpen(geoClob_in, &id, 0, 0);
       clob_length = FNC_GetLobLength(geoClob_in);
       while (FNC_LobRead(id, buffer, clob_length, &actlen) == 0 
                                                          && !trunc_err)
           trunc_err = FNC_LobAppend(*outClob, buffer, actlen, &actlen);
       FNC_LobClose(id);
    }
}