analytics/informix/extend/spatial.8.22.FC2/examples/esqlc/geomfromwkb.ec

/**************************************************************************
 *
 * geomfromwkb.ec
 *
 * This program demonstrates how to insert spatial data into a database
 * in OGIS well-known binary format using the Informix Spatial DataBlade's
 * ST_GeomFromWkb function.
 *
 * This program creates a table and inserts several polygons into it.
 * For an example program which shows how to insert all spatial types,
 * please see load_wkb.ec
 *
 * The following command will likely compile this source:
 *    esql -g -o geomfromwkb geomfromwkb.ec
 *
 **************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <float.h>

EXEC SQL include sqltypes;
EXEC SQL include exp_chk.ec;

/*
 * Constants, macros, typedefs, data structures, & functions for 
 * processing OGIS well-known-binary format data.
 */

#include "commfuncs.h"
#include "commfuncs.c"
#include "wkbfuncs.c"

/*
 * Main program
 *
 */

void main (int argc, char **argv)
{

    /* Declare local host variables */

    EXEC SQL BEGIN DECLARE SECTION;
    int           n, xid;
    short         typ;
    char          dbname[256];
    char          sql_stmt[200];
    int           lot_id;
    var binary    lot_wkb;
    EXEC SQL END DECLARE SECTION;

    /* Declare other local variables */

    int           rc;
    Geometry      geom = {0};
    Point         pt[30];
    int           suboffsets[5];
    int           max_alloced = 0;
    char         *lot_wkb_buf;
    int           lot_wkb_len;
    int           srid;
    
    /* Check for the correct number of arguments entered. */

    if (argc < 2)
    {
        printf ("\nUsage: %s <database> [<srid>]\n", argv[0]);
        exit (1);
    }

    /* Define exception handling routine for EXEC SQL statements */

    EXEC SQL whenever sqlerror CALL ignore206;

    /* Connect to the database. */

    sprintf(dbname, "%s", argv[1]);
    EXEC SQL connect to :dbname;

    /* Obtain srid to use for inserts */

    srid = (argc > 2) ? atoi(argv[2]) : 0;

    EXEC SQL DROP TABLE lots;

    EXEC SQL CREATE TABLE lots (lot_id integer, lot ST_Geometry);

    /* Initialize entries for non-spatial columns in the table  */

    lot_id = 1010;
    
    /* Populate the polygon point array */

    suboffsets[0] = 0;
    pt[0].x =  2;   pt[0].y = 57;
    pt[1].x = 21.5; pt[1].y = 57;
    pt[2].x = 21.5; pt[2].y = 38;
    pt[3].x =  2;   pt[3].y = 38;
    pt[4].x =  2;   pt[4].y = 57;

    geom.type = geomPolygon;
    geom.num_points    = 5;
    geom.num_parts     = 1;
    geom.num_subparts  = 1;
    geom.suboffsets = suboffsets;
    geom.pt = pt;

    /* Convert the points to a Well Known Binary
     * representation of a polygon. */

    geom_to_wkb (&geom, &max_alloced, &lot_wkb_len, &lot_wkb_buf);

    /* Create the SQL insert statement to populate the lots
     * table. The question marks are parameter markers that indicate
     * the column values that will be inserted at run time. */

    sprintf(sql_stmt,
            "INSERT INTO lots (lot_id, lot) "
            "VALUES(?, ST_GeomFromWKB(?, %d))", srid);

    /* Prepare the SQL statement for execution. */

    EXEC SQL prepare ins_stmt from :sql_stmt;

    /* Allocate a statement descriptor. */

    EXEC SQL allocate descriptor 'ins_desc';

    /* Set the number of input parameters */

    n = 2;
    EXEC SQL set descriptor 'ins_desc' COUNT = :n;

    /* Bind the input id to the first input parameter */

    n = 1; typ = SQLINT;
    EXEC SQL set descriptor 'ins_desc' VALUE :n
        TYPE = :typ,
        DATA = :lot_id;

    /* Create host variable for WKB representation */

    if ((rc = ifx_var_alloc(&lot_wkb,lot_wkb_len)) < 0)
    {
        fprintf(stderr, "Error calling ifx_var_alloc.");
        exit(1);
    }

    if ((rc = ifx_var_setdata(&lot_wkb,lot_wkb_buf,lot_wkb_len)) < 0)
    {
        fprintf(stderr, "Error calling ifx_var_setdata.");
        exit(1);
    }

    if ((rc = ifx_var_setlen(&lot_wkb,lot_wkb_len)) < 0)
    {
        fprintf(stderr, "Error calling ifx_var_setlen.");
        exit(1);
    }

    /* Bind the WKB to the second input parameter */

    n = 2; typ = SQLUDTVAR; xid = XID_LVARCHAR;

    EXEC SQL set descriptor 'ins_desc' VALUE :n
        TYPE = :typ,
        EXTYPEID = :xid,
        DATA = :lot_wkb;

    /* Execute the insert statement. */

    EXEC SQL execute ins_stmt using sql descriptor 'ins_desc';

    /* Free resources associated with the WKB host variable */

    if ((rc = ifx_var_dealloc(&lot_wkb)) < 0)
    {
        fprintf(stderr, "Error calling ifx_var_dealloc.");
        exit(1);
    }

    /* Free resources associated with INSERT statement */

    EXEC SQL deallocate descriptor 'ins_desc';
    EXEC SQL free ins_stmt;

    /* Disconnect from the database */

    EXEC SQL disconnect current;

    printf( "\nTest Complete\n");
}