From 7185fee5884740df0fa421136ebf40bcce402d63 Mon Sep 17 00:00:00 2001 From: Luke Date: Fri, 1 Sep 2017 11:54:20 +0200 Subject: Added SpatiaLite, an open-source library to extend SQLite to support Spatial SQL --- src/spatialite-1-fixes.patch | 38 +++++ src/spatialite-test.c | 362 +++++++++++++++++++++++++++++++++++++++++++ src/spatialite.mk | 44 ++++++ 3 files changed, 444 insertions(+) create mode 100644 src/spatialite-1-fixes.patch create mode 100644 src/spatialite-test.c create mode 100644 src/spatialite.mk diff --git a/src/spatialite-1-fixes.patch b/src/spatialite-1-fixes.patch new file mode 100644 index 0000000..d1bd03d --- /dev/null +++ b/src/spatialite-1-fixes.patch @@ -0,0 +1,38 @@ +This file is part of MXE. See LICENSE.md for licensing information. + +Contains ad hoc patches for cross building. + +From 0000000000000000000000000000000000000000 Mon Sep 17 00:00:00 2001 +From: Luke Potgieter +Date: Wed, 23 Aug 2017 12:51:27 +0200 +Subject: [PATCH] Patch: Makes sure to link against all of geos' libs as well + as the stdc++ lib + + +diff --git a/configure.ac b/configure.ac +index 1111111..2222222 100644 +--- a/configure.ac ++++ b/configure.ac +@@ -293,18 +293,18 @@ if test x"$enable_geos" != "xno"; then + GEOS_LDFLAGS=`$GEOSCONFIG --ldflags` + GEOS_CFLAGS=-I`$GEOSCONFIG --includes` + AC_SUBST([GEOS_LDFLAGS]) +- AC_SUBST([GEOS_CFLAGS]) ++ AC_SUBST([GEOS_CFLAGS]) + # Ensure that we can parse geos_c.h + CPPFLAGS_SAVE="$CPPFLAGS" + CPPFLAGS="$GEOS_CFLAGS" + AC_CHECK_HEADERS([geos_c.h],, [AC_MSG_ERROR([could not find geos_c.h - you may need to specify the directory of a geos-config file using --with-geosconfig])]) +- CPPFLAGS="$CPPFLAGS_SAVE" ++ CPPFLAGS="$CPPFLAGS_SAVE -I`$GEOSCONFIG --includes`" + # Ensure we can link against libgeos_c + LIBS_SAVE="$LIBS" +- LIBS="$GEOS_LDFLAGS" ++ LIBS="$GEOS_LDFLAGS `$GEOSCONFIG --clibs`" + AC_SEARCH_LIBS(GEOSCoveredBy,geos_c,,AC_MSG_ERROR([could not find libgeos_c (or obsolete 'libgeos_c' < v.3.3.0 found) - you may need to specify the directory of a geos-config file using --with-geosconfig])) + LIBS="$LIBS_SAVE" +- LIBS="$LIBS $GEOS_LDFLAGS -lgeos_c" ++ LIBS="$LIBS $GEOS_LDFLAGS `$GEOSCONFIG --clibs`" + + #----------------------------------------------------------------------- + # --enable-controlpoints diff --git a/src/spatialite-test.c b/src/spatialite-test.c new file mode 100644 index 0000000..6a6e7f5 --- /dev/null +++ b/src/spatialite-test.c @@ -0,0 +1,362 @@ +/* + +demo4.c + +Author: Sandro Furieri a.furieri@lqt.it + +This software is provided 'as-is', without any express or implied +warranty. In no event will the author be held liable for any +damages arising from the use of this software. + +Permission is granted to anyone to use this software for any +purpose, including commercial applications, and to alter it and +redistribute it freely + +*/ + +#include +#include +#include +#include + +/* +these headers are required in order to support +SQLite/SpatiaLite */ +#include +#include +#include + +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) + { +/* some 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 MBR caching +*/ + strcpy (sql, "SELECT CreateMbrCache('test', 'geom')"); + ret = sqlite3_exec (handle, sql, NULL, NULL, &err_msg); + if (ret != SQLITE_OK) + { +/* an error occurred */ + printf ("CreateMbrCache() 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 = (char*) ""; + 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 MBR cache Spatial Index\n", + ix); +/* +now we'll perform the spatial query USING the MBR cache Spatial Index +we'll loop 3 times in order to avoid buffering-caching side effects +*/ + strcpy (sql, "SELECT Count(*) FROM test "); + strcat (sql, "WHERE ROWID IN ("); + strcat (sql, "SELECT rowid FROM cache_test_geom WHERE "); + strcat (sql, + "mbr = FilterMbrWithin(1000400.5, 4000400.5, 1000450.5, 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 = (char*) ""; + 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; + } + printf ("\n\nsample successfully terminated\n"); + spatialite_cleanup_ex (cache); + return 0; + + abort: + sqlite3_close (handle); + spatialite_cleanup_ex (cache); + spatialite_shutdown(); + return -1; +} diff --git a/src/spatialite.mk b/src/spatialite.mk new file mode 100644 index 0000000..c6765c8 --- /dev/null +++ b/src/spatialite.mk @@ -0,0 +1,44 @@ +# This file is part of MXE. See LICENSE.md for licensing information. + +PKG := spatialite +$(PKG)_WEBSITE := https://www.gaia-gis.it/fossil/libspatialite/index +$(PKG)_DESCR := SpatiaLite +$(PKG)_IGNORE := +$(PKG)_VERSION := 4.4.0-RC1 +$(PKG)_CHECKSUM := 80f7fff0a147044c5eb197e565f598ac1f137d86d0a548cbc8f52fb7ff7cac68 +$(PKG)_SUBDIR := libspatialite-$($(PKG)_VERSION) +$(PKG)_FILE := libspatialite-$($(PKG)_VERSION).tar.gz +$(PKG)_URL := http://www.gaia-gis.it/gaia-sins/libspatialite-sources/$($(PKG)_FILE) +$(PKG)_DEPS := dlfcn-win32 freexl geos sqlite zlib proj libiconv libxml2 + +define $(PKG)_UPDATE + $(WGET) -q -O- 'http://www.gaia-gis.it/gaia-sins/libspatialite-sources/' | \ + $(SED) -n 's,.*libspatialite-\([0-9][^>]*\)\.tar.*,\1,p' | \ + tail -1 +endef + +define $(PKG)_BUILD + # freeXL support is only necessary if you want to be able to parse .xls files. + # If you disable freexl support, remove -lfreexl from the test program below. + cd '$(1)' && autoreconf -fi -I ./m4 + cd '$(1)' && ./configure \ + $(MXE_CONFIGURE_OPTS) \ + --enable-freexl=yes \ + --with-geosconfig='$(PREFIX)/$(TARGET)/bin/geos-config' + $(MAKE) -C '$(1)' -j '$(JOBS)' $(if $(BUILD_SHARED), LDFLAGS='-no-undefined') + $(MAKE) -C '$(1)' -j 1 $(INSTALL_STRIP_LIB) + + # compile one of the demo programs (copied from the source package) + '$(TARGET)-g++' $(if $(BUILD_SHARED), -Wno-undefined) \ + -W -Wall -Werror -ansi -pedantic \ + '$(TOP_DIR)/src/spatialite-test.c' -o '$(PREFIX)/$(TARGET)/bin/test-spatialite.exe' \ + `'$(TARGET)-pkg-config' $(PKG) --cflags --libs` \ + `'$(TARGET)-pkg-config' sqlite3 --cflags --libs` \ + $(if $(BUILD_STATIC), -lgeos_c -lgeos -lfreexl -lxml2 -liconv -llzma -lproj -lws2_32 -lz -lstdc++ -lm) + + # create a batch file to run the test program (as the program requires arguments) + (printf 'REM run against a database that should not exist, but remove afterward to save space.\r\n'; \ + printf 'test-spatialite.exe test-db.sqlite\r\n'; \ + printf 'del test-db.sqlite\r\n';) \ + > '$(PREFIX)/$(TARGET)/bin/test-spatialite.bat' +endef -- cgit v0.12