spatialite queries from xml stylesheets

[mischkew]

Hi again,

im still struggling with the advice of linking spatialite and sqlite given in #1572. I manage to load the spatialite extension programatically and as sqlite SELECT function. But I don't know how to load the extension when loading styles from xml files (my actual use case) using tilecache or tilelive for example.

It was suggested to link mapnik against an amalgamated version of sqlite/spatialite. Though the compiled libnames result in 'libspatialite' -- not 'libsqlite3'. Will renaming do the trick?

Any help is appreciated. Thanks!

[springmeyer]

Will renaming do the trick?

I think so, but I've not tested.

Could you create a small standalone testcase that I could run to try to get this working? Ideally adapt the ideas of https://github.com/mapnik/mapnik/wiki/A-perfect-testcase along with the example of a postgis testcase like https://github.com/mapnik/mapnik/issues/1577#issuecomment-54703147

[mischkew]

I will work on the issue next week. Thanks.

[vsivsi]

Building the amalgamated version of Spatialite is no longer supported (as of libspatialite 4.0, see https://groups.google.com/forum/#!topic/spatialite-users/zfUn0d9k9Aw).

Given that, what is the recommended path forward for those wanting to write spatial queries for SQLite/Spatialite in mapnik XML stylesheets (or Carto .mml project files)?

[jsimomaa]

Given that, what is the recommended path forward for those wanting to write spatial queries for SQLite/Spatialite in mapnik XML stylesheets (or Carto .mml project files)?

@vsivsi Did you find a solution for writing spatial queries?

I'm asking cause I'm also in a situation where I would like to write spatial queries in Mapnik XML stylesheets/Carto .mml project files..

Quote from #1572:

The other option would be having the Mapnik sqlite plugin accept an option to trigger loading the module. But I worry this would be a security issue so I'm not inclined to do this.

@springmeyer To avoid the security issues could libspatialite be added as an optional dependency (like libsqlite3 itself) and then the extension could be loaded based on a parameter given to sqlite datasource? E.g.

<Datasource>
    <Parameter name="type">sqlite</Parameter>
    <Parameter name="file">database.sqlite</Parameter>
    <Parameter name="mod_spatialite">true</Parameter>
</Datasource>

Edit: I familiarized myself a bit more into how spatialite works (how it actually can be activated on a sqlite db) and currently it seems there are two options:

1. Either by invoking a SQL query to the sqlite db SELECT load_extension('mod_spatialite'); and to ensure that mod_spatialite module and its required dependencies are available and found at runtime
2. Or by linking to libspatialite at compile time and wrapping the sqlite connection with spatialite:

#include <sqlite3.h>
#include <spatialite.h>
...
// Create connection
void *conn = spatialite_alloc_connection();
const int rc = sqlite3_open_v2 (file_.c_str(), &db_, mode, 0);
spatialite_init_ex( &db_, conn, 0 );
...
// Close connection
spatialite_cleanup_ex( db_ );
sqlite3_close (db_);

Any comments which do you find preferable?

Some open questions that I have in mind:

1. Should spatialite be enabled by default in sqlite.plugin (by either of above methods) or by some Datasource parameter
2. Should spatialite be separated completely to its own plugin (I doubt this wouldn't be the way to go..)

[vsivsi]

@jsimomaa This issue seemed like a dead-end, so I ended up using PostGIS instead.

[jsimomaa]

I opened a pull request related to this issue.

[rinigus]

Hi, any updates regarding use Spatialite extension? It would greatly simplify adoption of styles when using sqlite datasources.