Error when searching/downloading data using AOI

[Cornelius Senf]

Hi all! 

I ran into an error that is likely related to https://groups.google.com/g/force_eo/c/QL3U_-Hwd5M

I used following command to search/download Sentinel-2 data:

force-level1-csd -n -c 0,70 -d 20190101,20191231 -s S2A,S2B Sentinel2/metadata Sentinel2/level1/berchtesgaden Sentinel2/level1/berchtesgaden/l1_pool.txt Sentinel2/aoi/aoi_berchtesgaden_nationalpark_epsg4326.gpkg

But it returns following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(SELECT sentinel2.PRFID FROM sentinel2, aoi_berchtesgaden_nationalpark_epsg4326 WHERE ST_Intersects(sentinel2.geom, ST_Transform(aoi_berchtesgaden_nationalpark_epsg4326.geom, 4326))): no such column: sentinel2.geom

I checked the merged.gpdk file that is created and it contains all Sentinel granules intersecting with the study site. So the actual search has worked, but it seems FORCE cannot access the geometry. I also tried out entering the AOI as shape file but without luck.

Any ideas? In the other thread the solution was to run FORCE from Docker, but I would like to continue with my installation.

Cornelius

[Stefan]

Hi Cornelius,

I'm still not sure what caused the issue in the other thread. It should not make a difference whether FORCE is used inside or outside of a Docker (I only use Docker for testing).

Are you using the latest dev version?
Just a shot in the dark: Are you using GDAL 3? All tests were done on v2 so far and I do not know if anything changed regarding the sql handling.

As a workaround, you can of course simply select the tiles you need yourself by providing a tile list instead of a shapefile/gpkg.

[Cornelius Senf]

Hi Stefan!

I use gdal 2.4.2, so that shouldn't be a problem. I also change the location from a network drive to a local drive, but didn't solve the issue. Selecting the tiles directly worked, though, so it seems that the issue is related to the sql-request itself. Anyway, I will continue working with the workaround.

Thanks!

Cornelius

P.s. if you need for info from my side for fixing the issue, let me know!  

[Stefan]

Thanks for the offer Cornelius!

If it's the latest dev version that you're using I probably should have a closer look again.

Could you send the full paths you're using + the gpkg (via email if they contain any sensitive info) in that case?

Stefan

[Cornelius Senf]

I use FORCE 3.5.1. I just saw that there is a new version 3.5.2, which I can't install at the moment, though (no admin rights at the server).

The full call is: force-level1-csd -n -c 0,70 -d 20190101,20191231 -s S2A,S2B /home/csenf/Projects/Sentinel2/metadata Sentinel2/level1/berchtesgaden /home/csenf/Projects/Sentinel2/level1/berchtesgaden/l1_pool.txt /home/csenf/Projects/Sentinel2/aoi/aoi_berchtesgaden_nationalpark_epsg4326.gpkg

The gpkg can be found under following link (can't attach it here for some reason...): https://www.dropbox.com/s/bvl8bh4x06es44q/aoi_berchtesgaden_nationalpark_epsg4326.gpkg?dl=0

Thanks!

[Stefan]

Thank you. Just as in the other thread, I'm unable to reproduce the error.

Does the following command throw the same error?
ogrinfo merged.gpkg -dialect sqlite -sql "SELECT sentinel2.geom FROM sentinel2"

[Stefan]

By the way: You don't need admin rights if you want to install a later version of FORCE (or a dev version for new features / testing). Just change the bindir in the makefile. You'd only run into problems if there are new dependencies which need admin rights to install.
A couple of things in force-level1-csd have already changed since version 3.5.1 (and also 3.5.2, latest changes are in the dev branch on github) since it's still a fairly new piece of software.

You probably noticed this already, but your study area is covered by two tiles which are redundant for your study area.

Stefan

[Cornelius Senf]

>> Does the following command throw the same error?

>> ogrinfo merged.gpkg -dialect sqlite -sql "SELECT sentinel2.geom FROM sentinel2"

Yes, it throws an error.

> By the way: You don't need admin rights if you want to install a later version of FORCE (or a dev version for new features / testing). Just change the bindir in the makefile. You'd only run into problems if there are new dependencies which need

> admin rights to install.  A couple of things in force-level1-csd have already changed since version 3.5.1 (and also 3.5.2, latest changes are in the dev branch on github) since it's still a fairly new piece of software.

Good to know. Will update as soon as possible and see whether this fixes the issue.

> You probably noticed this already, but your study area is covered by two tiles which are redundant for your study area.

Yes, I know. It's just a quick test case.

Thanks for you help Stefan!

[Stefan]

Okay, thanks for testing. As a last test - could you upload the merged.gpkg you're getting?
I suspect there is an issue with GDAL / SQL that I honestly don't know how to troubleshoot.

[david frantz]

Hi both, 

if this persists, it would be good to document this as issue on github. 

Cheers,

David

[thie...@gmail.com]

Just a wild guess: Maybe this is related to the Spatialite version, as indicated here https://gdal.org/drivers/vector/gpkg.html#sql

@Cornelius, does the test statement mentioned before work, if you change the dialect, i.e.

ogrinfo merged.gpkg -dialect INDIRECT_SQLITE -sql "SELECT sentinel2.geom FROM sentinel2" 

[Cornelius Senf]

The merged.gpkg is here: https://www.dropbox.com/s/ucr7pc0qx73c564/merged.gpkg?dl=0

Changing the statement to ogrinfo merged.gpkg -dialect INDIRECT_SQLITE -sql "SELECT sentinel2.geom FROM sentinel2" also returned the error.

[thie...@gmail.com]

I can reproduce the error with your file. For whatever reason, the geometry column in your layer is called geometry. I think force-level1-csd expects the column to be named geom, as this is the default in gpkg. https://gdal.org/drivers/vector/gpkg.html#layer-creation-options.

So

ogrinfo merged.gpkg -dialect SQLITE -sql "SELECT sentinel2.geometry FROM sentinel2"

should work on your machine Cornelius.

@Stefan: Maybe either control the naming of the geometry column creating the merged.gpkg using GEOMETRY_NAME creation option or try to catch both variants in the tool!?

[Cornelius Senf]

Good catch! Just tried it out and it seems to solve the issue.

[Stefan]

Thanks a ton Fabian and Cornelius.

I am still puzzled as to why the geometry column would be named differently. As Fabian pointed out the name 'geom' is defined by the GPKG driver.

I implemented a fix. Cornelius if you have the time, maybe you could see if this works:
https://github.com/ernstste/force/blob/develop/bash/force-level1-csd.sh

[david frantz]

Hi guys,

adding to this, not sure if it is the same error or something related. I just came across this error:

force-level1-csd -s LT04,LT05,LE07 -c 0,70 download/metadata download/data download/queue.txt crete.shpSearching for footprints / tiles intersecting with geometries of AOI shapefile...
ERROR 1: sqlite3_exec(COMMIT) failed: database is locked
ERROR 1: sqlite3_exec(PRAGMA synchronous = OFF) failed: Safety level may not be changed inside a transaction
ERROR 1: sqlite3_exec(BEGIN) failed: cannot start a transaction within a transaction
ERROR 1: Transaction not established
ERROR 1: A file system object called 'merged.gpkg' already exists.
ERROR 1: GPKG driver failed to create merged.gpkg

Apparently, geopackage doesn't work on network drives in Unix....

https://gis.stackexchange.com/questions/327214/is-there-a-fix-for-sqlite3-execommit-failed-database-is-locked-qgis-3-6-pyt

When doing the same on a local directory, it works without a problem.

Cheers,

David