anonymous added on 2024-07-18 13:54:32:
While building a GeoPackage database with a table containing linestrings, I found that QGIS would not display the data unless the map was displayed at small scale. This was after using UpdateLayerStatistics().
Checking the triggers and spatial index for the table, and gpkg_contents found the extent recorded in gpkg_contents was wrong in the max_x and max_y columns; and found an unrelated issue in the geometry triggers. The spatial index was OK.
1) the cause: gpkg_contents has incorrect extent for a table due to getting mbrmin x or y values for the max columns
The fix: statistics.c -- about line 1248 in do_compute_layer_statistics() -- change the max_x=, max_y= lines to use MbrMax...
2) a table's four geometry triggers (fgsi..., fgsu..., fgti..., fgtu...) have incorrect SQL referencing a column rather than the table in the tests for SRID or geometry type.
gpkg_add_geometry_triggers.c -- about lines 145, 149 - third from last argument of the function call is a column name, rather than a table name
It doesn't directly affect the extent problem, but does allow geometries that have wrong SRID or type to be stored.
The fix: change the third from last argument to the "table" variable
---- SQL to show extent issue ----
select gpkgCreateBaseTables();
create table test (id integer primary key, name text not null);
select gpkgAddGeometryColumn('test','geom','LINESTRING',0,0,4326);
select gpkgAddGeometryTriggers('test','geom');
select gpkgAddSpatialIndex('test','geom');
insert into test (name,geom) values ('one',asgpb(linestringfromtext('LINESTRING(-91.3 45.7, -100.1 43.4)',4326)));
insert into test (name,geom) values ('two',asgpb(linestringfromtext('LINESTRING(-90.7 47.7, -91.3 44.1)',4326)));
select updatelayerstatistics('test');
select * from gpkg_contents where table_name='test'
union
select 'should be','query','','',current_timestamp,
min(mbrminx(geom)) as min_x,
min(mbrminy(geom)) as min_y,
max(mbrmaxx(geom)) as max_x,
max(mbrmaxy(geom)) as max_y, '' from test order by table_name desc;
----
I provide a possible patch:
Index: src/spatialite/statistics.c
==================================================================
--- src/spatialite/statistics.c
+++ src/spatialite/statistics.c
@@ -1243,12 +1243,12 @@
{
/* GeoPackage Vector only */
sql_statement = sqlite3_mprintf ("UPDATE gpkg_contents SET "
"min_x = (SELECT Min(MbrMinX(%s)) FROM \"%s\"),"
"min_y = (SELECT Min(MbrMinY(%s)) FROM \"%s\"),"
- "max_x = (SELECT Max(MbrMinX(%s)) FROM \"%s\"),"
- "max_y = (SELECT Max(MbrMinY(%s)) FROM \"%s\"),"
+ "max_x = (SELECT Max(MbrMaxX(%s)) FROM \"%s\"),"
+ "max_y = (SELECT Max(MbrMaxY(%s)) FROM \"%s\"),"
"last_change = strftime('%%Y-%%m-%%dT%%H:%%M:%%fZ', 'now') "
"WHERE ((lower(table_name) = lower('%s')) AND (Lower(data_type) = 'features'))",
col_quoted, quoted, col_quoted,
quoted, col_quoted, quoted,
col_quoted, quoted, quoted);
Index: src/geopackage/gpkg_add_geometry_triggers.c
==================================================================
--- src/geopackage/gpkg_add_geometry_triggers.c
+++ src/geopackage/gpkg_add_geometry_triggers.c
@@ -140,15 +140,15 @@
{
/* inserting the Triggers */
if (i == 0 || i == 2)
sql_stmt =
sqlite3_mprintf (trigger_stmts[i], xtable, xcolumn, xtable,
- table, xcolumn, column, column, xcolumn);
+ table, xcolumn, table, column, xcolumn);
else
sql_stmt =
sqlite3_mprintf (trigger_stmts[i], xtable, xcolumn, xcolumn,
- xtable, table, column, xcolumn, column,
+ xtable, table, column, xcolumn, table,
column, xcolumn);
ret = sqlite3_exec (sqlite, sql_stmt, NULL, NULL, &errMsg);
sqlite3_free (sql_stmt);
if (ret != SQLITE_OK)
{
|