Question

SqlExecutor not handling Geopackage spatial queries

alaryjerome

Trying to query tables from a GeoPackage file with st_intersections() function availabe for spatialite. When I run the SqlExecutor , I have an error logged :

GEOPACKAGE reader: In ExecuteSQL(): sqlite3_prepare_v2(select grid_id,geom  from grid   where st_intersects((Select geom from var_fuelt17),grid.geom)  group by grid_id):   no such function: st_intersects

Tried to use Spatialite Database format pointing to geopackage file but run into issue.

All spatial functions are working in QGIS or using spatialite 4.3 command line tools.

Any ways to make it work ?

2 replies

M
Badge +2

@alaryjerome I don't think Geopackage and SpatiaLite geometries are compatible. It looks like you have to build SpataiLite compatible tables. See this article Spatialite spatial queries do not function on GeoPackage

or from the Fulcrum blog:

GeoPackage databases from the SpatiaLite plugin, we need to first run the following command: SELECT EnableGpkgAmphibiousMode(); This command basically tells SpatiaLite to work natively with GeoPackage geometry, removing the need to explicitly call the appropriate format conversion functions such as GeomFromGPB() or CastAutomagic().

Any other experts on Geopackage please chime in.

alaryjerome

@alaryjerome I don't think Geopackage and SpatiaLite geometries are compatible. It looks like you have to build SpataiLite compatible tables. See this article Spatialite spatial queries do not function on GeoPackage

or from the Fulcrum blog:

GeoPackage databases from the SpatiaLite plugin, we need to first run the following command: SELECT EnableGpkgAmphibiousMode(); This command basically tells SpatiaLite to work natively with GeoPackage geometry, removing the need to explicitly call the appropriate format conversion functions such as GeomFromGPB() or CastAutomagic().

Any other experts on Geopackage please chime in.

Thanks @mark2atsafe for your reply.

The transformer SQLExecutor is suppose to support Geopackage as a built-in option in FME 2019. However, spatial operators such as st_intersects() doesn't seems to be supported in FME for Geopackage. Could it be a bug? Because I am able to use spatial queries on a GeoPackage, outside of FME.

Seeing in the log error "sqlite3_prepare_v2()" make me think FME is calling "sqlite" and not "spatialite" functionalities.

Reply


Community Stats

28,657
Posts
109,304
Replies
36,529
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.