Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.



SpatiaLite logo

Language bindings: C/C++

2011 January 28

Back to Language Bindings


C/C++ sample program

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

#ifdef SPATIALITE_AMALGAMATION
    #include <spatialite/sqlite3.h>
#else
    #include <sqlite3.h>
#endif


#ifndef SPATIALITE_EXTENSION
    #include <spatialite.h>
#endif

int
main (void)
{
    sqlite3 *db_handle;
    sqlite3_stmt *stmt;
    int ret;
    char *err_msg = NULL;
    char sql[2048];
    char sql2[1024];
    int i;
    char **results;
    int rows;
    int columns;
    int cnt;
    const char *type;
    int srid;
    char name[1024];
    char geom[2048];

#ifndef SPATIALITE_EXTENSION
    /*
     * initializing SpatiaLite-Amalgamation
     *
     * any C/C++ source requires this to be performed
     * for each connection before invoking the first
     * SQLite/SpatiaLite call
     */
    spatialite_init (0);
    fprintf(stderr, "\n\n******* hard-linked libspatialite ********\n\n");
#endif

/* creating/connecting the test_db */
    ret =
    sqlite3_open_v2 ("test-db.sqlite", &db_handle,
         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++)
       {
        fprintf (stderr, "SpatiaLite version: %s\n",
         results[(i * columns) + 0]);
       }
      }
    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);
      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)
      {
      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 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");
    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 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.
You can now build spatialite_sample.c in many different flavors, simply defining these two macros as appropriate, in the most flexible way.
The following practical examples are based on the standard GNU gcc compiler on Linux.




Loading SpatiaLite as an extension


gcc -Wall -Wextra -Wunused -DSPATIALITE_EXTENSION \
  spatialite_sample.c -o spatialite_sample -lsqlite3
Some interesting points to be noted:
This basic approach is exactely the same supported by any other language binding (Java, Python, PHP ...).
But using C/C++ you get get more flexible configuration options: please, see the following examples.




Dynamically linking libspatialite + libsqlite


gcc -Wall -Wextra -Wunused -I/usr/local/include \
  spatialite_sample.c -o spatialite_sample
  -L/usr/local/lib -lspatialite -lsqlite3
Some interesting points to be noted:


Dynamically linking libspatialite-amalgamation


gcc -Wall -Wextra -Wunused -DSPATIALITE_AMALGAMATION \
  -I/usr/local/include spatialite_sample.c \
  -o spatialite_sample \
  -L/usr/local/lib -lspatialite
Some interesting points to be noted:


Statically linking libspatialite + libsqlite


gcc -Wall -Wextra -Wunused -I/usr/local/include \
  spatialite_sample.c -o spatialite_sample \
  /usr/local/lib/libspatialite.a \
  /usr/lib/libsqlite.a \
  /usr/lib/libgeos_c.a \
  /usr/lib/libgeos.a \
  /usr/lib/libproj.a \
  -lstdc++ -ldl -lpthread -lm
Some interesting points to be noted:


Statically linking libspatialite-amalgamation


gcc -Wall -Wextra -Wunused -DSPATIALITE_AMALGAMATION \
  -I/usr/local/include spatialite_sample.c \
  -o spatialite_sample \
  /usr/local/lib/libspatialite.a \
  /usr/lib/libgeos_c.a \
  /usr/lib/libgeos.a \
  /usr/lib/libproj.a \
  -lstdc++ -ldl -lpthread -lm
Exactly the same as above; simply this time libsqlite is no longer required, because this is directly supported by the private internal copy included within the amalgamation itself.

Please note: different platforms, different system libraries, different file system layouts.
The gcc compiler is available on quite every platform (this including Microsoft Windows, of course).
Unhappily, each platform has its own specific idiosyncrasies.

MacOsX:
gcc -Wall -Wextra -Wunused -DSPATIALITE_AMALGAMATION \
  -I/usr/local/include -I/opt/local/include
  spatialite_sample.c -o spatialite_sample \
  /usr/local/lib/libspatialite.a \
  /opt/local/lib/libgeos_c.a \
  /opt/local/lib/libgeos.a \
  /opt/local/lib/libproj.a \
  /opt/local/lib/libiconv.a \
  /opt/local/lib/libcharset.a \
  -lstdc++ -ldl -lpthread -lm
MacOsX basically is an Unix derivative (most precisely a FreeBSD derivative).
The wonderful MacPorts fully supports standard open source libraries (in our case libgeos, libproj and libiconv):
  • MacPorts header files will be installed on /opt/local/include
  • MacPorts libraries will be installed on /opt/local/lib
  • Both libiconv and libcharset are directly integrated within the C run-time on Linux.
    But on MacOsX you are required to explicitly link these libraries.
Windows [MinGW + MSYS]:
gcc -Wall -Wextra -Wunused -DSPATIALITE_AMALGAMATION \
  -I/usr/local/include spatialite_sample.c \
  -o spatialite_sample.exe \
  /usr/local/lib/libspatialite.a \
  /usr/lib/libgeos_c.a \
  /usr/lib/libgeos.a \
  /usr/lib/libproj.a \
  /usr/local/lib/libiconv.a \
  -lstdc++ -lm
Windows and Unix are strongly different:
  • as we have already seen for MacOsX explicitly linking libiconv is required for Windows as well (but not libcharset).
  • -ldl and -lpthread aren't required at all
    (simply because Windows dynamic loader and multithreading are completely different from the corresponding Unix implementations, and are directly supported by the WIN32 run-time).




Simplest approach: no libs at all ...


gcc -Wall -Wextra -Wunused -DSPATIALITE_AMALGAMATION \
  -DOMIT_GEOS=1 -DOMIT_PROJ=1 -DOMIT_ICONV=1 \
  -DOMIT_GEOCALLBACKS=1 \
  -I./headers -I/usr/local/include \
  spatialite.c sqlite3.c spatialite_sample.c \
  -o spatialite_sample.exe
And finally a third way exists, one allowing to directly embed a complete Spatial SQL engine in the simplest and absolutely painless way.
[I suppose following this latest approach you can be actually able to get a minimal SpatiaLite support even on PDA or smart-phones]
Just few explanations: All right, you are now ready to build all-together your statically linked executable.
Please note, absolutely no external libraries are required:

Back to Language Bindings

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.