Convert PostGIS raster table into DTED2

Question

I've the following table in PostGIS

-- Table: public.elevation_data

-- DROP TABLE public.elevation_data;

CREATE TABLE public.elevation_data
(
  rid integer NOT NULL DEFAULT nextval('elevation_data_rid_seq'::regclass),
  rast raster,
  CONSTRAINT elevation_data_pkey PRIMARY KEY (rid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.elevation_data
  OWNER TO username;

-- Index: public.elevation_data_st_convexhull_idx

-- DROP INDEX public.elevation_data_st_convexhull_idx;

CREATE INDEX elevation_data_st_convexhull_idx
  ON public.elevation_data
  USING gist
  (st_convexhull(rast));

created with the following command

raster2pgsql -I -s 4326 -t auto DataSet\Elevation\*.dt2 public.elevation_data | psql -U username -d databasename

Now I want to export this table to a .dt2 file, so I can exchange it. How can I export the table in the raster format?

EDIT:

I've looking documentation and I've tried following query:

COPY(SELECT ST_AsGDALRaster(rast, 'DTED') FROM public.elevation_data) TO 'd:\dteddata.dt2' (FORMAT binary);

It creates a very big file (over 7Gb) when the table was created by importing two files of about 50Mb in total. When I try to open this file, for example in QGIS, it says me that the file is not supported. The same if I try to save it as GeoTiff with the command:

COPY(SELECT ST_AsGDALRaster(rast, 'GTiff') FROM public.elevation_data) TO 'd:\dteddata.tiff' (FORMAT binary);

even if in this case the file has a size of about 51Mb, that seems more correct to me.

I've followed instruction from this page for retrieving DTED data, and joining them with this other one, because ST_AsGDALRaster returns a bytea, and the second links explains how to save it to a file, but I'm doing it the wrong way of course...

Answer 1

You can't do it the way you are doing especially if you have a table because COPY TO is for tables and uses a delimeter to separate each row. Also I think there is some other stuff added to the binary that makes it incompatible.

I usually use the large object support for this which doesn't mangle it.

As I describe here: http://www.postgis.us/presentations/PGConfUS2017_PostGISTop10.html#/7/4

Try:

DROP TABLE IF EXISTS tmp_out ;

CREATE TABLE tmp_out AS 
SELECT lo_from_bytea(0, 
       ST_AsGDALRaster(ST_Union(rast), 'GTiff')  
        ) AS loid
  FROM public.elevation_data;

SELECT lo_export(loid, 'd:/dteddata.tiff')
   FROM tmp_out;

SELECT lo_unlink(loid)
  FROM tmp_out;

The only problem with the above approach is if your raster is big enough, you may run into the max array size limit in PostgreSQL when unioning which would mean you can't export it as a single file.

What you could do instead is use GDAL Toolkit:

If you have GDAL Toolkit installed, which you can get from https://trac.osgeo.org/gdal/wiki/DownloadingGdalBinaries

You can do this from the command line and even if your table is made up of multiple rows and is big, it will output as a single file

gdal_translate -of GTiff "PG:host=localhost port=5432 dbname='yourdatabase' user='postgres'  password='whatever' schema=public table=elevation_data mode=2" 'd:\dteddata.tiff'