[Qgis-user] Problems creating gpkg geometry columns

[dennis mckay]

I have updated my proposed database structure and have loaded thedata into PostGIS. I now am trying to load this same data intogeopackage tables using spatialite_gui and am having troublescreating geometry columns. My old sql statements added the geometrycolumns as part of the CREATE TABLE statements such as:

, geom_addlinelinestring not null); 
but now that doesnot add an entry into the gpkg_geometry_columns table. The populatedtables (> 2Gb total) appear to load properly into QGIS Win10 (3.38, then 3.40), butI am told there is no geometry, even though the tables have a type ofPoint or Linestring and the field is populated with the binarygeometric data. I was advised (by AI) to use:
ALTER TABLE addlineADD COLUMN geom_addline GEOMETRY(Linestring, 4269);

but that results inbad syntax and I suspect geopackages don’t allow adding geometrieslike above.

I reverted back tothe old gpkgAddGeometryColumn approach and was able to get a geometrycolumn in one table, but when I tried it on any subsequent table Ialways receive the “FOREIGN KEY constraint failed” message, eventhough there are no similarities between the tables and data has notbeen imported. If I use an INSERT statement to directly add a secondgeometry column to the gpkg_geometry_columns table I get the sameerror message.

My workaround was touse QGIS to export selections from my PostGIS tables into ageopackage file. This creates proper geometry columns that arelisted in the gpkg_geometry_columns table but it also creates anadditional fid column that is the new primary key. My table designspecifies GUID values for primary keys. I also used ogr2ogr tocreate tables in a new gpkg and it has the same behavior andseemingly no option to preserve my primary keys. The only advicefrom here I get (from AI) that says geopackages are unique in notallowing the primary key to switch from one field to another, but weare forced to create a new table in the geodatabase file with theprimary key we want, then copy data from one table to another. Thisbrings me back to creating a new table that has no ability to add ageometry column. 

An earlier approachof mine was to have three geodatabase files and now I see the wisdomof having all tables in one file. If I could combine the tables inthese three files into one file then I could RENAME, DROP and ADDcolumns to achieve my new standard. I see no way to do this.

I would appreciateany advice, even sending me to another forum that is better suited toanswer this, (but I have my doubts and subscribing to this forum forso many years has been uplifting.)

Also, this all ties into my proposal fora vendor-neutral national standard for boundary surveying data to support a resilient digital cadastre.  If interested download my free pdf book, The Digital Boundary Surveyor, SecondEdition. Emphasis on open source, open data.

https://1drv.ms/b/s!As8t4TFOF8xrshhK9pW-a1eJzkk2?e=XzhfSr 
Cheers,Dennis K McKay
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20250110/b9cc25e8/attachment.htm>