gpkgAddSpatialIndex not working on ESRI generated data

[David Anderson]

I am doing some post processing work with ESRI created geopackages.  I created spatial table by selecting from another spatial table.  I used the gpkgAddGeometryColumn, gpkgAddGeometryTriggers and gpkgAddSpatialIndex functions.  The process mostly worked.  The data is there, the various ST_ functions work.  What did not work is creating the spatial index.  The gpkgAddSpatialIndex function does not add any rows to the rtree _node, _parent or _rowid tables.

I broke down and used ArcCatalog to create the index.  That worked and an index was created.

The issue is that without the index ArcMap or ArcCatalog will not display the data.

I want to do the processing  without involving ESRI tools but I do have to deliver a data set that can be used by ESRI tools.

I would not be surprised that this is more ESRI not playing by the rules.

I can provide sample data if that would help.

[a.fu...@lqt.it]

Hi David,

it should be certainly useful if you could kindly send to me:

a) a sample of some GPKG created by ESRI tool without any
spatial index
b) exactly the same as before, but this time including the
corresponding Spatial Index created by ESRI tools.

this way I'll be then able to perform a complete differential
analysis and it will surely make simpler identifying where
the problem exactly is.

you can directly send an e-mail attachment to my personal
e-mail <a.fu...@lqt.it> if its size doesn't exceeds 4MB,
otherwise you can use some download service.

bye Sandro

[Brad Hards]

On Fri, 10 Jul 2015 10:13:07 PM a.fu...@lqt.it wrote:
> you can directly send an e-mail attachment to my personal
> e-mail <a.fu...@lqt.it> if its size doesn't exceeds 4MB,
> otherwise you can use some download service.

I would also like to see the samples:
br...@frogmouth.net

Brad

[a.fu...@lqt.it]

Hi David,

I've performed few tests using your sample GPKG; I simply started
by dropping both rtree_Upper_grid_aoi_simpplle_Shape and
Upper_grid_aoi_simpplle tables, then removing any previous row found
into gpkg_contents, gpkg_extensions and gpkg_geometry_columns so to
be finally able to create yet again a complete GPKG table starting
from the "shadow copy" fond into bkup_Upper_grid_aoi_simpplle.

here are my findings:

test #1:
--------
CREATE TABLE Upper_grid_aoi_simpplle(...);
SELECT gpkgAddGeometryColumn('Upper_grid_aoi_simpplle', 'Shape',
'MULTIPOLYGON', 0, 0, 26912);
SELECT gpkgAddGeometryTriggers('Upper_grid_aoi_simpplle', 'Shape');
SELECT gpkgAddSpatialIndex('Upper_grid_aoi_simpplle', 'Shape');
INSERT INTO Upper_grid_aoi_simpplle
SELECT ....... FROM bkup_Upper_grid_aoi_simpplle;

this first test seems to work exactly as expected; the main
table was correctly created and populated, all triggers were
correctly installed, the supporting RTree was successfully
created and populated.


test #2:
--------

CREATE TABLE Upper_grid_aoi_simpplle(...);
SELECT gpkgAddGeometryColumn('Upper_grid_aoi_simpplle', 'Shape',
'MULTIPOLYGON', 0, 0, 26912);
SELECT gpkgAddGeometryTriggers('Upper_grid_aoi_simpplle', 'Shape');
INSERT INTO Upper_grid_aoi_simpplle
SELECT ....... FROM bkup_Upper_grid_aoi_simpplle;
SELECT gpkgAddSpatialIndex('Upper_grid_aoi_simpplle', 'Shape');

same as above: but in this case the Spatial Index will be
created only after populating the main table.
this time the RTree is correctly created, but it's now completely
empty: this simply is because gpkgAddSpatialIndex() just creates
the Spatial Index table and installs all required triggers, but
don't attempts to recover the rows already inserted into the
main table.
Anyway a basically simple way allowing to fully recover an
already populated table exists:

UPDATE Upper_grid_aoi_simpplle SET Shape = Shape;

this will force re-inserting all Geometries, and this time
now all triggers will be already installed, so the RTree table
will now be correctly populated.

-------------
I'm not really sure if this should be considered a bug or not:
after all anything works smoothly when gpkgAddSpatialIndex()
is invoked *before* inserting any row into the table.
and even if the table is already populated just executing a
trivial UPDATE will fully restore the RTree.

bye Sandro

[David Anderson]

Sandro,

Thanks for looking into this.

Those solutions make sense.  The data processing that I was using followed the test2 example you gave.  In the process that I am using I decided after working with the data some to add the geometry column to the output table.

Implementing either solution will be simple enough to do.

While I wouldn't consider the behavior a bug it might save someone else a few minutes if the function documentation indicated that function only works on geometry added after the index is created.

David

[David Anderson]

Sandro,

I was working through this process and ran into a issue.

First I created a blank database then used this command:

select load_extension('mod_spatialite')
select gpkgCreateBaseTables()
create table gis_test (col1)
select gpkginsertepsgsrid(26912)
SELECT gpkgAddGeometryColumn('gis_test','shape','MULTIPOLYGON',0,0,26912)

At the gpkgAddGeometryColumn statements I get a foreign key violation.

Doing this:

pragma foreign_keys=off

then re running the gpkgAddGeometryColumn command works.  However no records are entered into the gpkg_contents table.

I am running the latest 4.3 version.

Am I missing a step along the way?

I was also wondering will the UpdateLayerStatistics command will work for geopackages to update the min_x,max_x,min_y,max_y fields in gpkgcontents?

[mj10777]

On Wednesday, 15 July 2015 00:27:21 UTC+2, David Anderson wrote:

Sandro,

I was working through this process and ran into a issue.

First I created a blank database then used this command:

select load_extension('mod_spatialite')
select gpkgCreateBaseTables()
create table gis_test (col1)
select gpkginsertepsgsrid(26912)
SELECT gpkgAddGeometryColumn('gis_test','shape','MULTIPOLYGON',0,0,26912)

At the gpkgAddGeometryColumn statements I get a foreign key violation.

Doing this:

pragma foreign_keys=off

then re running the gpkgAddGeometryColumn command works.  However no records are entered into the gpkg_contents table.

I am running the latest 4.3 version.

Am I missing a step along the way?

I tried your script, with the same results, but instead of applying the 'dirty trick' mentioned here:

https://groups.google.com/forum/?utm_source=digest&utm_medium=email#!searchin/spatialite-users/gpkg/spatialite-users/rBKeuXLpQv8/2yGdQIIXOUoJ

dirty trick: you can simply disable Foreign Key constrains by issuing a 

"PRAGMA foreign_keys=0" directive.  

simply fulfilled the constraints needed by 'gpkg_geometry_columns' 

CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),

by adding:

INSERT INTO gpkg_contents (table_name, data_type, srs_id) VALUES ('gis_test', 'features', 26912);

and everything worked correctly.

---

The use of 'PRAGMA foreign_keys=0' did not resolve the missing entry in 'gpkg_contents', just allowed an invalid addition

- that will fail later with functions relying on the fact that only tables entered correctly to 'gpkg_contents' can be considered usable/valid

This is a case where applying a 'dirty trick' to get rid of the symptom of a problem, does not resolve the problem

I was also wondering will the UpdateLayerStatistics command will work for geopackages to update the min_x,max_x,min_y,max_y fields in gpkgcontents?

I doesn't seem so. This may be an internal spatialite function (i.e. not for the gpkg layout).

I just ran the script found at the above link, where I assume that everything is done correctly (with gpkgAddGeometryTriggers and gpkgAddSpatialIndex)

- all min_x,max_x,min_y,max_y were also NULL

so it looks as if the Triggers do not update gpkgcontents

- so maybe a 'gpkgUpdateLayerStatistics()' is needed for this

Mark

[mj10777]

On Wednesday, 15 July 2015 00:27:21 UTC+2, David Anderson wrote:

Sandro,

I was working through this process and ran into a issue.

First I created a blank database then used this command:

select load_extension('mod_spatialite')
select gpkgCreateBaseTables()
create table gis_test (col1)
select gpkginsertepsgsrid(26912)
SELECT gpkgAddGeometryColumn('gis_test','shape','MULTIPOLYGON',0,0,26912)

At the gpkgAddGeometryColumn statements I get a foreign key violation.

Doing this:

pragma foreign_keys=off

then re running the gpkgAddGeometryColumn command works.  However no records are entered into the gpkg_contents table.

I am running the latest 4.3 version.

Am I missing a step along the way?

If I remember correctly, a statement was made in the last 12 months, that the spatialite project would not be offering a full fledged gpkg support.

This problem, I believes, reflects this policy.

As apposed to spatialite

- which takes care, when needed, that everything needed in its internal Metadata tables are taken care of

--> Add/Recover/DeleteGeometryColumn: '.. updating the Spatial Metadata tables ..'

For 'gpkgAddGeomtryColumn' this (it seems) is not the case 

- this should be noted in 'spatialite-sql-?.?.?.html' (misspelled BTW, missing 'e')

So it is up to the creator of the gpkg-Database to insure that the Metadata tables are correctly filled

- mainly 'gpkg_contents'

In the long term, however, it might be better to just 'bite the bullet' and support proper Metadata table, trigger handling with gpkg as it is done for spatialite

- saving a lot of hassle in the future

Mark

[a.fu...@lqt.it]

Sorry, there was something going wrong in gpkgAddGeometryColumn();
here is the full history:

1. about two months ago (immediately before releasing the first 4.3.0
Release Candidate) it emerged that gpkgAddGeometryColumn() failed
due to a Foreign Key constraint violation simply caused by a
missing corresponding entry into the "gpkg_content" table.

2. consequently we decided that the most appropriate solution was to
modify the gpkgAddGeometryColumn() implementation so to silently
insert an eventually missing "gpkg_content" entry whenever it
was required.

https://groups.google.com/forum/#!searchin/spatialite-users/creating$20an$20ESRI$20compatible$20Geopackage$20second$20thought|sort:relevance/spatialite-users/rBKeuXLpQv8/EzgG4GsJeeUJ

3. unhappily something gone wrong with the intended patch that
didn't worked properly due to a trivial programming error.
nobody noticed during the Release Candidate testings that the
problem was still unresolved, so now 4.3.0 continues to be
affected by this issue.

4. as already noticed by Mark the problem is possibly worsened by
the fact that checking Foreign Key constraints is an optional
feature of SQLite and requires an explicit call in order to
be effectively activated: PRAGMA foreign_key=1;
spatialite_gui always do this each time that a new connection
is established, and consequently the bugged gpkgAddGeometryColumn()
will always fail on spatialite_gui.
on the other hand sqlite3 and many language connectors adopt
the opposite default setting (always disabling foreign keys),
so gpkgAddGeometryColumn() will apparently work fine but an
incomplete GPKG will be produced.


Conclusions:
-----------------
the gpkgAddGeometryColumn() issue is now definitely fixed into
the Fossil repository and will be released with the next version.

in the meanwhile, as an interim solution, manually inserting the
appropriate entry into "gpkg_content" before any attempt to call
gpkgAddGeometryColumn() should be a reasonable workaround.

bye Sandro

[mj10777]

This work correctly.

Missing from the original script (which I assume will always be needed) were:

SELECT gpkgAddGeometryTriggers('gis_test','shape');

SELECT gpkgAddSpatialIndex('gis_test','shape'); 

the min_x,max_x,min_y,max_y values of gpkg_contents are still NULL.

[David Anderson]

I thought the function was supposed to fill in both tables, which is why I was surprised.  I didn't see it earlier because of reason 4.  I was using the Python SQLite which did not enforce foreign keys.  I switched to a GUI for testing reasons and the GUI does enforce foreign keys.

Doing the insert into contents is a easy enough workaround, which I had already done by the time I posted the question.

David

[David Anderson]

I am doing those steps after creating the data via a insert ... select ... operation.

The min and max don't seem to be used by ESRI to determine the extent based on the test I did where I inserted values into those fields for a table entry. 

David