PostGIS Out of db VRT containing JPEG2000 GDAL 2.1 OpenJPEG

Question

Build GDAL 2.1 with OpenJPEG
Build PostGIS with above GDAL build
Created VRT file with SENTINEL2 Driver / JP2 files.

gdal_translate SENTINEL2_L2A:S2A_USER_MTD_SAFL2A_PDMC_20160520T042556_R030_V20160520T000224_20160520T000224.xml:10m:EPSG_32756 10m.vrt -of vrt

raster2posgresql the VRT into PostGIS with -R flag (out of raster db using full path to file)

raster2pgsql -R /home/ben/S2A_USER_PRD_MSIL2A_PDMC_20160520T042556_R030_V20160520T000224_20160520T000224.SAFE/10m.vrt -b 2 -C -t 128x128 -P test.T56JKN_20160520_A_10m_b02 | psql -d giddb

When I query:

select 
     ST_Value(rast, ST_Transform(ST_PointFromText('POINT(149.9878 -29.3507)', 4326),32756)) 
from 
     test.T56JKN_20160520_A_10m_b02
where
     ST_Intersects(rast,ST_Transform(ST_PointFromText('POINT(149.9878 -29.3507)', 4326),32756))

I get error:

ERROR: rt_raster_from_gdal_dataset: Could not get data from GDAL raster SQL state: XX000 Context: PL/pgSQL function st_value(raster,integer,geometry,boolean) line 18 at RETURN

Other notes:
Out of db works perfectly fine with a VRT containg a tif file and also with a tif file on it's own.

Any advice or comments welcome. I am not sure if this is even possible?

Edit: Would using the Kakadu JPEG2000 driver help?

UPDATE:

OK so I've built everything from scratch again and can confirm:

JP2OpenJPEG is listed in PostGIS query: "SELECT short_name FROM ST_GDALDrivers();"

JP2OpenJPEG is available in GDAL by running "gdal_translate --formats"

I can gdal_translate a SENTINEL2 driver created VRT containing .jp2 to GTiff

PostGIS version information: POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.1.0, released 2016/04/25" LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY RASTER

When trying to query raster I now get error: ERROR: rt_raster_from_gdal_dataset: Could not get data from GDAL raster CONTEXT: PL/pgSQL function st_value(raster,integer,geometry,boolean) line 18 at RETURN

Other notes: When I run raster2pgdsql with -s [dataset epsg] I get error: ERROR: rt_raster_from_gdal_dataset: Could not get data from GDAL raster CONTEXT: SQL function "st_transform" statement 1

When I run without -s flag it completes OK with and without tiling.

Answer 1

I have solved the problem.

When generating VRT with GDAL I did not use full path for Sentinel2 xml file. This is fine for using gdal_translate etc but causes an error inside PostGIS as full path is required both to the VRT and inside it.

Wrong way to generate VRT that will be used as out-of-db raster in PostGIS:

gdal_translate -of VRT SENTINEL2_L2A:S2A_USER_MTD_SAFL2A_PDMC_20160510T084235_R030_V20160510T000223_20160510T000223.xml:10m:EPSG_32756 10m_wrong.vrt

Correct way:

gdal_translate -of VRT SENTINEL2_L2A:/home/ben/S2A_USER_PRD_MSIL2A_PDMC_20160510T084235_R030_V20160510T000223_20160510T000223.SAFE/S2A_USER_MTD_SAFL2A_PDMC_20160510T084235_R030_V20160510T000223_20160510T000223.xml:10m:EPSG_32756 10m_correct.vrt

Also I compiled GDAL 2.1 with following configuration:

./configure --with-openjpeg=/usr/local --without-jasper --with-python=yes