Skip to main content
Question

How to get the NAME of the FID field of a GeoPackage?

michaelhaertel
Forum|alt.badge.img

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?

 

3 replies

jakemolnar
Forum|alt.badge.img

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).

michaelhaertel
Forum|alt.badge.img
jakemolnar wrote:

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!

jakemolnar
Forum|alt.badge.img
michaelhaertel wrote:

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


Helpful Members This Week

Community Stats

31,468
Posts
119,283
Replies
39,083
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.