Hello @jnknnn, thanks for posting. So you'd like to use the original extents of features when creating a geopackage, instead of the extent for the template file? Let me know if I am understanding this correctly.
The OGC Geopackage Reader is capable of reading from an S3 bucket, so this could be one way to update the existing geopackages.
Happy to help, Kailin.
Hello @jnknnn, thanks for posting. So you'd like to use the original extents of features when creating a geopackage, instead of the extent for the template file? Let me know if I am understanding this correctly.
The OGC Geopackage Reader is capable of reading from an S3 bucket, so this could be one way to update the existing geopackages.
Happy to help, Kailin.
Yes, you understood correctly. Template file extent is applied to all files generated by featurewriter.
I know you can download from S3 but is there a way to update the extents of the S3 files without downloading them?
In my testing the BBOX was updating when writing with a template. Which version are you working with (I'm using FME 2022.2.5).
Regardless there is a workaround - Geopackage is just an SQLite database with a rigid structure. After writing the Geopackage with FME you can then use an SQLExcecutor or SQLite write to update the required table. Definitely a bit of a hack but should work well.
Take a look at your output geopackage file with a SQLite reader and you can explore the table structure. The table you want to update is probably "gpkg_contents"
I tried to use the SQLUpdator to UPDATE a SQLite database via URL but it didn't work. It also didn't work inside the zip file sadly.
You would need to download, unzip, update, rezip and re-upload. Pretty painful but you could automate it with FME I guess
I'm using 2021. To correct what I earlier said: Extent is updating but featurewriter seems to update extent from both the template file and the file wirtten. So if I use zoom to extent in QGIS it zooms to extent assuming both of those files are present.
"After writing the Geopackage with FME you can then use an SQLExcecutor or SQLite write to update the required table"
Do you mean running SQLExcecutor after featurewriter? How would i get the extent if the file is already zipped and saved in this point? Or do you mean reading the files separetly with SQLite reader?
I'm using 2021. To correct what I earlier said: Extent is updating but featurewriter seems to update extent from both the template file and the file wirtten. So if I use zoom to extent in QGIS it zooms to extent assuming both of those files are present.
"After writing the Geopackage with FME you can then use an SQLExcecutor or SQLite write to update the required table"
Do you mean running SQLExcecutor after featurewriter? How would i get the extent if the file is already zipped and saved in this point? Or do you mean reading the files separetly with SQLite reader?
Ahh right ok.
Maybe there is a way to modify/remove the extent from the initial template file (i.e., create a new one)? Not sure how that would work
Yeah, if using the SQLExcecutor you'd need to write the geopackage unzipped and the update with SQLExcecutor it and then zip it and then upload it.
So in the process of creating the geopackage file FME will build a spatial index. This index (I think) is stored in a a series of tables, one of which is called "rtree_<tableName>_geom" - in here the bounds are defined for each feature.
I think you could use the statement to update the metadata table based on the content of the spatial index. Here I have used "@Value(table_name)" to be the be the name of the table which holds the data. This process assumes that the spatial index is connect.
UPDATE gpkg_contents
SET
min_x = (SELECT MIN(minx) from rtree_@Value(table_name)_geom),
min_y = (SELECT MIN(miny) from rtree_@Value(table_name)_geom),
max_x = (SELECT MAX(maxx) from rtree_@Value(table_name)_geom),
max_y = (SELECT MAX(maxy) from rtree_@Value(table_name)_geom)
where table_name = '@Value(table_name)'
