I have a raster which is split into 30 tiles and I want to dump them all into PNGs.

I've read the documentation http://postgis.net/docs/RT_ST_AsPNG.html but still don't know how to dump it well.

If i use ST_Histogram, i get numbers, think the raster is well inserted,

SELECT band, (stats).*
FROM (SELECT rid, band, ST_Histogram(rast, band) As stats
      FROM "corine-g100_06-v15" CROSS JOIN generate_series(1,3) As band
      WHERE rid=2) As foo;


The output of ST_AsPNG is the binary blob of a PNG file, so you output it to a file as you would any other binary document saved in PostgreSQL . It really depends what you are using as your tool.

For example we have examples of this that output to browser in thsese quick examples we put together to demonstrate how it is done.

http://www.bostongis.com/blog/index.php?/archives/175-Minimalist-Web-based-PHP-PostGIS-2.0-Spatial-GeometryRaster-Viewer.html Download the examples to see.

Only difference in your case is you would output to a file instead ofr browser stream.

You can also do it with plpython, plperl, or any other postgresql pl language that can write to a file if you just want to output to the database server. We'l try to put together examples of that in the next month or so.

| improve this answer | |

There are a couple of ways to do this, noted here: http://petewarden.com/2013/08/31/how-to-save-an-image-to-disk-from-postgis/ and here: http://www.postgis.org/documentation/manual-svn/using_raster.xml.html#RasterOutput_PSQL. I've used both methods successfully with PostGIS 2.1.1, but found the first method a bit more concise on a Linux server. For example:

COPY (SELECT encode(ST_AsTiff(ST_ColorMap((SELECT rast FROM worldclim.bio12_2_5m), 1, 
'100% 1,70,54
90% 1,70,54
80% 1,108,89
70% 2,129,138
60% 54,144,192
50% 103,169,207
40% 166,189,219
30% 208,209,230
20% 236,226,240
10% 255,247,251
0% 255,247,251
nodata 0,0,0')), 'hex')) 
TO '/var/www/https/maps/tmp/bio12_2_5m.hex';

Then drop back to the OS and do:

xxd -p -r bio12_2_5m.hex > bio12_2_5m.tiff

Depending on your rasters this should work for a PNG dump too, but mine only worked to a TIFF.

| improve this answer | |

Actually I was searching for a solution to dump multiple raster tiles to Image files as well and came up with a solution to create a view for a single tile and dump it afterwards using gdal_translate into a tiff file. But PNG should work the same way. To Automate this I simply wrote a short linux-shell script to loop over my ID's in the database. I guess there are similar ways for windows users:

psql -d mydb -t -A -F ";" -c  "select rid from dsm where ent = 0" | 
while read -a Record ; do psql mydb -c "create or replace view temp AS
select * from dsm where gid = ${Record[0]};" && 
gdal_translate -of GTiff PG:"host='localhost' port='5432' dbname='mydb' schema='public' table='temp' mode='2'" ${Record[0]}_DSM.tiff
| improve this answer | |

Your Answer

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

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