Skip to main content
  • EN

I'm trying to update (or rather replace) rows with new features and write them to geopackage. The problem is geopackage primary key is "id" that is generated when writing geopackages and I'm reading features from postgres where that same id doesn't exists and another id (let's call it ChangeID) is used as a primary key. ChangeID does exist in the geopackages and postgres table but geopackages primary key must be "id".

 

Unfortunately I have no control over why the id's is implemented like this. Also I know i could read those geopackages in to fme to get the "id"s but the problem is the dataset is hundreds of geopackages resulting in 100 millions of rows so the whole workflow is designed no to do that because that would result to weeks of runtime. I have a workflow to get rows that need to be replaced but fail to write them into geopackages because i can't use ChangeID to replace the rows because of different primary key.

 

I know there is a way to set match row id in postgres writer but can't find that option in geopackage writer. Is there way to replace those rows based on my ChangeID that is not hte primary key of geopackage or some another way around it without reading all those geopackages in?

hmmmmmmm

 

Maybe a bug? Here's a side-by-side of FME 2020 (left) and FME 2022.2.5 (right). You can see that the Row Selection is missing...imageI'm not sure if there was a problem in the FME 2020 version and they removed it or if for some reason it's missing.

@andreaatsafe​ and ideas?

 

hmmmmmmm

 

Maybe a bug? Here's a side-by-side of FME 2020 (left) and FME 2022.2.5 (right). You can see that the Row Selection is missing...imageI'm not sure if there was a problem in the FME 2020 version and they removed it or if for some reason it's missing.

@andreaatsafe​ and ideas?

 

Huh, I have 2021.1 (that i need to use) and 2022.2 for testing but i have the same problem in both. Is there specific settings i should use to get it appear? I tried update and use existing but that didn't help.

Seems like that's how the writer is now... https://docs.safe.com/fme/html/FME-Form-Documentation/FME-ReadersWriters/ogcgeopackage/user-attributes.htm#PrimaryKey-FeatureOp

"Notice: FME Version 2020.0+: To maintain backwards compatibility, we have released an updated version of the OGC GeoPackage Reader/Writer. The previous version of this format (Format Type Identifier GEOPACKAGE) has been deprecated."

 

One answer to the questions is since a geopackage is sqlite, so you could insert to a temp table in the geopackage, and write your own update statement (SQLExecutor on the gpkg) to update the actual table. It's preferable to have an update method in the writer though.

Seems like that's how the writer is now... https://docs.safe.com/fme/html/FME-Form-Documentation/FME-ReadersWriters/ogcgeopackage/user-attributes.htm#PrimaryKey-FeatureOp

"Notice: FME Version 2020.0+: To maintain backwards compatibility, we have released an updated version of the OGC GeoPackage Reader/Writer. The previous version of this format (Format Type Identifier GEOPACKAGE) has been deprecated."

 

One answer to the questions is since a geopackage is sqlite, so you could insert to a temp table in the geopackage, and write your own update statement (SQLExecutor on the gpkg) to update the actual table. It's preferable to have an update method in the writer though.

Yeah I'm trying to do sqlexecutor as a work around. Got stuck in "attempt to write a readonly database" I found this https://community.safe.com/s/question/0D54Q0000913PEbSAM/geopackage-attempt-to-write-a-readonly-database but there is no solution. What do you need by insert into temp table, is it not possible to write directly (with REPLACE or DELETE and INSERT) to the target data table?

Hm, I've never had that error. I was thinking you'd write to some "output" table on the geopackage, FeatureWriter set to drop and create, then after that a SQL statement 

UPDATE MasterTable
SET field = Output.field
   ,etc = Output.etc
FROM Output
WHERE MasterTable.ChangeID = Output.ChangeID

That's worked for me at least. The drop and create is a low effort way to not leave behind the temp data that's not needed after it's been written to the main table

Hm, I've never had that error. I was thinking you'd write to some "output" table on the geopackage, FeatureWriter set to drop and create, then after that a SQL statement 

UPDATE MasterTable
SET field = Output.field
   ,etc = Output.etc
FROM Output
WHERE MasterTable.ChangeID = Output.ChangeID

That's worked for me at least. The drop and create is a low effort way to not leave behind the temp data that's not needed after it's been written to the main table

Hmm, I was able to write the changes to new table "Output" and tried SQLexecutor: 

 

UPDATE MasterTable
SET "id" = (
  SELECT Output."id"
  FROM Output
  WHERE MasterTable."ChangeID " = Output."ChangeID"
);

but got the same "attempt to write a readonly database" Also if i use drop and create in featurewriter before SQLexecutor, "Output" table is still left in the geopackage?

Hmm, I was able to write the changes to new table "Output" and tried SQLexecutor: 

 

UPDATE MasterTable
SET "id" = (
  SELECT Output."id"
  FROM Output
  WHERE MasterTable."ChangeID " = Output."ChangeID"
);

but got the same "attempt to write a readonly database" Also if i use drop and create in featurewriter before SQLexecutor, "Output" table is still left in the geopackage?

There seems to be a bug in FME 2021 when writing into geopackage with sqlexecutor that results in "attempt to write a readonly database" In FME 2022 I'm a able to write.

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.