1

I'm trying to setup a PostGIS table with NED raster tiles stored remotely. My raster2pgsql command works fine and results in a valid table without the -R flag, but when I use the -R flag (for an 'out-of-db' raster), the resulting table does not have a correctly defined CRS and the postGIS table (raster layer) is not visible when loading it into QGIS. This issue with the -R flag happens to both local dataset, as well as a COG located on an S3 bucket.

Is it possible to create a valid PostGIS raster table with just references (remote raster datasets)? Or am I going about this all wrong? This was also an interesting project that seems related, but i'm not sure where to take it.

Step 1: I have two NED 1" tiles that I'm testing and have a .txt doc listing these file locations:

Local (local_ned_1.txt):

NED_1/float/n48w114/floatn48w114_1.flt
NED_1/float/n47w114/floatn47w114_1.flt

S3 (s3_ned_1.txt):

/vsicurl/https://prd-tnm.s3.amazonaws.com/StagedProducts/Elevation/1/TIFF/n47w114/USGS_1_n47w114.tif
/vsicurl/https://prd-tnm.s3.amazonaws.com/StagedProducts/Elevation/1/TIFF/n48w114/USGS_1_n48w114.tif

Step 2: build a .vrt from the .txt files. This resulting .vrt works fine in QGIS for both local as well as the S3 cogtiff source:

Local: gdalbuildvrt -input_file_list local_ned_1.txt local_ned_1.vrt

S3: gdalbuildvrt -input_file_list s3_ned_1.txt s3_ned_1.vrt

Step 3: Analyze .vrt contents:

Local (just a sample showing paths):

<ComplexSource>
      <SourceFilename relativeToVRT="0">/Volumes/RASTER/NED/NED_1/float/n48w114/floatn48w114_1.flt</SourceFilename>
      <SourceBand>1</SourceBand>
      <SourceProperties RasterXSize="3612" RasterYSize="3612" DataType="Float32" BlockXSize="3612" BlockYSize="1" />
      <SrcRect xOff="0" yOff="0" xSize="3612" ySize="3612" />
      <DstRect xOff="0" yOff="0" xSize="3612" ySize="3612" />
      <NODATA>-9999</NODATA>
    </ComplexSource>
    <ComplexSource>
      <SourceFilename relativeToVRT="0">/Volumes/RASTER/NED/NED_1/float/n47w114/floatn47w114_1.flt</SourceFilename>

S3 (just a sample showing paths):

<ComplexSource>
  <SourceFilename relativeToVRT="0">/vsicurl/https://prd-tnm.s3.amazonaws.com/StagedProducts/Elevation/1/TIFF/n47w114/USGS_1_n47w114.tif</SourceFilename>
  <SourceBand>1</SourceBand>
  <SourceProperties RasterXSize="3612" RasterYSize="3612" DataType="Float32" BlockXSize="256" BlockYSize="256" />
  <SrcRect xOff="0" yOff="0" xSize="3612" ySize="3612" />
  <DstRect xOff="0" yOff="3600" xSize="3612" ySize="3612" />
  <NODATA>-999999</NODATA>
</ComplexSource>
<ComplexSource>
  <SourceFilename relativeToVRT="0">/vsicurl/https://prd-tnm.s3.amazonaws.com/StagedProducts/Elevation/1/TIFF/n48w114/USGS_1_n48w114.tif</SourceFilename>

Step 4: Load .vrt into postGIS with raster2pgsql:

Use the -R flag, this results in a table that does not show the raster layer when the table is loaded into QGIS, and has an invalid CRS:

raster2pgsql -c -s 4269 -R -I -Y -C /Users/username/local_ned_1.vrt public.ned1_local | psql -h localhost -p 5432 -U postgres -d database_name

DON'T use the -R flag, this results in a table that loads the actual raster data into postGIS (not what i'm after) but loads fine into QGIS and has a valid CRS (EPSG:4269)

raster2pgsql -c -s 4269 -F -I -Y -C /Users/username/local_ned_1.vrt public.ned1_local | psql -h localhost -p 5432 -U postgres -d database_name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.