[gdal-dev] Binary Predicates in SQLite SQL dialect

[Even Rouault]

Le mercredi 01 octobre 2014 14:49:27, vous avez écrit :
> On 2014-09-30 17:08, Even Rouault wrote:
> > Le mardi 30 septembre 2014 20:20:14, Andre Vautour a écrit :
> >> Hi all,
> >> 
> >> I am trying to use a geometry binary predicate (ST_Intersects) in
> >> ExecuteSQL() with the SQLite dialect and keep getting back an
> >> "ST_Intersect function does not exist" error back from SQLite.
> >> 
> >> GDAL is built with SpatiaLite (HAVE_SPATIALITE is defined), but I
> >> believe the problem is that SpatiaLite is built without GEOS in our
> >> case, so those predicate functions end-up not being available in
> >> SpatiaLite. The end result is that OGRSQLiteRegisterSQLFunctions does
> >> not add the OGR-based predicate functions as SpatiaLite is present, but
> >> SpatiaLite does not contain the predicate functions.
> >> 
> >> I would build SpatiaLite with GEOS support, but unfortunately its LGPL
> >> licensing is too restrictive for our application. So, would it make
> >> sense to change the logic in OGRSQLiteRegisterSQLFunctions to account
> >> for the case of SpatiaLite being built without GEOS?
> > 
> > Andre,
> > 
> > That makes sense. I guess that can only be checked at runtime though,
> > probably by issuing a ST_Intersects() and checking the error code.
> > 
> > I'd note that the spatial predicates in OGR geometry are also based on
> > GEOS. Except OGRGeometry::Intersects() that has a simplified
> > implementation based on bounding box intersection when GEOS is not
> > available.
> > 
> > Some time ago, I had a look at boost geometry (
> > http://www.boost.org/doc/libs/1_56_0/libs/geometry/doc/html/index.html 
> > ). That could be used as an alternative backend to GEOS for most
> > predicates and functions returning geometries, and the boost licence is
> > a modified X/MIT.
> 
> In that case, I might lean towards implementing the binary predicates as
> SQLite functions at our level given that we have a proprietary
> implementation of ISO 19107.
> 
> In order to do so, I'd need to have a way to add functions to the
> created sqlite3 object. Assuming we cache the sqlite data source between
> SQL executions, one option would be to enable the loadable extensions on
> the created sqlite3 object like is done in
> sqlite\test_load_virtual_ogr.c using sqlite3_enable_load_extension(). I
> could then call ExcuteSQL() with "SELECT load_extension(...)". Another
> option would be to expose a callback that would allow an application to
> add functions to an SQLite database when it is created, that is, expose
> something similar to OGR2SQLITEModule::SetHandleSQLFunctions().
> 
> Yet another option would be to allow for an extension point by
> introducing something like an OverlayStrategy and having the default be
> the GEOSOverlayStrategy. That would allow for the boost::geometry
> extension point you were talking about, and allow applications that have
> their own geometry library like us to use their in house implementation
> for predicates and overlays.
> 
> Would you guys be willing to take one of those options?

André,

The latter one looks the more appealing to me.

That would likely be a C++ interface IGeometryBackend with pure virtual 
methods like
   bool Intersects(OGRGeometry*, OGRGeometry*);
   OGRGeometry* Intersection(OGRGeometry*, OGRGeometry*);

And potentially GEOS prepared geometry could fit with :
   bool SupportsPreparedGeometry()
   bool SupportsPreparedGeometryInersection()
   GeometryBackendPreparedGeometry* Prepare(OGRGeometry*)
   void DeletePreparedGeometry(GeometryBackendPreparedGeometry*)
   bool Intersects(GeometryBackendPreparedGeometry*, OGRGeometry*)

And there would be OGRInstallGeometryBackend(IGeometryBackend*).

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com