[postgis-users] Rasteroutput of a complex query to a file

[Bborie Park]

On Fri, Jan 20, 2012 at 6:45 AM, Andreas Neumann <a.neumann at carto.net> wrote:
> Hi,
>
> I see that there are functions for raster output like ST_AsGdalRaster(),
> ST_AsTiff(), ST_AsJPEG(), etc.
> How can I export this to a valid rasterfile? Do I need to write my own
> script or is there a trick with psql?
>
> Here is my query:
>
> SELECT ST_AsTiff(ST_Clip(
>  (SELECT ST_Union(
>    ST_Intersection(dtm.rast, ST_BUFFER(lieg.the_geom,-0.5))) AS raster FROM
> dtm.dtmav2002 dtm, av_user.liegenschaften lieg WHERE lieg.gru_nummer =
> 'B7294' AND ST_Intersects(dtm.rast, lieg.the_geom)),
> (SELECT the_geom FROM av_user.liegenschaften WHERE gru_nummer =
> 'B7294'),true)) AS clipped_raster;
>
> How can I output this to a tiff-file in the filesystem? I tried with
>
> psql -d uster -U an -t -c "query as above" >test.tif
>
> but when I open test.tif it tells me that there is no valid tiff-header.
>
> Any idea how can export the result of my query?
>
> Thanks,
> Andreas
>

You won't be able to use psql to do the outputting as psql does some
automatic escaping of the bytea.  You can use your favorite
programming language though.  robe has examples using ASP and PHP...

http://www.bostongis.com/blog/index.php?/archives/174-Minimalist-Web-based-ASP.NET-PostGIS-2.0-Spatial-GeometryRaster-Viewer.html

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

-bborie

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu