Skip to main content
  • EN

I am stuck with the problem of updating a table in geopackage.

I am reading excel records which have a unique Excel_ID number. Then, after some transformers I write the result in a geopackage non spatial table with the primary index column FID set in writer's parameters. Also I track changes with the Change Detector based on Excel_ID field, where the Original features is destination geopackage table and the Revised is the excel table.

 

Further, if I try to write updatet rows in geopackage table with the selected Excel_ID as the ID attribute of the Row selection of a writer parameters, I get an error since the Excel ID is compared with the geopackage FID, which, of course, are different.

 

Then I tried using Feature Merger to combine the updated excel rows with the record from the geopackage table to attach geopackage_fid to the rows join on Excel_ID field. ID attribute of the Row selection of a writer parameters I set to geopackage_fid.

However, this time some of the features are successfully writes to the destination , and some are rejected with the GEOPACKAGE writer error:

"Cannot perform Update on feature type "ABC" because feature with ID '555' does not exist. Skipping features"

despite the fact that features with this FID are in the table. In the inspector - the updated features also look quite correct. Perhaps the problem is in the order of the records to be written, however, I tried to apply some Sorters and FeatureHolders which did not bring results.

Any idea why this error appears?

I would really appreciate for help!

Let me know if description looks unclear and I'll attach the workspace.

I managed to get around the problem. First, I had to manually delete the records by changing the fme_db_operation to "DELETE" for updated rows, and then inserting them again.

Updating rows for an unknown reason does not work.

@ifitch I was able to reproduce your error: "Cannot perform Update on feature type "ABC" because feature with ID '555' does not exist. Skipping features"

Currently the FME Geopackage writer only supports updates using the primary index. This is read back in from a geopackage as the geopackage_fid format attribute.

I've attached a small example that reproduces the problem and shows how you can use the geopackage_id field for the updates.

FME 2019.1: GeopackageUpdateexample.fmwt

@ifitch I was able to reproduce your error: "Cannot perform Update on feature type "ABC" because feature with ID '555' does not exist. Skipping features"

Currently the FME Geopackage writer only supports updates using the primary index. This is read back in from a geopackage as the geopackage_fid format attribute.

I've attached a small example that reproduces the problem and shows how you can use the geopackage_id field for the updates.

FME 2019.1: GeopackageUpdateexample.fmwt

Thank you @markatsafe. I want to clarify. In your workspace, you create a geopackage_id attribute as _changes {0} .originalValue which is equal to something like "ABC312431256". Is it some kind of mistake? Should the value be equal to _changes {1} .originalValue which is equal to the original value of the geobackage_fid attribute?

 

Also It's looks like if you consider the geopackage fid as an attribute to track changes, it forces the change detector to update all records, since geopackage fid does not match any attribute from the source CSV. Or am I wrong?

@ifitch I was able to reproduce your error: "Cannot perform Update on feature type "ABC" because feature with ID '555' does not exist. Skipping features"

Currently the FME Geopackage writer only supports updates using the primary index. This is read back in from a geopackage as the geopackage_fid format attribute.

I've attached a small example that reproduces the problem and shows how you can use the geopackage_id field for the updates.

FME 2019.1: GeopackageUpdateexample.fmwt

@ifitch It should be the geopackage_fid. The order of the list might change, but in my case it's the first element :

_changes{0}.attributeName geopackage_fid

_changes{0}.originalValue 1

_changes{0}.revisedValue

_changes{1}.attributeName Code

_changes{1}.originalValue ABC3116631180

_changes{1}.revisedValue ZYX3116631180

@ifitch I was able to reproduce your error: "Cannot perform Update on feature type "ABC" because feature with ID '555' does not exist. Skipping features"

Currently the FME Geopackage writer only supports updates using the primary index. This is read back in from a geopackage as the geopackage_fid format attribute.

I've attached a small example that reproduces the problem and shows how you can use the geopackage_id field for the updates.

FME 2019.1: GeopackageUpdateexample.fmwt

Hi @Mark Stoakes​ , could you please share your workbench again? I am having the same issue and cannot see the attachment anymore.

Hi @Mark Stoakes​ , could you please share your workbench again? I am having the same issue and cannot see the attachment anymore.

Hi @_dschweitzer​, I've fixed the link to the workspace in Mark's answer and attached it to this comment as well.

Hi @Mark Stoakes​ , could you please share your workbench again? I am having the same issue and cannot see the attachment anymore.

Thanks a lot @lizatsafe​ for reuploading.

Hi - I ran into this same issue and found that two things were required in order to update a geopackage successfully:

1) That the table was created with a named primary index column (under table creation parameters).

2) That the above named column is exposed (on/after any reader/sqlexecutor) and used as the row selection attribute on the writer performing the update.

 

Why this has been implemented differently than other writers is a little confusing. Why can't I just specify an existing ID column that is already in the data, rather than being forced to use a "primary index column"?

 

Hi - I ran into this same issue and found that two things were required in order to update a geopackage successfully:

1) That the table was created with a named primary index column (under table creation parameters).

2) That the above named column is exposed (on/after any reader/sqlexecutor) and used as the row selection attribute on the writer performing the update.

 

Why this has been implemented differently than other writers is a little confusing. Why can't I just specify an existing ID column that is already in the data, rather than being forced to use a "primary index column"?

 

@john_gis4bus​  I think this should be fixed in FME 2021.1 (due for release in June 2021) and later. So you'll be able to update using a join key other then the primary id.

Reply


Community Stats

30,177
Posts
114,556
Replies
37,844
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.