Questions about GeoPackage

[Nolan Darilek]

I've been looking into GeoPackage recently and had some questions about
its capabilities, plus its use with Spatialite. GPKG seems to have
broader library/language support--I can access them via JavaScript for
instance--but I'm very confused about their capabilities.

* Can they be fully queried in Spatialite? It looks like there is
support, but I'm wondering if I can run all supported spatial functions
or just a subset?
* Does the Spatialite support modify the database, replacing or
inserting Spatialite-compatible geometries for the GPKG ones? Or are
they translated in place?
* Do I need to create my GPKG in a certain way? Seems GDAL supports
vector and raster GPKG formats, but I don't know what the difference is,
or if one works for spatial queries while the other doesn't.
* Is it possible to perform spatial queries on GPKG files without
Spatialite? I don't have a strong aversion to Spatialite, but I remember
3-4 years ago that the Android version corrupted files larger than 2 GB
and there wasn't a whole lot of certainty about how to fix it. I'd also
like to experiment with geospatial data in React Native, so if I can use
GPKG's native JS library then that lowers the barrier some. Still
though, if I can use all my tools on the same dataset then that's a win.

Thanks.

[mj10777]

I will attempt to answer this, but please not I am not an expert on the GPKG support.

I assume you have gone through the set of  GPKG specific commands found here:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p16gpkg

On Saturday, 11 June 2016 17:56:59 UTC+2, Nolan Darilek wrote:

I've been looking into GeoPackage recently and had some questions about
its capabilities, plus its use with Spatialite. GPKG seems to have
broader library/language support--I can access them via JavaScript for
instance--but I'm very confused about their capabilities.

  * Can they be fully queried in Spatialite? It looks like there is
support, but I'm wondering if I can run all supported spatial functions
or just a subset?

As far as I can see, once 'EnableGpkgMode' has been called

- most of the Spatial-Functions will assume that the Geometries received are of the GPKG type

--> to my knowledge Spatial-Functions returning a Geometry will also be of the GPKG type

  * Does the Spatialite support modify the database, replacing or
inserting Spatialite-compatible geometries for the GPKG ones? Or are
they translated in place?

As far as I can see, after 'EnableGpkgMode' has been called (and found a GPKG-Database): yes

- this can be checked with 'GetGpkgMode' (TRUE of FALSE)

  * Do I need to create my GPKG in a certain way? Seems GDAL supports
vector and raster GPKG formats, but I don't know what the difference is,

Geometries are 'Vectors'

Images (which are Tiles) are 'Rasters'

- similar, but different, to mbtiles

or if one works for spatial queries while the other doesn't.

When creating a Database, Geometry-Fields in a Table 

- use the GPKG specific Functions listed in the above link

  * Is it possible to perform spatial queries on GPKG files without
Spatialite?

Here I am not sure what you mean.

1) can other software supporting the GPKG functionality read the Database created using the commands in the above link

I assume yes

2) can spatialite Spatial-Queries be executed (after 'EnableGpkgMode' has been called)

I assume yes, but care must be taken with SpatialiIndexes 

- the 2 Systems implementing SpatialiIndex are different and this should not be mixed

There is also an 'GpkgAmphibiousMode' [VirtualGPKS]

- which will emulate the Spatialite logic on a  GPKG-Database

 

I don't have a strong aversion to Spatialite, but I remember
3-4 years ago that the Android version corrupted files larger than 2 GB

This is true for all files stored on Android 

- they use a 'signed integer' instead of an 'unsigned integer'

-- thus adding something (such has a tile) will fail, but not corrupt, if the final result is > 2 GB

and there wasn't a whole lot of certainty about how to fix it.

I have not heard of any attempt to correct this error in newer systems  

I'd also
like to experiment with geospatial data in React Native, so if I can use

no idea was is meant by 'React Native' 

GPKG's native JS library then that lowers the barrier some. Still
though, if I can use all my tools on the same dataset then that's a win.

Important would be, with each new connection, to call 'EnableGpkgMode'

- this should insure that read/create Geometries are of the GPKG type

- Database Table creation MUST use the GPKG-Functions shown in the above link

Thanks.

[Pepijn Van Eeckhoudt]

 

I don't have a strong aversion to Spatialite, but I remember
3-4 years ago that the Android version corrupted files larger than 2 GB

This is true for all files stored on Android 

- they use a 'signed integer' instead of an 'unsigned integer'

-- thus adding something (such has a tile) will fail, but not corrupt, if the final result is > 2 GB

This was due to a bug in older versions of SQLite where it was assumed that offset_t was 64-bit when large file system support was enabled and that sys call transparently delegated to the 64-bit versions. That's not the case on Android where offset64_t and 64-bit sys calls need to be used explicitly. I don't remember in which version of SQLite this was fixed, but I'm pretty sure this is no longer an issue. 

To get the fix you need to be using a fairly recent version of SQLite which may not be included in the Android version you're targeting. Easiest fix that works on all Android versions is using your own SQLite binary instead of the system one. These days you can get a binary version of that including Java bindings at https://www.sqlite.org/android/doc/trunk/www/index.wiki

The only issue that still exists is that once your database gets larger than 2Gb, SQLite may not be able to shrink the file again after a vacuum. See http://www.sqlite.org/cgi/src/artifact/a9443cdab41d7f3c and search for robust_ftruncate for details.

I provided a patch for this to the SQLite developers, but they rejected it. The fix consists of using ftruncate64 instead of ftruncate. Unfortunately when I submitted this patch the Android libc, bionic, did not provide a sys call wrapper function for ftruncate64. That meant the sys call wrapper needed to be generated (AOSP contains a python script to do that) and the wrapper function needed to be included in SQLite. I'm not sure why they rejected the patch exactly, they never explained why, but I assume it's due to the perceived complexity of the fix.

Pepijn

[Nolan Darilek]

Hmm, so it sounds like this issue may have been fixed then? I don't recall on which version I last encountered it but I know it was still true with the Android geopaparazzi Spatialite as of early 2014. I don't know if this ships/shipped its own SQLite library at that time or if it relied on Android's.

Also, to be clear about my use case, it wasn't with the sqlite binary. I was opening the database read-only in my app's code, and the md5sum of the database file would change before and after it was accessed. Nothing in my app wrote to the database, and the same code worked fine on my laptop.

If this still sounds like the same bug that was fixed, then great! At the time I remember there being confusion, and I'm not smart enough to fix it myself. Would love to transition this project back to Spatialite rather than trying to figure out gpkg.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[mj10777]

On Sunday, 12 June 2016 18:16:46 UTC+2, Nolan Darilek wrote:

Hmm, so it sounds like this issue may have been fixed then? I don't recall on which version I last encountered it but I know it was still true with the Android geopaparazzi Spatialite as of early 2014. I don't know if this ships/shipped its own SQLite library at that time or if it relied on Android's.

I wrote the mbtiles-class for geopaparazzi

- it uses (and still does use) the native SQL-Driver.

-- it does not use the 'Spatialite' Driver used for geometries in geopaparazzi (where the latest sqlite3 version is being used)

Also, to be clear about my use case, it wasn't with the sqlite binary. I was opening the database read-only in my app's code, and the md5sum of the database file would change before and after it was accessed. Nothing in my app wrote to the database, and the same code worked fine on my laptop.

If this still sounds like the same bug that was fixed, then great! At the time I remember there being confusion, and I'm not smart enough to fix it myself. Would love to transition this project back to Spatialite rather than trying to figure out gpkg.

At the time I wrote the mbtiles class, there was no mention of sqlite3 being the cause, only of the file-system programming logic did not supporting files > 2 GB - supposedly because of the signed integer being used for file size (FAT32 used on sdcard's does support files > 2GB)

- the claim then was: a stupid programming error, that would hopefully be resolved

Mark

[Brad Hards]

When thinking about GeoPackage support in SpatiaLite, consider that the geometry in the database is not the same as the geometry in memory.

There is "native" spatialite format (which changed in 2.4), FDO, geopackage, and potentially other formats that are basically simple features.
There can also be other data structures, like topology or routing networks.

The functions that operate on simple features don't work on the serialised (in database / blob) formats directly, but rather the in-memory datastructures (e.g. gaiaPoint, gaiaLinestring).
So any of those simple features functions should work for GeoPackage.

You should not expect routing functions or topology functions to work because there is no matching capability in GeoPackage.

Brad

[mj10777]

On Monday, 13 June 2016 03:34:36 UTC+2, Brad Hards wrote:

When thinking about GeoPackage support in SpatiaLite, consider that the geometry in the database is not the same as the geometry in memory.

There is "native" spatialite format (which changed in 2.4), FDO, geopackage, and potentially other formats that are basically simple features.
There can also be other data structures, like topology or routing networks.

The functions that operate on simple features don't work on the serialised (in database / blob) formats directly, but rather the in-memory datastructures (e.g. gaiaPoint, gaiaLinestring).
So any of those simple features functions should work for GeoPackage.

A further note:

- when  'EnableGpkgMode' has been set, the function that reads (gaiaFromSpatiaLiteBlobWkbEx) or writes (gaiaToSpatiaLiteBlobWkbEx) a Blob, knows that you are working with GeoPackage Geometries and will deal with the needed 'translation' when needed

-- so if your goal is to work in a pure Gpkg-Environment, calling 'SELECT 'EnableGpkgMode();'  at the start of each Database connection should avoid any problems

You should not expect routing functions or topology functions to work because there is no matching capability in GeoPackage.

Yes, very true.

If this is desired, the result must be in a spatialite Database-Format (no  'SELECT 'EnableGpkgMode();')

- with the use of 'ATTACH Database'

-- any BLOB being read from the GeoPackage-Database would need a  GeomFromGPB() to return a spatialite-geometry needed by these functions

ATTACH Database 'somefile.gpkg' AS db_gpkg;
SELECT GeomFromGPB(gpkg_geometry) AS spatialite_geometry FROM db_gpkg.gpkg_table WHERE ...
-- Save a spatialite Geometry to a ATTACHed GeoPackage-Database
-- UPDATE db_gpkg.gpkg_table SET gpkg_geometry= (SELECT AsGPB(spatialite_geometry) FROM spatialite_table WHERE ...);

Never mix the 2 types in one Database.

Mark

Brad

[David Anderson]

Nolan,

I have done some work with the Geopackage format in the past year.  There are some posts on the site where I was asking questions about how it works with Spatialite.  I eventually stopped using the format as I did not see any advantages over native Spatialite.

Some of the issues that I got tired of dealing with were the spatial indexing and mixing data sources.

The spatial indexing issue actually gets at using the Spatialite functionality.  Doing spatial indexing in a geopackage means going back to the older method of querying the r-tree index instead of the shorthand method that was rolled out a few versions back.  There is some new functionality that my processing required, which are the elementary geometry and the KNN searching that the geopackage format does not support or at least I couldn't figure it out.

I also go tired of remembering data formats when working with data in native Spatialite format and geopackage format. 

Working with geopackage data in other tools is what I understood as the goal of the format.  An open database format for storing spatial data.  I have experience using the 10.2 and newer versions of ESRI tools with geopackages.  That mostly worked.  There were some quirks, as to be expected.

I hope this helps.

David