Question

How to correct or update Geopackage extent from FeatureWriter Template File?

  • 12 May 2023
  • 5 replies
  • 23 views
jnknnn
Rank
Badge +4

I'm using template file (geopackage) from one feature of the dataset to set the datastructure in FeatureWriter. FeatureWriter zips geopackages in dataset parameter like this: $(FME_SHAREDRESOURCE_TEMP)/Folder/FeatureTypeName_@Value(attribute).zip/FeatureTypeName_@Value(attribute).gpkg. Zip-files are then uploaded to S3 bucket using S3Connector.

 

It's working great but it seems that the extent of the template file is used for all geopackage/zip-files. Is there a way to not get the extent from template file but rather from actual features to get correct extent for each geopackage? I noticed the error when usign "zoom to extent" in QGIS. And if not, is there way to update the extents of geopackage/zip -files already in S3 bucket, preferably by not dowloading all of them locally first.

5 replies

kailinatsafe
Rank
Userlevel 3
Badge +13

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.

imageHappy to help, Kailin.

jnknnn
Rank
Badge +4

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.

imageHappy 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?

virtualcitymatt
Rank
Userlevel 4
Badge +26

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"

 

image 

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

jnknnn
Rank
Badge +4

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?

virtualcitymatt
Rank
Userlevel 4
Badge +26

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

 

 

 

 

 

Reply


Community Stats

28,717
Posts
109,495
Replies
36,560
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.