PostGIS Raster: Outputting raster into GDAL supported file?

Question

I have import a raster into my postGIS database via raster2pgsql. My raster tiled to 100x100. Now I'm trying to export (output) the raster to GDAL supported file (for example: PNG).

Following the instruction in postGIS manual dev 2.1 (page 68). However, I don't get any output file in destination folder.

Here's what I type in SQL tool in pgAdmin III

<!-- language: lang-sql -->
SELECT lo_export(demo.oid,'D:\demo_rast.png')
FROM
  (SELECT oid,
          lowrite(lo_open(oid, 131072), png) AS num_bytes
   FROM (
         VALUES (lo_create(0),
                 ST_AsPNG(
                            (SELECT rast
                             FROM landsat
                             WHERE rid=1)))) AS v(oid,png)) AS demo
WHERE demo.oid = 1
    WHERE demo.oid = 1

Answer 1

Your query must be done in two steps:

1) First, you must to obtain the oid for the image object, and to create the object into a temporary buffer.

SELECT oid, lowrite(lo_open(oid, 131072), png) As num_bytes
 FROM 
 (VALUES (lo_create(0),
    ST_AsPNG((SELECT rast FROM landsat WHERE rid=1))
 )) As v(oid,png);

This is the oid, in my case:

Note: you'll obtain a different oid everytime you run the query.

2) Using the oid from the previous query, you'll be able to extract the image into the desired path:

SELECT lo_export(117989, 'd:\demo_rast.png');

The data output is:

That means your image is already generated, so, you can check your path.

I don't know your case, but this is how my data looks:

If I want to extract the third image, for example, I'll use rid = 3 in the first query.