Error when searching/downloading data using AOI

120 views
Skip to first unread message

Cornelius Senf

unread,
Nov 9, 2020, 12:33:04 PM11/9/20
to FORCE
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

unread,
Nov 10, 2020, 5:12:34 AM11/10/20
to FORCE
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

unread,
Nov 10, 2020, 5:29:14 AM11/10/20
to FORCE
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

unread,
Nov 10, 2020, 5:57:33 AM11/10/20
to FORCE
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

unread,
Nov 10, 2020, 6:17:04 AM11/10/20
to FORCE
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

unread,
Nov 10, 2020, 7:56:05 AM11/10/20
to FORCE
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

unread,
Nov 10, 2020, 8:01:12 AM11/10/20
to FORCE
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

unread,
Nov 10, 2020, 8:05:46 AM11/10/20
to FORCE
>> 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

unread,
Nov 10, 2020, 8:40:29 AM11/10/20
to FORCE
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

unread,
Nov 11, 2020, 10:04:03 AM11/11/20
to FORCE
Hi both, 

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

Cheers,
David

thie...@gmail.com

unread,
Nov 11, 2020, 11:45:55 AM11/11/20
to FORCE
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

unread,
Nov 11, 2020, 2:42:26 PM11/11/20
to FORCE
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

unread,
Nov 12, 2020, 3:08:57 AM11/12/20
to FORCE
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

unread,
Nov 12, 2020, 3:22:10 AM11/12/20
to FORCE
Good catch! Just tried it out and it seems to solve the issue.

Stefan

unread,
Nov 12, 2020, 5:42:32 AM11/12/20
to FORCE
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

unread,
Nov 12, 2020, 10:42:38 AM11/12/20
to FORCE
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....

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

Cheers,
David
Reply all
Reply to author
Forward
0 new messages