static void
vknn_init_context (VKnnContextPtr ctx, const char *table, const char *column,
gaiaGeomCollPtr geom, int max_items,
sqlite3_stmt * stmt_dist, sqlite3_stmt * stmt_rect)
{
/* initializing a KNN context */
int i;
if (ctx == NULL)
return;
vknn_reset_context (ctx);
i = strlen (table);
ctx->table_name = malloc (i + 1);
strcpy (ctx->table_name, table);
i = strlen (column);
ctx->column_name = malloc (i + 1);
strcpy (ctx->column_name, column);
ctx->minx = geom->MinX;
ctx->maxx = geom->MaxX;
ctx->miny = geom->MinY;
ctx->maxy = geom->MaxY;
gaiaToSpatiaLiteBlobWkb (geom, &(ctx->blob), &(ctx->blob_size));
ctx->max_items = max_items;
ctx->stmt_dist = stmt_dist;
ctx->stmt_rect = stmt_rect;
ctx->knn_array = malloc (sizeof (VKnnItem) * max_items);
for (i = 0; i < max_items; i++)
{
/* initializing the KNN sorted array */
VKnnItemPtr item = ctx->knn_array + i;
item->rowid = 0;
item->dist = DBL_MAX;
}
ctx->max_dist = -DBL_MAX;
ctx->curr_items = 0;
ctx->tree = NULL;
ctx->levels = 0;
ctx->curr_level = -1;
}
int
main (int argc, char *argv[])
{
int ret;
sqlite3 *handle;
sqlite3_stmt *stmt;
char sql[256];
char *err_msg = NULL;
double x;
double y;
int pk;
int ix;
int iy;
gaiaGeomCollPtr geo = NULL;
unsigned char *blob;
int blob_size;
int i;
char **results;
int n_rows;
int n_columns;
char *count;
clock_t t0;
clock_t t1;
void *cache;
if (argc != 2)
{
fprintf (stderr, "usage: %s test_db_path\n", argv[0]);
return -1;
}
/*
trying to connect the test DB:
- this demo is intended to create a new, empty database
*/
ret = sqlite3_open_v2 (argv[1], &handle,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (ret != SQLITE_OK)
{
printf ("cannot open '%s': %s\n", argv[1], sqlite3_errmsg (handle));
sqlite3_close (handle);
return -1;
}
cache = spatialite_alloc_connection ();
spatialite_init_ex (handle, cache, 0);
/* showing the SQLite version */
printf ("SQLite version: %s\n", sqlite3_libversion ());
/* showing the SpatiaLite version */
printf ("SpatiaLite version: %s\n", spatialite_version ());
printf ("\n\n");
/*
we are supposing this one is an empty database,
so we have to create the Spatial Metadata
*/
strcpy (sql, "SELECT InitSpatialMetadata(1)");
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("InitSpatialMetadata() error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
/*
now we can create the test table
for simplicity we'll define only one column, the primary key
*/
strcpy (sql, "CREATE TABLE test (");
strcat (sql, "PK INTEGER NOT NULL PRIMARY KEY)");
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("CREATE TABLE 'test' error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
/*
... we'll add a Geometry column of POINT type to the test table
*/
strcpy (sql, "SELECT AddGeometryColumn('test', 'geom', 3003, 'POINT', 2)");
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("AddGeometryColumn() error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
/*
and finally we'll enable this geo-column to have a Spatial Index based on R*Tree
*/
strcpy (sql, "SELECT CreateSpatialIndex('test', 'geom')");
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("CreateSpatialIndex() error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
printf
("\nnow we are going to insert 1 million POINTs; wait, please ...\n\n");
t0 = clock ();
/*
beginning a transaction
*** this step is absolutely critical ***
the SQLite engine is a TRANSACTIONAL one
the whole batch of INSERTs has to be performed as an unique transaction,
otherwise performance will be surely very poor
*/
strcpy (sql, "BEGIN");
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("BEGIN error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
/*
preparing to populate the test table
we'll use a Prepared Statement we can reuse in order to insert each row
*/
strcpy (sql, "INSERT INTO test (pk, geom) VALUES (?, ?)");
ret = sqlite3_prepare_v2 (handle, sql, strlen (sql), &stmt, NULL);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("INSERT SQL error: %s\n", sqlite3_errmsg (handle));
goto abort;
}
pk = 0;
for (ix = 0; ix < 1000; ix++)
{
x = 1000000.0 + (ix * 10.0);
for (iy = 0; iy < 1000; iy++)
{
/* this double loop will insert 1 million rows into the the test table */
y = 4000000.0 + (iy * 10.0);
pk++;
if ((pk % 25000) == 0)
{
t1 = clock ();
printf ("insert row: %d\t\t[elapsed time: %1.3f]\n",
pk, (double) (t1 - t0) / CLOCKS_PER_SEC);
}
/* preparing the geometry to insert */
geo = gaiaAllocGeomColl ();
geo->Srid = 3003;
gaiaAddPointToGeomColl (geo, x, y);
/* transforming this geometry into the SpatiaLite BLOB format */
gaiaToSpatiaLiteBlobWkb (geo, &blob, &blob_size);
/* we can now destroy the geometry object */
gaiaFreeGeomColl (geo);
/* resetting Prepared Statement and bindings */
sqlite3_reset (stmt);
sqlite3_clear_bindings (stmt);
/* binding parameters to Prepared Statement */
sqlite3_bind_int64 (stmt, 1, pk);
sqlite3_bind_blob (stmt, 2, blob, blob_size, free);
/* performing actual row insert */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
;
else
{
/* an unexpected error occurred */
printf ("sqlite3_step() error: %s\n",
sqlite3_errmsg (handle));
sqlite3_finalize (stmt);
goto abort;
}
}
}
/* we have now to finalize the query [memory cleanup] */
sqlite3_finalize (stmt);
/*
committing the transaction
*** this step is absolutely critical ***
if we don't confirm the still pending transaction,
any update will be lost
*/
strcpy (sql, "COMMIT");
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("COMMIT error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
/*
now we'll optimize the table
*/
strcpy (sql, "ANALYZE test");
ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("ANALYZE error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
for (ix = 0; ix < 3; ix++)
{
printf ("\nperforming test#%d - not using Spatial Index\n", ix);
/*
now we'll perform the spatial query WITHOUT using the Spatial Index
we'll loop 3 times in order to avoid buffering-caching side effects
*/
strcpy (sql, "SELECT Count(*) FROM test ");
strcat (sql, "WHERE MbrWithin(geom, BuildMbr(");
strcat (sql, "1000400.5, 4000400.5, ");
strcat (sql, "1000450.5, 4000450.5))");
t0 = clock ();
ret = sqlite3_get_table (handle, sql, &results, &n_rows, &n_columns,
&err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("NoSpatialIndex SQL error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
count = "";
for (i = 1; i <= n_rows; i++)
{
count = results[(i * n_columns) + 0];
}
t1 = clock ();
printf ("Count(*) = %d\t\t[elapsed time: %1.4f]\n", atoi (count),
(double) (t1 - t0) / CLOCKS_PER_SEC);
/* we can now free the table results */
sqlite3_free_table (results);
}
for (ix = 0; ix < 3; ix++)
{
printf ("\nperforming test#%d - using the R*Tree Spatial Index\n",
ix);
/*
now we'll perform the spatial query USING the R*Tree Spatial Index
we'll loop 3 times in order to avoid buffering-caching side effects
*/
strcpy (sql, "SELECT Count(*) FROM test ");
strcat (sql, "WHERE MbrWithin(geom, BuildMbr(");
strcat (sql, "1000400.5, 4000400.5, ");
strcat (sql, "1000450.5, 4000450.5)) AND ROWID IN (");
strcat (sql, "SELECT pkid FROM idx_test_geom WHERE ");
strcat (sql, "xmin > 1000400.5 AND ");
strcat (sql, "xmax < 1000450.5 AND ");
strcat (sql, "ymin > 4000400.5 AND ");
strcat (sql, "ymax < 4000450.5)");
/*
YES, this query is a very unhappy one
the idea is simply to simulate exactly the same conditions as above
*/
t0 = clock ();
ret = sqlite3_get_table (handle, sql, &results, &n_rows, &n_columns,
&err_msg);
if (ret != SQLITE_OK)
{
/* an error occurred */
printf ("SpatialIndex SQL error: %s\n", err_msg);
sqlite3_free (err_msg);
goto abort;
}
count = "";
for (i = 1; i <= n_rows; i++)
{
count = results[(i * n_columns) + 0];
}
t1 = clock ();
printf ("Count(*) = %d\t\t[elapsed time: %1.4f]\n", atoi (count),
(double) (t1 - t0) / CLOCKS_PER_SEC);
/* we can now free the table results */
sqlite3_free_table (results);
}
/* disconnecting the test DB */
ret = sqlite3_close (handle);
if (ret != SQLITE_OK)
{
printf ("close() error: %s\n", sqlite3_errmsg (handle));
return -1;
}
spatialite_cleanup_ex (cache);
printf ("\n\nsample successfully terminated\n");
return 0;
abort:
sqlite3_close (handle);
spatialite_cleanup_ex (cache);
spatialite_shutdown();
return -1;
}
static int
vbbox_column (sqlite3_vtab_cursor * pCursor, sqlite3_context * pContext,
int column)
{
/* fetching value for the Nth column */
VirtualBBoxCursorPtr cursor = (VirtualBBoxCursorPtr) pCursor;
SqliteValuePtr value;
int icol = 0;
int icol_ok = 0;
if (column == 0)
{
/* the BBox Geometry */
if (cursor->pVtab->BBoxGeom == NULL)
sqlite3_result_null (pContext);
else
{
unsigned char *blob;
int size;
gaiaToSpatiaLiteBlobWkb (cursor->pVtab->BBoxGeom, &blob, &size);
sqlite3_result_blob (pContext, blob, size, free);
}
return SQLITE_OK;
}
while (icol < cursor->pVtab->nColumns)
{
if (*(cursor->pVtab->Visible + icol) != 'Y')
{
icol++;
continue;
}
icol_ok++;
if (icol_ok == column)
{
value = *(cursor->pVtab->Value + icol);
switch (value->Type)
{
case SQLITE_INTEGER:
sqlite3_result_int64 (pContext, value->IntValue);
break;
case SQLITE_FLOAT:
sqlite3_result_double (pContext, value->DoubleValue);
break;
case SQLITE_TEXT:
sqlite3_result_text (pContext, value->Text, value->Size,
SQLITE_STATIC);
break;
case SQLITE_BLOB:
sqlite3_result_blob (pContext, value->Blob, value->Size,
SQLITE_STATIC);
break;
default:
sqlite3_result_null (pContext);
break;
};
return SQLITE_OK;
}
icol++;
}
sqlite3_result_null (pContext);
return SQLITE_OK;
}
static void
check_end2_fid (struct gml_params *params, const char *el)
{
/* checking if the FID tag ends here */
int ret;
int fld = 1;
struct gml_column *col;
if (strcasecmp (params->fid_tag, el) == 0)
{
params->is_fid = 0;
if (params->stmt == NULL)
return;
/* resetting the SQL prepared statement */
sqlite3_reset (params->stmt);
sqlite3_clear_bindings (params->stmt);
col = params->first;
while (col)
{
/* binding ordinary column values */
switch (col->value_type)
{
case VALUE_INT:
sqlite3_bind_int64 (params->stmt, fld, col->int_value);
break;
case VALUE_FLOAT:
sqlite3_bind_double (params->stmt, fld, col->dbl_value);
break;
case VALUE_TEXT:
sqlite3_bind_text (params->stmt, fld, col->txt_value,
strlen (col->txt_value),
SQLITE_STATIC);
break;
default:
sqlite3_bind_null (params->stmt, fld);
break;
};
fld++;
col = col->next;
}
/* setting up the latest Polygon (if any) */
polygon_set_up (params);
/* binding Geometry BLOB value */
if (params->geometry == NULL)
sqlite3_bind_null (params->stmt, fld);
else
{
unsigned char *blob;
int blob_size;
params->geometry->Srid = params->srid;
params->geometry->DeclaredType = params->declared_type;
gaiaToSpatiaLiteBlobWkb (params->geometry, &blob, &blob_size);
sqlite3_bind_blob (params->stmt, fld, blob, blob_size, free);
}
/* performing INSERT INTO */
ret = sqlite3_step (params->stmt);
clean_values (params);
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
return;
fprintf (stderr, "sqlite3_step() error: INSERT INTO\n");
sqlite3_finalize (params->stmt);
params->stmt = NULL;
}
}
GAIAGEO_DECLARE int
gaiaExportDxf (gaiaDxfWriterPtr dxf, sqlite3 * db_handle,
const char *sql, const char *layer_col_name,
const char *geom_col_name, const char *label_col_name,
const char *text_height_col_name,
const char *text_rotation_col_name, gaiaGeomCollPtr geom_filter)
{
/* exporting a complex DXF by executing an arbitrary SQL query */
sqlite3_stmt *stmt = NULL;
int ret;
int params;
int first_row = 1;
int layer_col = -1;
int geom_col = -1;
int label_col = -1;
int text_height_col = -1;
int text_rotation_col = -1;
int i;
unsigned char *p_blob;
const unsigned char *blob;
int len;
const char *layer;
const char *label = NULL;
gaiaGeomCollPtr geom;
gaiaDxfExportPtr aux = NULL;
gaiaDxfExportLayerPtr lyr;
if (dxf == NULL)
return 0;
if (dxf->error)
return 0;
if (dxf->out == NULL)
return 0;
if (db_handle == NULL)
return 0;
if (sql == NULL)
return 0;
if (layer_col_name == NULL)
return 0;
if (geom_col_name == NULL)
return 0;
/* attempting to create the SQL prepared statement */
ret = sqlite3_prepare_v2 (db_handle, sql, strlen (sql), &stmt, NULL);
if (ret != SQLITE_OK)
{
spatialite_e ("exportDXF - CREATE STATEMENT error: %s\n",
sqlite3_errmsg (db_handle));
goto stop;
}
params = sqlite3_bind_parameter_count (stmt);
if (params > 0 && geom_filter != NULL)
{
/* parameter binding - Spatial Filter */
sqlite3_reset (stmt);
sqlite3_clear_bindings (stmt);
for (i = 1; i <= params; i++)
{
gaiaToSpatiaLiteBlobWkb (geom_filter, &p_blob, &len);
ret = sqlite3_bind_blob (stmt, i, p_blob, len, free);
if (ret != SQLITE_OK)
{
spatialite_e ("exportDXF - parameter BIND error: %s\n",
sqlite3_errmsg (db_handle));
goto stop;
}
}
}
/* pass #1 - sniffing the result set */
while (1)
{
/* scrolling the result set rows */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE)
break; /* end of result set */
if (ret == SQLITE_ROW)
{
if (first_row)
{
/* this one is the first row of the resultset */
for (i = 0; i < sqlite3_column_count (stmt); i++)
{
/* attempting to identify the resultset columns */
if (strcasecmp
(layer_col_name,
sqlite3_column_name (stmt, i)) == 0)
layer_col = i;
if (strcasecmp
(geom_col_name,
sqlite3_column_name (stmt, i)) == 0)
geom_col = i;
if (label_col_name != NULL)
{
if (strcasecmp
(label_col_name,
sqlite3_column_name (stmt, i)) == 0)
label_col = i;
}
if (text_height_col_name != NULL)
{
if (strcasecmp
(text_height_col_name,
sqlite3_column_name (stmt, i)) == 0)
text_height_col = i;
}
if (text_rotation_col_name != NULL)
{
if (strcasecmp
(text_rotation_col_name,
sqlite3_column_name (stmt, i)) == 0)
text_rotation_col = i;
}
}
if (layer_col < 0)
{
spatialite_e
("exportDXF - Layer Column not found into the resultset\n");
goto stop;
}
if (geom_col < 0)
{
spatialite_e
("exportDXF - Geometry Column not found into the resultset\n");
goto stop;
}
first_row = 0;
aux = alloc_aux_exporter ();
}
layer = (const char *) sqlite3_column_text (stmt, layer_col);
blob = sqlite3_column_blob (stmt, geom_col);
len = sqlite3_column_bytes (stmt, geom_col);
geom = gaiaFromSpatiaLiteBlobWkb (blob, len);
if (geom)
{
update_aux_exporter (aux, layer, geom);
gaiaFreeGeomColl (geom);
}
}
}
/* pass #2 - exporting the DXF file */
gaiaDxfWriteHeader (dxf, aux->minx, aux->miny, 0, aux->maxx, aux->maxy, 0);
gaiaDxfWriteTables (dxf);
lyr = aux->first;
while (lyr != NULL)
{
gaiaDxfWriteLayer (dxf, lyr->layer_name);
lyr = lyr->next;
}
gaiaDxfWriteEndSection (dxf);
gaiaDxfWriteEntities (dxf);
sqlite3_reset (stmt);
while (1)
{
/* scrolling the result set rows */
int ival;
double height = 10.0;
double rotation = 0.0;
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE)
break; /* end of result set */
if (ret == SQLITE_ROW)
{
layer = (const char *) sqlite3_column_text (stmt, layer_col);
if (label_col >= 0)
label =
(const char *) sqlite3_column_text (stmt, label_col);
if (text_height_col >= 0)
{
if (sqlite3_column_type (stmt, text_height_col) ==
SQLITE_INTEGER)
{
ival = sqlite3_column_int (stmt, text_height_col);
height = ival;
}
if (sqlite3_column_type (stmt, text_height_col) ==
SQLITE_FLOAT)
height =
sqlite3_column_double (stmt, text_height_col);
}
if (text_rotation_col >= 0)
{
if (sqlite3_column_type (stmt, text_rotation_col) ==
SQLITE_INTEGER)
{
ival = sqlite3_column_int (stmt, text_rotation_col);
rotation = ival;
}
if (sqlite3_column_type (stmt, text_height_col) ==
SQLITE_FLOAT)
rotation =
sqlite3_column_double (stmt, text_rotation_col);
}
blob = sqlite3_column_blob (stmt, geom_col);
len = sqlite3_column_bytes (stmt, geom_col);
geom = gaiaFromSpatiaLiteBlobWkb (blob, len);
if (geom)
{
gaiaDxfWriteGeometry (dxf, layer, label, height, rotation,
geom);
gaiaFreeGeomColl (geom);
}
}
}
gaiaDxfWriteEndSection (dxf);
gaiaDxfWriteFooter (dxf);
sqlite3_finalize (stmt);
if (aux != NULL)
destroy_aux_exporter (aux);
return dxf->count;
stop:
if (stmt != NULL)
sqlite3_finalize (stmt);
if (aux != NULL)
destroy_aux_exporter (aux);
return 0;
}
