Sqlite3, Spatialite and GeoPackages

Photo by Firanka Mipinska on Unsplash

When using Spatialite to query a GeoPackage in Sqlite3, you might find that your spatial functions incorrectly return NULLs, rather than anything useful.

This is because the internal format of the geometry in a GeoPackage is different to a “normal” Spatialite table. You can read more about that here.

To demonstrate the issue, if you execute a statement such as:

sqlite> select id,AsText(geom) from mytable limit 3;

You might be confused at the result, where the result of the AsText() function call appears to be blank:

160648|
160813|
164695|

The blanks are actually NULLs, which we can see by using the .nullvalue command, like this:

sqlite> .nullvalue NULL;

And then run the query again:

sqlite> select id,AsText(geom) from mytable limit 3;

To see that the values are indeed NULL:

160648|NULL
160813|NULL
164695|NULL

We can fix this by using the EnableGpkgAmphibiousMode() command, like this:

sqlite> select EnableGpkgAmphibiousMode();

And then performing our query again:

sqlite> select id,AsText(geom) from mytable limit 3;

Which finally lets us see the result of the spatial function calls:

160648|POINT(-12316.568011 676195.2516)
160813|POINT(-21374.774462 676056.951949)
164695|POINT(-12024.68563 67482.284744)

Success!