Language bindings: C/C++ 2011 January
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL); if (ret != SQLITE_OK)
{
fprintf (stderr, "cannot open 'test-db.sqlite': %s\n", sqlite3_errmsg (db_handle));
sqlite3_close (db_handle); db_handle = NULL;
return -1; }
#ifdef SPATIALITE_EXTENSION /*
* loading SpatiaLite as an extension */
sqlite3_enable_load_extension (db_handle, 1);
strcpy (sql, "SELECT load_extension('libspatialite.so')"); ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "load_extension() error: %s\n", err_msg); sqlite3_free (err_msg);
return 0; }
fprintf(stderr, "\n\n**** SpatiaLite loaded as an extension ***\n\n"); #endif
/* reporting version infos */ #ifndef SPATIALITE_EXTENSION /*
* please note well:
* this process is physically linked to libspatialite * so we can directly call any SpatiaLite's API function */
fprintf (stderr, "SQLite version: %s\n", sqlite3_libversion()); fprintf (stderr, "SpatiaLite version: %s\n", spatialite_version()); #else
/*
* please note well:
* this process isn't physically linked to libspatialite * because we loaded the library as an extension
*
* so we aren't enabled to directly call any SpatiaLite's API functions * we simply can access SpatiaLite indirectly via SQL statements
*/
strcpy (sql, "SELECT sqlite_version()"); ret =
sqlite3_get_table (db_handle, sql, &results, &rows, &columns, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg); goto stop; } if (rows < 1) { fprintf (stderr,
"Unexpected error: sqlite_version() not found ??????\n"); goto stop;
} else {
for (i = 1; i <= rows; i++) {
fprintf (stderr, "SQLite version: %s\n", results[(i * columns) + 0]);
} }
sqlite3_free_table (results);
strcpy (sql, "SELECT spatialite_version()"); ret =
sqlite3_get_table (db_handle, sql, &results, &rows, &columns, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg); goto stop; } if (rows < 1) { fprintf (stderr,
"Unexpected error: spatialite_version() not found ??????\n"); goto stop;
} else {
for (i = 1; i <= rows; i++) {
sqlite3_free_table (results);
#endif /* SpatiaLite as an extension */
/* initializing SpatiaLite's metadata tables */ strcpy (sql, "SELECT InitSpatialMetadata()");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "InitSpatialMetadata() error: %s\n", err_msg); sqlite3_free (err_msg);
return 0; }
/* creating a POINT table */
strcpy (sql, "CREATE TABLE test_pt (");
strcat (sql, "id INTEGER NOT NULL PRIMARY KEY,"); strcat (sql, "name TEXT NOT NULL)");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* creating a POINT Geometry column */
strcpy (sql, "SELECT AddGeometryColumn('test_pt', "); strcat (sql, "'geom', 4326, 'POINT', 'XY')");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* creating a LINESTRING table */
strcpy (sql, "CREATE TABLE test_ln (");
strcat (sql, "id INTEGER NOT NULL PRIMARY KEY,"); strcat (sql, "name TEXT NOT NULL)");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* creating a LINESTRING Geometry column */
strcpy (sql, "SELECT AddGeometryColumn('test_ln', "); strcat (sql, "'geom', 4326, 'LINESTRING', 'XY')");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* creating a POLYGON table */
strcpy (sql, "CREATE TABLE test_pg (");
strcat (sql, "id INTEGER NOT NULL PRIMARY KEY,"); strcat (sql, "name TEXT NOT NULL)");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* creating a POLYGON Geometry column */
strcpy (sql, "SELECT AddGeometryColumn('test_pg', "); strcat (sql, "'geom', 4326, 'POLYGON', 'XY')");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook/html/c.html[01/02/2011 21:48:48]
goto stop; }
/*
* inserting some POINTs
* please note well: SQLite is ACID and Transactional * so (to get best performance) the whole insert cycle * will be handled as a single TRANSACTION
*/
ret = sqlite3_exec (db_handle, "BEGIN", NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
for (i = 0; i < 100000; i++) {
/* for POINTs we'll use full text sql statements */
strcpy (sql, "INSERT INTO test_pt (id, name, geom) VALUES ("); sprintf (sql2, "%d, 'test POINT #%d'", i + 1, i + 1);
strcat (sql, sql2);
sprintf (sql2, ", GeomFromText('POINT(%1.6f %1.6f)'", i / 1000.0, i / 1000.0);
strcat (sql, sql2);
strcat (sql, ", 4326))");
ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
}
ret = sqlite3_exec (db_handle, "COMMIT", NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* checking POINTs */
strcpy (sql, "SELECT DISTINCT Count(*), ST_GeometryType(geom), "); strcat (sql, "ST_Srid(geom) FROM test_pt");
ret =
sqlite3_get_table (db_handle, sql, &results, &rows, &columns, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
if (rows < 1) {
fprintf (stderr, "Unexpected error: ZERO POINTs found ??????\n"); goto stop;
} else {
for (i = 1; i <= rows; i++) {
cnt = atoi (results[(i * columns) + 0]); type = results[(i * columns) + 1];
srid = atoi (results[(i * columns) + 2]);
fprintf (stderr, "Inserted %d entities of type %s SRID=%d\n", cnt, type, srid);
} }
sqlite3_free_table (results); /*
* inserting some LINESTRINGs
* this time we'll use a Prepared Statement */
strcpy (sql, "INSERT INTO test_ln (id, name, geom) "); strcat (sql, "VALUES (?, ?, GeomFromText(?, 4326))");
ret = sqlite3_prepare_v2 (db_handle, sql, strlen (sql), &stmt, NULL); if (ret != SQLITE_OK)
http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook/html/c.html[01/02/2011 21:48:48]
ret = sqlite3_exec (db_handle, "BEGIN", NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
for (i = 0; i < 100000; i++) {
/* setting up values / binding */
sprintf (name, "test LINESTRING #%d", i + 1); strcpy (geom, "LINESTRING(");
if ((i % 2) == 1) {
/* odd row: five points */
strcat (geom, "-180.0 -90.0, "); sprintf (sql2, "%1.6f %1.6f, ", -10.0 - (i / 1000.0), -10.0 - (i / 1000.0)); strcat (geom, sql2); sprintf (sql2, "%1.6f %1.6f, ", -10.0 - (i / 1000.0), 10.0 + (i / 1000.0)); strcat (geom, sql2); sprintf (sql2, "%1.6f %1.6f, ", 10.0 + (i / 1000.0), 10.0 + (i / 1000.0)); strcat (geom, sql2); strcat (geom, "180.0 90.0"); } else {
/* even row: two points */
sprintf (sql2, "%1.6f %1.6f, ", -10.0 - (i / 1000.0), -10.0 - (i / 1000.0)); strcat (geom, sql2); sprintf (sql2, "%1.6f %1.6f, ", 10.0 + (i / 1000.0), 10.0 + (i / 1000.0)); strcat (geom, sql2); } strcat (geom, ")"); sqlite3_reset (stmt); sqlite3_clear_bindings (stmt); sqlite3_bind_int (stmt, 1, i + 1);
sqlite3_bind_text (stmt, 2, name, strlen (name), SQLITE_STATIC); sqlite3_bind_text (stmt, 3, geom, strlen (geom), SQLITE_STATIC); /* performing INSERT INTO */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE || ret == SQLITE_ROW) continue;
fprintf (stderr, "sqlite3_step() error: [%s]\n", sqlite3_errmsg (db_handle));
goto stop; }
sqlite3_finalize (stmt);
ret = sqlite3_exec (db_handle, "COMMIT", NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* checking LINESTRINGs */
strcpy (sql, "SELECT DISTINCT Count(*), ST_GeometryType(geom), "); strcat (sql, "ST_Srid(geom) FROM test_ln");
ret =
sqlite3_get_table (db_handle, sql, &results, &rows, &columns, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
if (rows < 1) {
fprintf (stderr, "Unexpected error: ZERO LINESTRINGs found ??????\n"); goto stop;
else {
for (i = 1; i <= rows; i++) {
cnt = atoi (results[(i * columns) + 0]); type = results[(i * columns) + 1];
srid = atoi (results[(i * columns) + 2]);
fprintf (stderr, "Inserted %d entities of type %s SRID=%d\n", cnt, type, srid);
} }
sqlite3_free_table (results); /*
* inserting some POLYGONs
* this time too we'll use a Prepared Statement */
strcpy (sql, "INSERT INTO test_pg (id, name, geom) "); strcat (sql, "VALUES (?, ?, GeomFromText(?, 4326))");
ret = sqlite3_prepare_v2 (db_handle, sql, strlen (sql), &stmt, NULL); if (ret != SQLITE_OK)
{
fprintf (stderr, "SQL error: %s\n%s\n", sql, sqlite3_errmsg (db_handle));
goto stop; }
ret = sqlite3_exec (db_handle, "BEGIN", NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
for (i = 0; i < 100000; i++) {
/* setting up values / binding */
sprintf (name, "test POLYGON #%d", i + 1); strcpy (geom, "POLYGON((");
sprintf (sql2, "%1.6f %1.6f, ", -10.0 - (i / 1000.0), -10.0 - (i / 1000.0)); strcat (geom, sql2); sprintf (sql2, "%1.6f %1.6f, ", 10.0 - (i / 1000.0), -10.0 - (i / 1000.0)); strcat (geom, sql2); sprintf (sql2, "%1.6f %1.6f, ", 10.0 + (i / 1000.0), 10.0 + (i / 1000.0)); strcat (geom, sql2); sprintf (sql2, "%1.6f %1.6f, ", -10.0 - (i / 1000.0), 10.0 - (i / 1000.0)); strcat (geom, sql2); sprintf (sql2, "%1.6f %1.6f", -10.0 - (i / 1000.0), -10.0 - (i / 1000.0)); strcat (geom, sql2); strcat (geom, "))"); sqlite3_reset (stmt); sqlite3_clear_bindings (stmt); sqlite3_bind_int (stmt, 1, i + 1);
sqlite3_bind_text (stmt, 2, name, strlen (name), SQLITE_STATIC); sqlite3_bind_text (stmt, 3, geom, strlen (geom), SQLITE_STATIC); /* performing INSERT INTO */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE || ret == SQLITE_ROW) continue;
fprintf (stderr, "sqlite3_step() error: [%s]\n", sqlite3_errmsg (db_handle));
goto stop; }
sqlite3_finalize (stmt);
ret = sqlite3_exec (db_handle, "COMMIT", NULL, NULL, &err_msg); if (ret != SQLITE_OK)
{
fprintf (stderr, "Error: %s\n", err_msg); sqlite3_free (err_msg);
goto stop; }
/* checking POLYGONs */
strcpy (sql, "SELECT DISTINCT Count(*), ST_GeometryType(geom), "); strcat (sql, "ST_Srid(geom) FROM test_pg");
sqlite3_free (err_msg); goto stop;
}
if (rows < 1) {
fprintf (stderr, "Unexpected error: ZERO POLYGONs found ??????\n"); goto stop;
} else {
for (i = 1; i <= rows; i++) {
cnt = atoi (results[(i * columns) + 0]); type = results[(i * columns) + 1];
srid = atoi (results[(i * columns) + 2]);
fprintf (stderr, "Inserted %d entities of type %s SRID=%d\n", cnt, type, srid);
} }
sqlite3_free_table (results); /* closing the DB connection */ stop:
sqlite3_close (db_handle); return 0;
}
Compiling and linking SpatiaLite comes in different flavors:
libspatialite is the standard library intended to be loaded as an extension.
libspatialite-amalgamation is a self-standing complete SQL-engine supporting an internal private copy of SQLite.
If your principal interest is developing easy-to-be-deployed, stand-alone C/C++ applications, then using the statically linked libspatialite-amalgamation will surely be a desirable option.
Anyway nothing prevents you from using a different layout based on dynamically linked shared libraries. And a third option is supported as well: you can completely avoid using SQLite and SpatiaLite libraries. Both them simply are one single monolithic source file: so you can directly compile any required source in a single pass (this will obviously generate a statically linked executable).
Get a quick glance to the spatialite_sample.c source code; you easily notice that two conditional macros are
extensively used:
SPATIALITE_AMALGAMATION is used to determine if we are using separate libsqlite and libspatialite,
or if we are using the all-in-one libspatialite-amalgamation
as you can notice the only difference affects inclusion of header files:
when using amalgamation you cannot use #include <sqlite3.h> (because this one is the standard
system sqlite header file).
you are required using #include <spatialite/sqlite3.h> (a purposely modified version supporting amalgamation).
SPATIALITE_EXTENSION is used to determine if we are using an hard-linked libspatialite or if we intend
loading this library as an extension:
please note: loading libspatialite as an extension excludes using the amalgamation, because libsqlite is already linked to the executable in this case.
appropriate, in the most flexible way.
The following practical examples are based on the standard GNU gcc compiler on Linux.