Skip to main content
  • EN

We made copies of our feature class tables and copied them over to several GeoPackages. Now I want to use the geopackages in a FME workspace to import data to the same table again.

First step therefore is to validate the GPKG table to the DB table to make sure that the structure and fields are correct and dtermine the correct db table.

To accomplish this, I use a dynamic reader and collect all field names (attribute{}.name), concatenae them and compare the resultin strings with the concatenated string of the db table.

This generally works as expected but unfortunately the FID column is missing in the attribute{}.name list from the GPKG reader.

I know that there is the geopackage_fid attribute but that attribute only holds the value and not the name of the FID column.

is there a way to get the name of the fid field of a GPKG?

 

get_fid_column.zip

^ Here's a sample workspace that can find out the ID column in a GeoPackage table.

 

Basically you can use SQL to execute the query:

pragma table_info("your_table_name");

This works because OGC GeoPackage is actually a SQLite3 database, just with extra features.

 

This will give you information about all of the columns in the table, including their names and whether they are the primary key column (ID column).

get_fid_column.zip

^ Here's a sample workspace that can find out the ID column in a GeoPackage table.

 

Basically you can use SQL to execute the query:

pragma table_info("your_table_name");

This works because OGC GeoPackage is actually a SQLite3 database, just with extra features.

 

This will give you information about all of the columns in the table, including their names and whether they are the primary key column (ID column).

Superb!

I knew about the SQLite and also worked around some GPKG limitations by using DBeaver to manipulate Geopackages but I never heard about "pragma table_info".

Thank you very much!

Superb!

I knew about the SQLite and also worked around some GPKG limitations by using DBeaver to manipulate Geopackages but I never heard about "pragma table_info".

Thank you very much!

No problem, happy to help!

I use `pragma table_info` quite a lot. Whenever I open up a database in the shell I pretty much always do:

SELECT name FROM sqlite_master WHERE type = 'table';

and then

PRAGMA table_info("interesting_table_name_here");

for whatever tables look interesting.

Reply


Community Stats

30,112
Posts
114,305
Replies
37,745
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.