Using GEOMETRY and GEOGRAPHY data types in ODBC

Vertica GEOMETRY and GEOGRAPHY data types are backed by LONG VARBINARY native types and ODBC client applications treat them as binary data.

Vertica GEOMETRY and GEOGRAPHY data types are backed by LONG VARBINARY native types and ODBC client applications treat them as binary data. However, these data types have a format that is unique to Vertica. To manipulate this data in your C++ application, you must use the functions in Vertica that convert them to a recognized format.

To convert a WKT or WKB to the GEOMETRY or GEOGRAPHY format, use one of the following SQL functions:

ST_GeographyFromText—Converts a WKT to a GEOGRAPHY type.
ST_GeographyFromWKB—Converts a WKB to a GEOGRAPHY type.
ST_GeomFromText—Converts a WKT to a GEOMETRY type.
ST_GeomFromWKB—Converts a WKB to GEOMETRY type.

To convert a GEOMETRY or GEOGRAPHY object to its corresponding WKT or WKB, use one of the following SQL functions:

ST_AsText—Converts a GEOMETRY or GEOGRAPHY object to a WKT, returns a LONGVARCHAR.
ST_AsBinary—Converts a GEOMETRY or GEOGRAPHY object to a WKB, returns a LONG VARBINARY.

The following code example converts WKT data into GEOMETRY data using ST_GeomFromText and stores it in a table. Later, this example retrieves the GEOMETRY data from the table and converts it to WKT and WKB format using ST_AsText and ST_AsBinary.

// Compile on Linux using:
//  g++ -g -I/opt/vertica/include -L/opt/vertica/lib64 -lodbc -o SpatialData SpatialData.cpp
// Some standard headers
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>
#include <sstream>
// Only needed for Windows clients
// #include <windows.h>
// Standard ODBC headers
#include <sql.h>
#include <sqltypes.h>
#include <sqlext.h>
// Helper function to print SQL error messages.
template <typename HandleT>
void reportError(int handleTypeEnum, HandleT hdl)
{
    // Get the status records.
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     ret2;
    SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;
    i = 1;
    printf("\n");
    while ((ret2 = SQLGetDiagRec(handleTypeEnum, hdl, i, SqlState, &NativeError,
                                Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
        printf("error record %d\n", i);
        printf("sqlstate: %s\n", SqlState);
        printf("detailed msg: %s\n", Msg);
        printf("native error code: %d\n\n", NativeError);
        i++;
    }
    exit(EXIT_FAILURE); // bad form... but Ok for this demo
}
int main()
{
    // Set up the ODBC environment
    SQLRETURN ret;
    SQLHENV hdlEnv;
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);
    assert(SQL_SUCCEEDED(ret));
    // Tell ODBC that the application uses ODBC 3.
    ret = SQLSetEnvAttr(hdlEnv, SQL_ATTR_ODBC_VERSION,
        (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    assert(SQL_SUCCEEDED(ret));
    // Allocate a database handle.
    SQLHDBC hdlDbc;
    ret = SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlDbc);
    assert(SQL_SUCCEEDED(ret));
    // Connect to the database
    printf("Connecting to database.\n");
    const char *dsnName = "ExampleDB";
    const char* userID = "dbadmin";
    const char* passwd = "password123";
    ret = SQLConnect(hdlDbc, (SQLCHAR*)dsnName,
        SQL_NTS,(SQLCHAR*)userID,SQL_NTS,
        (SQLCHAR*)passwd, SQL_NTS);
    if(!SQL_SUCCEEDED(ret)) {
        printf("Could not connect to database.\n");
        reportError<SQLHDBC>(SQL_HANDLE_DBC, hdlDbc);

    } else {
        printf("Connected to database.\n");
    }

    // Disable AUTOCOMMIT
    ret = SQLSetConnectAttr(hdlDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF,
        SQL_NTS);

    // Set up a statement handle
    SQLHSTMT hdlStmt;
    SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);

    // Drop any previously defined table.
    ret = SQLExecDirect(hdlStmt, (SQLCHAR*)"DROP TABLE IF EXISTS polygons",
        SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}

    // Run query to create a table to hold a geometry.
    ret = SQLExecDirect(hdlStmt,
        (SQLCHAR*)"CREATE TABLE polygons(id INTEGER PRIMARY KEY, poly GEOMETRY);",
        SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}

    // Create the prepared statement. This will insert data into the
    // table we created above. It uses the ST_GeomFromText function to convert the
    // string-formatted polygon definition to a GEOMETRY datat type.
    printf("Creating prepared statement\n");
    ret = SQLPrepare (hdlStmt,
        (SQLTCHAR*)"INSERT INTO polygons(id, poly) VALUES(?, ST_GeomFromText(?))",
        SQL_NTS) ;
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}

    SQLINTEGER id = 0;
    int numBatches = 5;
    int rowsPerBatch = 10;

    // Polygon definition as a string.
    char polygon[] = "polygon((1 1, 1 2, 2 2, 2 1, 1 1))";
    // Bind variables to the parameters in the prepared SQL statement
    ret = SQLBindParameter(hdlStmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,
        0, 0, &id, 0 , NULL);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT,hdlStmt);}
    // Bind polygon string to the geometry column
    SQLBindParameter(hdlStmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_LONGVARCHAR,
        strlen(polygon), 0, (SQLPOINTER)polygon, strlen(polygon), NULL);
     if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT,hdlStmt);}
    // Execute the insert
    ret = SQLExecute(hdlStmt);
    if(!SQL_SUCCEEDED(ret)) {
       reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);
    }  else {
        printf("Executed batch.\n");
    }

    // Commit the transaction
    printf("Committing transaction\n");
    ret = SQLEndTran(SQL_HANDLE_DBC, hdlDbc, SQL_COMMIT);
    if(!SQL_SUCCEEDED(ret)) {
        reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);
    }  else {
        printf("Committed transaction\n");
    }
    // Now, create a query to retrieve the geometry.
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    printf("Getting data from table.\n");
    // Execute a query to get the id, raw geometry data, and
    // the geometry data as a string. Uses the ST_AsText SQL function to
    // format raw data back into a string polygon definition
    ret = SQLExecDirect(hdlStmt,
        (SQLCHAR*)"select id,ST_AsBinary(poly),ST_AsText(poly) from polygons ORDER BY id;",
        SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT,hdlStmt);}

    SQLINTEGER idval;
    // 10MB buffer to hold the raw data from the geometry (10Mb is the maximum
    // length of a GEOMETRY)
    SQLCHAR* polygonval = (SQLCHAR*)malloc(10485760);
    SQLLEN polygonlen, polygonstrlen;
    // Buffer to hold a LONGVARCHAR that can result from converting the
    // geometry to a string.
    SQLTCHAR* polygonstr = (SQLTCHAR*)malloc(33554432);

    // Get the results of the query and print each row.
    do {
        ret = SQLFetch(hdlStmt);
        if (SQL_SUCCEEDED(ret)) {
            // ID column
            ret = SQLGetData(hdlStmt, 1, SQL_C_LONG, &idval, 0, NULL);
            if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
            printf("id: %d\n",idval);
            // The WKB format geometry data
            ret = SQLGetData(hdlStmt, 2, SQL_C_BINARY, polygonval, 10485760,
                &polygonlen);
            if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
            printf("Polygon in WKB format: ");
            // Print each byte of polygonval buffer in hex format.
            for (int z = 0; z < polygonlen; z++)
                printf("%02x ",polygonval[z]);
            printf("\n");
            // Geometry data formatted as a string.
            ret = SQLGetData(hdlStmt, 3, SQL_C_TCHAR, polygonstr, 33554432, &polygonstrlen);
            if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
            printf("Polygon in WKT format: %s\n", polygonstr);
        }
    } while(SQL_SUCCEEDED(ret));


    free(polygonval);
    free(polygonstr);
    // Clean up
    printf("Free handles.\n");
    ret = SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    ret = SQLFreeHandle(SQL_HANDLE_DBC, hdlDbc);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    ret = SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
    if (!SQL_SUCCEEDED(ret)) {reportError<SQLHDBC>(SQL_HANDLE_STMT, hdlStmt);}
    exit(EXIT_SUCCESS);
}

The output of running the above example is:

Connecting to database.
Connected to database.
Creating prepared statement
Executed batch.
Committing transaction
Committed transaction
Getting data from table.
id: 0
Polygon in WKB format: 01 03 00 00 00 01 00 00 00 05 00 00 00 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00 40 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f
Polygon in WKT format: POLYGON ((1 1, 1 2, 2 2, 2 1, 1 1))
Free handles.

Note

Do not use inefficient encoding formats for LONG VARBINARY and LONG VARCHAR values. Vertica cannot load encoded values larger than 32 MB, even if the decoded value is less than 32 MB in size. For example, Vertica returns an error if you attempt to load a 32 MB LONG VARBINARY value encoded in octal format, since the octal encoding quadruples the size of the value (each byte is converted into a backslash followed by three digits).