No Such Function: ST_MinX

[abucklecslt]

Hi,

I ran the NSG GeoPackage 2.1 test from the OGC Validation Web Site against a geopackage. The NSG Data Validity test case failed with the following error:

"java.sql.SQLException:
[SQLITE_ERROR] SQL error or missing database (no such function: ST_MinX)"

I believe it may be an issue with the NSG GeoPackage 2.1 test case. The function ST_MinX() should be supplied when loading the SpatiaLite drivers. Maybe the loading of the SpatiaLite drivers are failing when the test case is ran.

[dstenger]

@jyutzler
Does this error message sound familiar to you? If this is the case, can you please provide details about it?

@abucklecslt
Can you please provide a test dataset we can use to reproduce the error?

[jyutzler]

@dstenger I don't own the NSG GeoPackage 2.1 profile or its ETS but it seems strange to me that either would have a dependency on SpatiaLite. This should be brought to the attention of whoever owns those products.

[dstenger]

@keshav-nangare
Can you take a look at this issue?

[abucklecslt]

Attached is a sample dataset that will cause the error. Regards, Anthony

…

On 18-03-26 09:31 AM, dstenger wrote: @jyutzler <https://github.com/jyutzler> Does this error message sound familiar to you? If this is the case, can you please provide details about it? @abucklecslt <https://github.com/abucklecslt> Can you please provide a test dataset we can use to reproduce the error? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#10 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AVEt8NWj46dkmcVkDF1MbMOI24HAtxbiks5tiNiPgaJpZM4S4iU_>.

[keshavnangare]

@abucklecslt

Can you please provide the dataset in issue tracker? I think it's not uploaded.

[abucklecslt]

The following geopackage dataset will reproduce the issue.

CSLT_Canada.zip

[abucklecslt]

I uploaded the requested dataset as a zip.

…

On 18-04-02 10:31 AM, Keshav Nangare wrote: @abucklecslt <https://github.com/abucklecslt> Can you please provide the dataset in issue tracker? I think it's not uploaded. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#10 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AVEt8KMa9LB8DDbKgnhx-jHAbD9hCYFQks5tkiFEgaJpZM4S4iU_>.

[keshavnangare]

@abucklecslt

I have tested the dataset with the ets and found following observations.

SELECT column_name FROM gpkg_geometry_columns WHERE table_name='CAN_CSLT_ROUTES'; Output==>> Shape[Column_name]

By using output above query, following query is executed

SELECT ST_MinX(Shape) AS theResult FROM CAN_CSLT_ROUTES;

And this query is failed with the reported error because the table " CAN_CSLT_ROUTES" is null.

Can you please check this table?

[abucklecslt]

Thank you for your help. I will review.

…

On 18-04-03 11:36 AM, Keshav Nangare wrote: @abucklecslt <https://github.com/abucklecslt> I have tested the dataset with the ets and found following observations. |SELECT column_name FROM gpkg_geometry_columns WHERE table_name='CAN_CSLT_ROUTES'; Output==>> Shape[Column_name]| By using output above query, following query is executed |SELECT ST_MinX(Shape) AS theResult FROM CAN_CSLT_ROUTES;| And this query is failed with the reported error because the table |" CAN_CSLT_ROUTES"| is null. Can you please check this table? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#10 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AVEt8H9cNjTXyImVUFgyemmD_bP7pLi4ks5tk4HsgaJpZM4S4iU_>.

[ajanett]

The NSG GeoPackage 2.1 test suite has apparently got one and only one 'feature' related test in it at the moment and you managed to hit it. A detailed explanation is forthcoming.

[gtrenum]

Please accept my apologies in advance for the length of this comment.
A couple of people much smarter than me provided the following analysis and recommendations.

The earlier comment "The function ST_MinX() should be supplied when loading the SpatiaLite drivers. Maybe the loading of the SpatiaLite drivers are failing when the test case is ran." Is relevant, but not complete. The ST_Geometry function calls are included in the Spatialite extensions and are not native to SQLite. The NSG and OGC standards for GeoPackages specifically call out the ST_MinX and several other ST_Geometry functions in their tests. However, the test code does not include the Spatialite extensions to the SQLite database and so we would expect all of the ST_Geometry function calls to behave similarly. It is also worth noting that the current NSG test code making the calls to ST_MinX etc. is performing this test on the features. This is the only 'feature' test in the NSG GeoPackage test code.

There has been discussion about not wanting the NSG standards to have any GeoPackage tools dependent on the full Spatialite extensions. However, the way the standard is written, it is explicitly dependent on them (see the SQL test statements). Also, while the NSG Standard or GeoPackage explicity calls out SQLite support, is does not explicitly mention Spatialite extensions. The way the GeoPackage test tools are all coded, SQLite is opened in a manner that does not allow extensions. The GeoPackage standards do call out the "GeoPackage SQLite Configuration" settings and those settings do not include the settings needed for extensions. This part of the standard should be corrected to allow for extensions if the previously described extensions are really wanted.

As far as corrective action is concerned, we see two options; either update the test code to include the Spatialite extensions or write custom extensions/custom code to support the functionality needed to execute the NSG Profile tests.

The spatialite-like capabilities are required for feature operations and the one within the NSG code that is failing is tests needed to establish feature R-Tree values. There is a good possibility that the spatialite type of content can be implemented within the GeoPackage test units in a manner that does not require SQLite extensions at all, but in this case these spatilite-like capabilities would be local to the GeoPackage test tool (features portion of it). This avoids having to change the way SQLite is started up and any potential security headaches caused by those changes.

[ghost]

"However, the way the standard is written, it is explicitly dependent on them (see the SQL test statements)."

You are right in that we should probably update the way the NSG profile provided their examples and remove references to spatialite extensions. The data validity constraints are fine, but the SQL statement examples should not reference those spatialite extensions.

I think that going the custom extension/custom code route seems to be the best solution in this case.

[jyutzler]

However, the way the standard is written, it is explicitly dependent on [SpatiaLite] (see the SQL test statements).

Yep, this is something that was corrected in GPKG 1.2. Compare http://www.geopackage.org/spec120/#abstract_test_suite to http://www.geopackage.org/spec110/#abstract_test_suite.

[gramnation]

I agree with @jyutzler and @melvchan. What this issue means to me is ETS will fail any GeoPackage vector table created with a recent version of GDAL (barring strange layer creation options).

In recent branches of GDAL this C++ code supplies these functions internally, without spatialite:
https://github.com/OSGeo/gdal/blob/f7b90faac087ca5d71084a64057c87309fdae7ae/gdal/ogr/ogrsf_frmts/gpkg/ogrgeopackagedatasource.cpp#L6350

Is there a reason the ETS wouldn't want similar internal code?

[ghost]

@gramnation , thank you for pointing this out. We have begun writing some custom internal code for the ETS, but will take a look at what GDAL did and see if this is something we can utilize.

[dstenger]

@keshav-nangare Can you please do investigations?

[ajanett]

I already have custom code that will perform these tests w/o spatialite but have been unable to test it in this revision of ETS geoPackage because all feature tests are being excluded. Am posting another issue on that now.

[ajanett]

We need to remove the test NSG 19B on "features" from the NSG ETS GeoPackage and this needs to be grouped with the other feature geometry tests within the OGC GeoPackage tests.

[dstenger]

As far as I see, spatialite cannot be added as a Maven dependency (Java library) but must be installed on OS and loaded by driver then.

Thus, removing the spatialite functions from tests is, in my opinion, the better solution as there are no extra dependencies then which make the installation and maintenance a lot more complex.

@ajanett You are very much welcome to provide your custom code via pull request.

[ajanett]

We will be initiating the pull request to this NSG geopackage soon to remove this test.
However, the nsg geopackage tests are built upon the OGC geopackage tests, and the pull request: opengeospatial/ets-gpkg12#62
corrects this issue within the OGC Geopackage tests and performs these tests without the use of spatialite extensions.