[gdal-dev] Binary Predicates in SQLite SQL dialect

[Andre Vautour]

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?

Thanks again,
André

> Even
>

-- 
*André Vautour*, B.Sc.
Application Developer
CARIS <http://www.caris.com>
115 Waggoners Lane
Fredericton, New Brunswick
Canada    E3B 2L4
Tel: +1.506.458.8533     Fax: +1.506.459.3849
www.caris.com <http://www.caris.com>

*Connect with CARIS*
Twitter <http://www.twitter.com/CARIS_GIS> LinkedIn 
<http://www.linkedin.com/groups?mostPopular=&gid=3217878> FaceBook 
<http://www.facebook.com/pages/CARIS-The-Marine-GIS-Experts/123907500987669?v=app_4949752878> 
YouTube <http://www.youtube.com/user/CARISGIS>

Download your free copy of CARIS Easy View today!
www.caris.com/easyview <http://www.caris.com/easyview>

_________________________________________________________________________
This email and any files transmitted with it are confidential and 
intended only for the addressee(s). If you are not the intended 
recipient(s) please notify us by email reply. You should not use, 
disclose, distribute or copy this communication if received in error.

Any views or opinions expressed in this email are solely those of the 
author and do not necessarily represent those of the company. No binding 
contract will result from this email until such time as a written 
document is signed on behalf of the company.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20141001/07058a5b/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 3859 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20141001/07058a5b/attachment-0005.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 1208 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20141001/07058a5b/attachment-0006.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 1262 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20141001/07058a5b/attachment-0007.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 811 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20141001/07058a5b/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 1299 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20141001/07058a5b/attachment-0009.png>