<html><head></head><body><div class="ydp1613dd47yahoo-style-wrap" style="font-family:verdana, helvetica, sans-serif;font-size:13px;"><div dir="ltr" data-setdir="false"><div><p style="line-height: 100%; margin-bottom: 0in">
I have updated my proposed database structure and have loaded the
data into PostGIS. I now am trying to load this same data into
geopackage tables using spatialite_gui and am having troubles
creating geometry columns. My old sql statements added the geometry
columns as part of the CREATE TABLE statements such as:</p>
<p style="line-height: 100%; margin-bottom: 0in">, geom_addline
linestring not null);
</p>
<div style="line-height: 100%; margin-bottom: 0in">but now that does
not add an entry into the gpkg_geometry_columns table. The populated
tables (> 2Gb total) appear to load properly into QGIS Win10 (3.38, then 3.40), but
I am told there is no geometry, even though the tables have a type of
Point or Linestring and the field is populated with the binary
geometric data. I was advised (by AI) to use:</div>
<p style="line-height: 100%; margin-bottom: 0in">ALTER TABLE addline
ADD COLUMN geom_addline GEOMETRY(Linestring, 4269);</p>
<p style="line-height: 100%; margin-bottom: 0in">but that results in
bad syntax and I suspect geopackages don’t allow adding geometries
like above.</p>
<p style="line-height: 100%; margin-bottom: 0in">I reverted back to
the old gpkgAddGeometryColumn approach and was able to get a geometry
column in one table, but when I tried it on any subsequent table I
always receive the “FOREIGN KEY constraint failed” message, even
though there are no similarities between the tables and data has not
been imported. If I use an INSERT statement to directly add a second
geometry column to the gpkg_geometry_columns table I get the same
error message.</p>
<p style="line-height: 100%; margin-bottom: 0in">My workaround was to
use QGIS to export selections from my PostGIS tables into a
geopackage file. This creates proper geometry columns that are
listed in the gpkg_geometry_columns table but it also creates an
additional fid column that is the new primary key. My table design
specifies GUID values for primary keys. I also used ogr2ogr to
create tables in a new gpkg and it has the same behavior and
seemingly no option to preserve my primary keys. The only advice
from here I get (from AI) that says geopackages are unique in not
allowing the primary key to switch from one field to another, but we
are forced to create a new table in the geodatabase file with the
primary key we want, then copy data from one table to another. This
brings me back to creating a new table that has no ability to add a
geometry column.
</p>
<p style="line-height: 100%; margin-bottom: 0in">An earlier approach
of mine was to have three geodatabase files and now I see the wisdom
of having all tables in one file. If I could combine the tables in
these three files into one file then I could RENAME, DROP and ADD
columns to achieve my new standard. I see no way to do this.</p>
<p style="line-height: 100%; margin-bottom: 0in">I would appreciate
any advice, even sending me to another forum that is better suited to
answer this, (but I have my doubts and subscribing to this forum for
so many years has been uplifting.)</p>
<p style="line-height: 100%; margin-bottom: 0in">Also, this all ties into my proposal for
a 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, Second
Edition. Emphasis on open source, open data.</p>
<p><font face="sans-serif"><font size="2" style="font-size: 10pt"><a href="https://1drv.ms/b/s!As8t4TFOF8xrshhK9pW-a1eJzkk2?e=XzhfSr" rel="nofollow" target="_blank">https://1drv.ms/b/s!As8t4TFOF8xrshhK9pW-a1eJzkk2?e=XzhfSr</a>
</font></font>
</p>
</div>Cheers,</div><div dir="ltr" data-setdir="false">Dennis K McKay<br></div></div></body></html>