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')
  (SELECT oid,
          lowrite(lo_open(oid, 131072), png) AS num_bytes
   FROM (
         VALUES (lo_create(0),
                            (SELECT rast
                             FROM landsat
                             WHERE rid=1)))) AS v(oid,png)) AS demo
WHERE demo.oid = 1
    WHERE demo.oid = 1

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
 (VALUES (lo_create(0),
    ST_AsPNG((SELECT rast FROM landsat WHERE rid=1))
 )) As v(oid,png);

This is the oid, in my case:

enter image description here

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:

enter image description here

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:

enter image description here

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

  • You're completely right Sorin. Doing it in 2 steps works.
    – Jackie
    Jan 22, 2014 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.