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
_______________________________________________ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
