The ST_AsBinary() function

The return type of ST_AsBinary() is defined as ST_Geometry to allow spatial objects greater than 2 kilobytes to be retrieved by a client application. Typically, you use ST_AsBinary() to retrieve spatial data from the server and send it to a client, as in:

SELECT ST_AsBinary(geomcol) FROM mytable

You can write user-defined routines (UDRs) in C or SPL to extend the functionality of the existing spatial data type functions. You can use ST_AsBinary() to convert an ST_Geometry to its well-known binary representation. If you pass the output of ST_AsBinary() to another UDR whose function signature requires an LVARCHAR input, you should explicitly cast the return type of ST_AsBinary() to LVARCHAR, as in:

EXECUTE FUNCTION MySpatialFunc(ST_AsBinary(geomcol)::lvarchar);

The code fragment below converts the footprint multipolygons of the buildingfootprints table into WKB multipolygons using the ST_AsBinary() function. The multipolygons are passed to the application's draw_polygon() function for display:

/* Create the SQL expression. */
    sprintf(sql_stmt,
            "SELECT ST_AsBinary(zone) "
            "FROM sensitive_areas WHERE "
            "SE_EnvelopesIntersect(zone,ST_PolyFromWKB(?,%d))", srid);
    
    /* Prepare the SQL statement. */
    SQLPrepare(hstmt, (UCHAR *) sql_stmt, SQL_NTS);

    /* Bind the query shape parameter. */
    pcbvalue1 = query_wkb_len;
    SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
                      SQL_INFX_UDT_LVARCHAR, query_wkb_len, 0,
                      query_wkb_buf, query_wkb_len, &pcbvalue1);

    /* Execute the query. */
    rc = SQLExecute(hstmt);

    /* Assign the results of the query (the buildingfootprint polygons)
     * to the fetched_binary variable. */
    SQLBindCol (hstmt, 1, SQL_C_BINARY,
                fetched_wkb_buf, 10000, &fetched_wkb_len);

    /* Fetch each polygon within the display window and display it. */
    while (1)
    {
        rc = SQLFetch(hstmt);
        if (rc == SQL_NO_DATA_FOUND)
            break;
        else
            returncode_check(NULL, hstmt, rc, "SQLFetch");
        
        draw_polygon(fetched_wkb_buf);
    }

    /* Close the result set cursor */
    SQLCloseCursor(hstmt);