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

/***********************************************************************
*
*  load_wkb.ec -- Loads data in Well Known Binary data format.
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*  Purpose:
*     Creates a sample dataset using OGIS Well Known Binary functions
*  
*  The following command will likely compile this source:
*     esql -g -o load_wkb load_wkb.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"

/*
 * Local function prototypes
 *
 */

static void insert_wkb     (SpatialColumn  *spatial_column,
                            int             id,
                            WkbType         type,
                            int             data_len,
                            char           *binary);

static void create_table   (SpatialColumn  *spatial_column);


/************************************************************************
*
*  main -- Inserts of all wkb types from binary data into a test table.
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*  Purpose:
*      Load a table with all types of geometry from wkb format.
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*  Parameters:
*     argc     <Input>  == (int) The parameter index.
*     argv     <Input>  == (char[] *) Pointer to an array of parameters.
*
*************************************************************************/

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

    /* Declare local host variables */

    EXEC SQL BEGIN DECLARE SECTION;
    char          dbname[256];
    EXEC SQL END DECLARE SECTION;

    /* Declare other local variables */

    char         *binary;
    int           rc;
    SpatialColumn sc; 
    Geometry      geom = {0};
    int           offsets[10];
    int           suboffsets[10];
    Point         pt[30];
    int           data_len;
    int           max_alloced = 0;

    /* Check for the correct number of arguments entered. */

    if (argc < 2)
    {
        printf ("\nUsage: %s <database> [<srid>]\n", argv[0]);
        printf ("\n");
        printf ("\nThis program will create a table named 'wkb_test' and\n");
        printf ("insert data using various ST_xxxFromWkb functions.\n");
        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 */

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

    /* Load the spatial column structure. */

    strcpy (sc.table, "wkb_test");
    strcpy (sc.column, "geom");

    /* Create the test table */

    create_table (&sc);

    /* The remainder of the main function prepares the various WKB 
       geometry structures with valid X,Y coordinate data and 
       passes these structures to the insert_wkb function.  insert_wkb
       inserts the geometry into the spatial column of the 
       table, along with an integer id value.
     */

    geom.offsets = offsets;
    geom.suboffsets = suboffsets;
    geom.pt = pt;
  
    /*********************************************************/
    /*                                                       */
    /* POINTS                                                */
    /*                                                       */
    /* Points are written into the wkbPoint structure which  */
    /* includes the byte order (little endian or big         */
    /* endian), the data type, (in this case point), and     */
    /* The X,Y coordinate pair.                              */
    /*                                                       */
    /*********************************************************/

    /*======================================================================*/
    /*                                                                      */
    /* Insert a single point.                                               */
    /*                                                                      */
    /*======================================================================*/
   
    /* Populate the point */
    pt[0].x = 10; pt[0].y = -10;

    geom.type = geomPoint;
    geom.num_points = 1;
    geom.num_parts  = 1;
    
    /* Convert the point to a Well Known Binary representation. */

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Store the point in the table. */

    insert_wkb (&sc, 1, wkbPoint, data_len, binary);


    /*********************************************************/
    /*                                                       */
    /* MULTIPOINTS                                           */
    /*                                                       */
    /* Multipoints are collections of points and as such     */
    /* the wkbMultiPoint structure stores an array of        */
    /* WKBPoint structures. Therefore the thePoint WKBPoint  */
    /* variable is populated successively and each one       */
    /* is stored as an element of the wkbMultiPoint.point[]  */
    /* array.                                                */
    /*                                                       */
    /*********************************************************/


    /*======================================================================*/
    /*                                                                      */
    /* Insert a multipoint containing 5 points. This will be a simple       */
    /* multipoint since none of the points share the same location.         */
    /*                                                                      */
    /*======================================================================*/

    /* Populate the multipoint */

    pt[0].x = 1; pt[0].y =  2;
    pt[1].x = 3; pt[1].y =  4; 
    pt[2].x = 5; pt[2].y =  6; 
    pt[3].x = 7; pt[3].y =  8; 
    pt[4].x = 9; pt[4].y = 10; 

    geom.type = geomMultiPoint;
    geom.num_points = 5;
    geom.num_parts  = 5;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table */ 

    insert_wkb (&sc, 2, wkbMultiPoint, data_len, binary);


    /*======================================================================*/
    /*                                                                      */
    /* Insert a multipoint containing a single point.                       */ 
    /*                                                                      */
    /*======================================================================*/

    /* Populate the multipoint. */

    pt[0].x = 10.0; pt[0].y = 3.1415927; 

    geom.type = geomMultiPoint;
    geom.num_points = 1;
    geom.num_parts  = 1;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table */
  
    insert_wkb (&sc, 3, wkbMultiPoint, data_len, binary);


    /*======================================================================*/
    /*                                                                      */
    /* Insert a multipoint containing three points at the same location.    */
    /* This is a non-simple multipoint since it contains points that        */
    /* share the same location.                                             */
    /*                                                                      */
    /*======================================================================*/

    /* Populate the multipoint. */

    pt[0].x = 1; pt[0].y = 1; 
    pt[0].x = 1; pt[0].y = 1;
    pt[0].x = 1; pt[0].y = 1;

    geom.type = geomMultiPoint;
    geom.num_points = 3;
    geom.num_parts  = 3;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);
  
    /* Insert the record into the table. */

    insert_wkb (&sc, 4, wkbMultiPoint, data_len, binary);

  
    /*********************************************************/
    /*                                                       */
    /* LINESTRINGS                                           */
    /*                                                       */
    /* Linestrings are linear paths of X,Y coordinates       */
    /* assembled in the wkbLineString structure which        */
    /* includes the byte order, data type (LINESTRING),      */
    /* number of points in the linestring, and the array     */
    /* of X,Y coordinates that form the linestring.          */
    /*                                                       */
    /*********************************************************/


    /*======================================================================*/
    /*                                                                      */
    /* Insert a two point linestring. This linestring is simple since does  */
    /* not intersect its interior.                                          */
    /*                                                                      */
    /*======================================================================*/

    /* Populate the linestring. */

    pt[0].x = -50.123; pt[0].y = -50.1234567;
    pt[1].x =  50;     pt[1].y =  50;

    geom.type = geomLineString;
    geom.num_points   = 2;
    geom.num_parts    = 1;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table  */

    insert_wkb (&sc, 5, wkbLineString, data_len, binary);

  
    /*======================================================================*/
    /*                                                                      */
    /* Insert a six point linestring. The linestring is simple because it   */
    /* does not intersect its interior.                                     */ 
    /*                                                                      */
    /*======================================================================*/

    /* Populate the linestring. */

    pt[0].x = 0;   pt[0].y = 0;
    pt[1].x = 1;   pt[1].y = 1;
    pt[2].x = 2;   pt[2].y = 2;
    pt[3].x = 3;   pt[3].y = 3;
    pt[4].x = 4.5; pt[4].y = 5.4;
    pt[5].x = 7;   pt[5].y = 7;

    geom.type = geomLineString;
    geom.num_points   = 6;
    geom.num_parts    = 1;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table. */

    insert_wkb (&sc, 6, wkbLineString, data_len, binary);


    /*======================================================================*/
    /*                                                                      */
    /* Insert a closed linestring (ring). This linestring is simple because */
    /* the interior is not intersected.                                     */
    /*                                                                      */
    /*======================================================================*/

    /* Populate the linestring (ring). */

    pt[0].x = 10; pt[0].y = 10;
    pt[1].x = 10; pt[1].y = 20;
    pt[2].x = 20; pt[2].y = 10;
    pt[3].x = 10; pt[3].y = 10;

    geom.type = geomLineString;
    geom.num_points   = 4;
    geom.num_parts    = 1;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table.  */

    insert_wkb (&sc, 7, wkbLineString, data_len, binary);


    /*======================================================================*/
    /*                                                                      */
    /* Insert a four point self intersecting linestring.  This linestring   */
    /* is considered non-simple because it intersects its interior.         */
    /*                                                                      */
    /*======================================================================*/

    /* Populate the linestring. */

    pt[0].x = 10; pt[0].y = 10;
    pt[1].x = 20; pt[1].y = 20;
    pt[2].x = 25; pt[2].y = 15;
    pt[3].x = 0;  pt[3].y = 15;
  
    geom.type = geomLineString;
    geom.num_points   = 4;
    geom.num_parts    = 1;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);
  
    /* Insert the record into the table. */

    insert_wkb (&sc, 8, wkbLineString, data_len, binary);

  
    /***********************************************************/
    /*                                                         */
    /* MULTILINESTRINGS                                        */
    /*                                                         */
    /* Multilinestrings are collections of linestrings. The    */
    /* wkbMultiLineString structure stores an array of         */
    /* wkbLineString structures. Therefore the theLinestring   */
    /* variable is populated successively and each one is      */
    /* stored as an element of the                             */
    /* wkbMultiLineString.linestrings[] array.                 */
    /*                                                         */
    /***********************************************************/


    /*======================================================================*/
    /*                                                                      */
    /* Insert a multilinestring containing two intersecting linestrings.    */
    /* Since the linestrings intersect at their interiors they are          */
    /* considered non-simple.                                               */
    /*                                                                      */
    /*======================================================================*/

    /* Populate the first linestring. */

    pt[0].x = 10; pt[0].y = 10;  offsets[0] = 0;
    pt[1].x = 10; pt[1].y = 20;
  
    /* Populate the second linestring. */

    pt[2].x = 5;  pt[2].y = 15;  offsets[1] = 2; 
    pt[3].x = 15; pt[3].y = 15;

    geom.type = geomMultiLineString;
    geom.num_points   = 4;
    geom.num_parts    = 2;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);
  
    /* Insert the record into the table. */

    insert_wkb (&sc, 9, wkbMultiLineString, data_len, binary);

 
    /*======================================================================*/
    /*                                                                      */
    /* Insert a multilinestring containing 4 non-intersecting linestrings.  */
    /* The multilinesting is simple because the linestrings do not          */
    /* intersect.                                                           */
    /*                                                                      */
    /*======================================================================*/

    /* Populate the first linestring. */

    pt[0].x  =   1;  pt[0].y  =   1;  offsets[0] = 0;
    pt[1].x  =  11;  pt[1].y  =   1;
    pt[2].x  =  11;  pt[2].y  =  -9;

    /* Populate the second linesting. */

    pt[3].x  =   4;  pt[3].y  =  10;  offsets[1] = 3;
    pt[4].x  =   4;  pt[4].y  =  20;

    /* Populate the third linestring. */

    pt[5].x  = 10;  pt[5].y  = 10;  offsets[2] = 5;
    pt[6].x  = 20;  pt[6].y  = 10;
 
    /* Populate the fourth linestring. */

    pt[7].x  = -10;  pt[7].y  = -10;  offsets[3] = 7;
    pt[8].x  = -20;  pt[8].y  = -10;
    pt[9].x  = -30;  pt[9].y  = -10;
    pt[10].x = -30;  pt[10].y =   0;
  
    geom.type = geomMultiLineString;
    geom.num_points   = 11;
    geom.num_parts    = 4;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);
  
    /* Insert the record into the table. */

    insert_wkb (&sc, 10, wkbMultiLineString, data_len, binary);


    /*********************************************************/
    /*                                                       */
    /* POLYGONS                                              */
    /*                                                       */
    /* Polygons are areas formed by the enclosure of an      */
    /* exterior ring and the exclusion of the area within    */
    /* any number of non-overlapping interior rings.         */
    /* The WKBPolygon structure includes the byte-order,     */
    /* data-type (POLYGON), number of rings, and an array    */
    /* of ring structures. The ring structures include the   */
    /* number of points in the ring and the array of X,Y     */
    /* coordinate points.                                    */
    /*                                                       */
    /*********************************************************/


    /*======================================================================*/
    /*                                                                      */
    /* Insert a polygon with no interior rings. Notice that to be a polygon */
    /* the first and last coordinate must be the same.                      */
    /*                                                                      */
    /*======================================================================*/

    /* The exterior ring. */

    pt[0].x = 10; pt[0].y = 10;
    pt[1].x = 10; pt[1].y = 20;
    pt[2].x = 20; pt[2].y = 20;
    pt[3].x = 20; pt[3].y = 10;
    pt[4].x = 10; pt[4].y = 10;

    suboffsets[0]=0;

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

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table. */

    insert_wkb (&sc, 11, wkbPolygon, data_len, binary);


    /*======================================================================*/
    /*                                                                      */
    /* Insert a polygon containing a single interior ring.                  */
    /*                                                                      */
    /*======================================================================*/


    /* The exterior ring. */

    pt[0].x = 10; pt[0].y = 10;  suboffsets[0] = 0; offsets[0] = 0;
    pt[1].x = 10; pt[1].y = 20;
    pt[2].x = 20; pt[2].y = 20;
    pt[3].x = 20; pt[3].y = 10;
    pt[4].x = 10; pt[4].y = 10;

    /* The interior ring */

    pt[5].x = 12; pt[5].y = 12;  suboffsets[1] = 5;
    pt[6].x = 12; pt[6].y = 13;
    pt[7].x = 13; pt[7].y = 13;
    pt[8].x = 13; pt[8].y = 12;
    pt[9].x = 12; pt[9].y = 12; 

    geom.type = geomPolygon;
    geom.num_points   = 10;
    geom.num_parts    = 1;
    geom.num_subparts = 2;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table. */

    insert_wkb (&sc, 12, wkbPolygon, data_len, binary);


    /*======================================================================*/
    /*                                                                      */
    /* Insert a Polygon containing two interior rings.                      */
    /*                                                                      */
    /*======================================================================*/

    /* The exterior ring. */

    pt[0].x =  10;  pt[0].y =  10;  suboffsets[0] =  0;  offsets[0] = 0;
    pt[1].x =  10;  pt[1].y =  20;
    pt[2].x =  20;  pt[2].y =  20;
    pt[3].x =  20;  pt[3].y =  10;
    pt[4].x =  10;  pt[4].y =  10;

    /* The first interior ring. */

    pt[5].x =  12;  pt[5].y =  12;  suboffsets[1] =  5;
    pt[6].x =  12;  pt[6].y =  13;
    pt[7].x =  13;  pt[7].y =  13;
    pt[8].x =  13;  pt[8].y =  12;
    pt[9].x =  12;  pt[9].y =  12;

    /* The second interior ring. */

    pt[10].x = 16;  pt[10].y = 16;  suboffsets[2] = 10;
    pt[11].x = 16;  pt[11].y = 18;
    pt[12].x = 18;  pt[12].y = 18;
    pt[13].x = 18;  pt[13].y = 16;
    pt[14].x = 16;  pt[14].y = 16;

    geom.type = geomPolygon;
    geom.num_points   = 15;
    geom.num_parts    = 1;
    geom.num_subparts = 3;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the record into the table. */

    insert_wkb (&sc, 13, wkbPolygon, data_len, binary);
  
  
    /*********************************************************/
    /*                                                       */
    /* MULTIPOLYGONS                                         */
    /*                                                       */
    /* MultiPolygons are collections of polygons. The        */
    /* WKBMultipoly structure stores an array of WKBPolygon  */
    /* structures. The WKBPolygon structure is populated     */
    /* successively and each one is stored as an element of  */
    /* the WKBMultiPolygon.polygons[] array.                 */
    /*                                                       */
    /*********************************************************/


    /*======================================================================*/
    /*                                                                      */
    /* Insert a multipolygon containing three polygons. The first polygon   */
    /* contains a single interior ring, the second polygon contains no      */
    /* interior rings and the third polygon contains 2 interior rings.      */
    /*                                                                      */
    /*======================================================================*/

    /* The first polygon's exterior ring. */
  
    pt[0].x =   10;  pt[0].y =   10;  suboffsets[0] =  0;  offsets[0] = 0;
    pt[1].x =   10;  pt[1].y =   20;
    pt[2].x =   20;  pt[2].y =   20;
    pt[3].x =   20;  pt[3].y =   10;
    pt[4].x =   10;  pt[4].y =   10;

    /* The first polygon's interior ring. */ 

    pt[5].x =   14;  pt[5].y =   14;  suboffsets[1] =  5;
    pt[6].x =   14;  pt[6].y =   16;
    pt[7].x =   16;  pt[7].y =   16;
    pt[8].x =   16;  pt[8].y =   14;
    pt[9].x =   14;  pt[9].y =   14;

    /* The second polygon's exterior ring. */

    pt[10].x = 70;  pt[10].y = 70;  suboffsets[2] = 10;  offsets[1] = 2;
    pt[11].x = 70;  pt[11].y = 80;
    pt[12].x = 80;  pt[12].y = 80;
    pt[13].x = 80;  pt[13].y = 70;
    pt[14].x = 70;  pt[14].y = 70;

    /* The third polygons exterior ring. */

    pt[15].x =  50;  pt[15].y =  50;  suboffsets[3] = 15;  offsets[2] = 3;
    pt[16].x =  50;  pt[16].y =  60;
    pt[17].x =  60;  pt[17].y =  60;
    pt[18].x =  60;  pt[18].y =  50;
    pt[19].x =  50;  pt[19].y =  50;
 
    /* The third polygon's interior ring. */

    pt[20].x =  52;  pt[20].y =  52;  suboffsets[4] = 20;
    pt[21].x =  52;  pt[21].y =  54;
    pt[22].x =  54;  pt[22].y =  54;
    pt[23].x =  54;  pt[23].y =  52;
    pt[24].x =  52;  pt[24].y =  52;

    /* The third polygon's interior ring. */

    pt[25].x =  56;  pt[25].y =  56;  suboffsets[5] = 25;
    pt[26].x =  56;  pt[26].y =  58;
    pt[27].x =  58;  pt[27].y =  58;
    pt[28].x =  58;  pt[28].y =  56;
    pt[29].x =  56;  pt[29].y =  56;
  
    geom.type = geomMultiPolygon;
    geom.num_points   = 30;
    geom.num_parts    = 3;
    geom.num_subparts = 6;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);

    /* Insert the multipolygon into the table. */

    insert_wkb (&sc, 14, wkbMultiPolygon, data_len, binary);


    /*======================================================================*/
    /*                                                                      */
    /* Insert a multipolygon containing two polygons that do not have       */
    /* interior rings.                                                      */
    /*                                                                      */
    /*======================================================================*/

    /* The first polygon's exterior ring. */

    pt[0].x = 10;  pt[0].y = 10;  suboffsets[0] = 0;  offsets[0] = 0;
    pt[1].x = 10;  pt[1].y = 20;
    pt[2].x = 20;  pt[2].y = 20;
    pt[3].x = 20;  pt[3].y = 10;
    pt[4].x = 10;  pt[4].y = 10;

    /* The second polygon's exterior ring. */

    pt[5].x =  1;  pt[5].y =  1;  suboffsets[1] = 5;  offsets[1] = 1;
    pt[6].x =  1;  pt[6].y =  5;
    pt[7].x =  5;  pt[7].y =  5;
    pt[8].x =  5;  pt[8].y =  1;
    pt[9].x =  1;  pt[9].y =  1;

    geom.type = geomMultiPolygon;
    geom.num_points   = 10;
    geom.num_parts    = 2;
    geom.num_subparts = 2;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);
  
    /* Insert the multipolygon into the table. */

    insert_wkb (&sc, 15, wkbMultiPolygon, data_len, binary);
  

    /*======================================================================*/
    /*                                                                      */
    /*  Insert a multipolygon containing two polygons. The first polygon    */
    /*  contains an interior ring and the second polygon does not.          */
    /*                                                                      */
    /*======================================================================*/
  
    /* Populate the first polygons exterior ring. */

    pt[0].x = 10; pt[0].y = 10;  suboffsets[0] = 0;  offsets[0] = 0;
    pt[1].x = 10; pt[1].y = 20;
    pt[2].x = 20; pt[2].y = 20;
    pt[3].x = 20; pt[3].y = 10;
    pt[4].x = 10; pt[4].y = 10;
  
    /* Populate the first polygon's interior ring. */

    pt[5].x = 14; pt[5].y = 14;  suboffsets[1] = 5;
    pt[6].x = 14; pt[6].y = 16;
    pt[7].x = 16; pt[7].y = 16;
    pt[8].x = 16; pt[8].y = 14;
    pt[9].x = 14; pt[9].y = 14;

    /* Populate the exterior ring of the second polygon. */

    pt[10].x = 1; pt[10].y = 1;  suboffsets[2] = 10;  offsets[1] = 2;
    pt[11].x = 1; pt[11].y = 5;
    pt[12].x = 5; pt[12].y = 5;
    pt[13].x = 5; pt[13].y = 1;
    pt[14].x = 1; pt[14].y = 1;
  
    geom.type = geomMultiPolygon;
    geom.num_points   = 15;
    geom.num_parts    = 2;
    geom.num_subparts = 3;

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

    geom_to_wkb (&geom, &max_alloced, &data_len, &binary);
  
    /* Insert the multipolygon into the table. */

    insert_wkb (&sc, 16, wkbMultiPolygon, data_len, binary);


    /*===============================*/
    /*                               */
    /* Disconnect from the database. */
    /*                               */
    /*===============================*/
  
    EXEC SQL disconnect current;
}


/***********************************************************************
*
*  create_table -  Creates a table for inserting spatial data
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*  Purpose:
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*  Parameters:
*     sc              <Input>  == (SpatialColumn *) Spatial column.
*
*     RETURN          <Output> == (void)
*
***********************************************************************/

static void create_table (
    SpatialColumn *sc
    )
{
    EXEC SQL BEGIN DECLARE SECTION;
    char           sql_stmt[256];
    EXEC SQL END DECLARE SECTION;
    
    sprintf (sql_stmt, "DROP TABLE %s", sc->table);
    EXEC SQL execute immediate :sql_stmt;

    sprintf (sql_stmt, "CREATE TABLE %s (id integer, %s ST_Geometry)",
             sc->table, sc->column);
    EXEC SQL execute immediate :sql_stmt;
}


/***********************************************************************
*
*  insert_wkb  -  Inserts the geometry and integer id values into
*                 the spatial and id columns of the table 
*                 specified as arguments to the main program.
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*  Purpose:
*   The function inserts the geometry WKB binary structure into the
*   spatial column and an integer into the id column.
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*  Parameters:
*     sc              <Input>  == (SpatialColumn *) Spatial column.
*     id              <Input>  == (int) The integer id value.
*     type            <Input>  == (WkbType) The data type of the geometry.
*     data_len        <Input>  == (int) The number of bytes in wkb_buffer. 
*     wkb_buffer      <Input>  == (char *) The WKB geometry structure.
*
*     RETURN          <Output> == (void)
*
***********************************************************************/

static void insert_wkb (
    SpatialColumn *sc,
    int            id,
    WkbType        type,
    int            data_len,
    char          *wkb_buffer
    )
{
    EXEC SQL BEGIN DECLARE SECTION;
    int            i, n, xid;
    short          typ;
    char           wkb_sql[256];
    var binary     wkb;
    EXEC SQL END DECLARE SECTION;

    int            rc;
    char           wkbfunction[19];

    /* Set the wkbfunction to the "FromWkb" function that corresponds */
    /* to the data type of the WKB geometry representation. */

    switch (type)
    {
        case wkbPoint:
            strcpy (wkbfunction,"ST_PointFromWkb");
            break;
        case wkbLineString:
            strcpy (wkbfunction,"ST_LineFromWkb");
            break;
        case wkbPolygon:
            strcpy (wkbfunction,"ST_PolyFromWkb");
            break;
        case wkbMultiPoint:
            strcpy (wkbfunction,"ST_MPointFromWkb");
            break;
        case wkbMultiLineString:
            strcpy (wkbfunction,"ST_MLineFromWkb");
            break;
        case wkbMultiPolygon:
            strcpy (wkbfunction,"ST_MPolyFromWkb");
            break;
    } 

    /* Generate the SQL insert expression. The integer value and the */
    /* name of the spatial column are entered as bind parameters. */

    sprintf (wkb_sql, 
             "insert into %s (id, %s) values (?, %s (?, %d))",
             sc->table, sc->column, wkbfunction, sc->srid);

    /* Prepare the SQL statement for execution. */

    fprintf (stdout, "SQL is %s \n", wkb_sql);
    EXEC SQL prepare ins_stmt from :wkb_sql;

    /* 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; i = id;

    EXEC SQL set descriptor 'ins_desc' VALUE :n
        TYPE = :typ,
        DATA = :i;

    /* Create host variable for WKB representation */

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

    if ((rc = ifx_var_setdata(&wkb,wkb_buffer,data_len)) < 0)
    {
        fprintf(stderr, "Error calling ifx_var_setdata.");
        exit(1);
    }

    if ((rc = ifx_var_setlen(&wkb,data_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 = :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(&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;

}