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

/**************************************************************************
 *
 * asshape.ec
 *
 * This program demonstrates how to retrieve spatial data from a database
 * in ESRI shapefile format using the Informix Spatial DataBlade's
 * SE_AsShape function.
 *
 * The following command will likely compile this source:
 *    esql -g -o asshape asshape.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 ESRI shapefile format data.
 */

#include "commfuncs.h"
#include "commfuncs.c"
#include "shapefuncs.c"

/*
 * Local function prototypes
 *
 */

void draw_polygon (char *shape_buf);

/*
 * Main program
 *
 */

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

    /* Declare local host variables */

    EXEC SQL BEGIN DECLARE SECTION;
    int           i, n, xid;
    short         typ;
    char          dbname[256];
    char          sql_stmt[200];
    var binary    query_shape;
    var binary 'st_geometry' fetched_shape;
    EXEC SQL END DECLARE SECTION;

    /* Declare other local variables */

    int           rc;
    Geometry      geom = {0};
    Point         pt[30];
    int           max_alloced = 0;
    char         *fetched_shape_buf;
    int           fetched_shape_len;
    char         *query_shape_buf;
    int           query_shape_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 sensitive_areas;
    
    EXEC SQL CREATE TABLE sensitive_areas (id integer, zone ST_Polygon);

    sprintf(sql_stmt,
            "INSERT INTO sensitive_areas VALUES(1, "
            "'%d polygon((22 45,22 58,28 58,28 42,25 42,25 45,22 45))')",srid);
    EXEC SQL execute immediate :sql_stmt;

    sprintf(sql_stmt,
            "INSERT INTO sensitive_areas VALUES(2, "
            "'%d polygon((22 18,28 18,28 12,22 12,22 18))')", srid);
    EXEC SQL execute immediate :sql_stmt;

    sprintf(sql_stmt,
            "INSERT INTO sensitive_areas VALUES(3, "
            "'%d polygon((42 18,48 18,48 13,42 13,42 18))')", srid);
    EXEC SQL execute immediate :sql_stmt;

    /* Populate the query polygon point array */

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

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

    /* Convert the polygon to an ESRI shape. */

    geom_to_shape (&geom, &max_alloced, &query_shape_len, &query_shape_buf);

    /* Create host variable for shape representation */

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

    if ((rc = ifx_var_setdata(&query_shape,query_shape_buf,query_shape_len)) < 0)
    {
        fprintf(stderr, "Error calling ifx_var_setdata.");
        exit(1);
    }

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

    /* Create the SQL expression. */

    sprintf(sql_stmt,
            "SELECT SE_AsShape(zone) "
            "FROM sensitive_areas WHERE "
            "SE_EnvelopesIntersect(zone,SE_PolyFromShape(?,%d))", srid);

    /* Prepare the SQL statement. */

    EXEC SQL prepare sel_stmt from :sql_stmt;

    /* Declare cursor for the SELECT statement. */

    EXEC SQL declare sel_curs cursor for sel_stmt;

    /* Allocate a statement descriptor. */

    EXEC SQL allocate descriptor 'sel_desc';

    /* Set the number of input parameters */

    n = 1;
    EXEC SQL set descriptor 'sel_desc' COUNT = :n;

    /* Bind the query shape to the input parameter */

    typ = SQLUDTVAR;
    xid = XID_LVARCHAR;

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

    /* Open cursor for the SELECT statement. */

    EXEC SQL open sel_curs using sql descriptor 'sel_desc';

    /* Fetch each polygon within the display window and display it. */

    while (1)
    {
        EXEC SQL fetch sel_curs into :fetched_shape;

        if (sqlca.sqlcode == SQLNOTFOUND)
            break;

        printf("Fetched row.\n");

        if ((fetched_shape_len = ifx_var_getlen(&fetched_shape)) == (int) NULL)
        {
            fprintf(stderr, "Error calling ifx_var_getlen.");
            exit(1);
        }

        if ((fetched_shape_buf = ifx_var_getdata(&fetched_shape)) == NULL)
        {
            fprintf(stderr, "Error calling ifx_var_getdata.");
            exit(1);
        }

        draw_polygon(fetched_shape_buf);

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

    /* Close the result set cursor */

    EXEC SQL close sel_curs;

    /* Free resources associated with SELECT statement */

    EXEC SQL deallocate descriptor 'sel_desc';
    EXEC SQL free sel_curs;
    EXEC SQL free sel_stmt;

    /* Disconnect from the database */

    EXEC SQL disconnect current;

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

/*
 * Local function draw polygon
 *
 */

void draw_polygon (char *shape_buf)
{
    Geometry geom = {0};
    
    /* Convert the incoming well-known binary byte stream
     * into a Geometry data structure.  */
    shape_to_geom (shape_buf, &geom);

    /* The remainder of this function is left as an exercise for the reader */
}