<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 9.00.8112.16440"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial>Yah psql doesn't make it easy. I've put in an example of
doing it in docs. Which takes tips from this
article:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial><A
href="http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html">http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial>There are other examples in that article but this is the only
one that doesn't require Perl or Unix so is more cross
platform.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial>I think strk has one too though his probably requires perl or
some unixy thing.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial>Docs I don't think have rebuilt yet, but basic script is
this:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial>SELECT oid, lowrite(lo_open(oid, 131072), png) As
num_bytes<BR> FROM <BR> ( VALUES (lo_create(0), <BR>
ST_AsPNG( (SELECT rast FROM aerials.boston WHERE rid=1) ) <BR> ) ) As
v(oid,png);<BR>-- you'll get an output something like --<BR>
oid | num_bytes<BR>---------+-----------<BR> 2630819
| 74860<BR> <BR>-- next note the oid and do this
replacing the c:/test.png to file path location<BR>-- on your local
computer<BR> \lo_export 2630819 'C:/temp/aerial_samp.png'<BR> <BR>--
this deletes the file from large object storage on db<BR>SELECT
lo_unlink(2630819);</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=588072522-14022012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=588072522-14022012></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>Thanks,</FONT></FONT></FONT></DIV>
<DIV><SPAN class=588072522-14022012></SPAN><SPAN
class=588072522-14022012></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2>R<SPAN class=588072522-14022012>egina</SPAN></FONT></FONT></FONT></DIV>
<DIV><SPAN class=588072522-14022012></SPAN><SPAN
class=588072522-14022012></SPAN><FONT face=Arial><FONT color=#0000ff><FONT
size=2><A href="http://www.postgis.us">h<SPAN
class=588072522-14022012>ttp://www.postgis.us</A></SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=588072522-14022012></SPAN></FONT></FONT></FONT><BR> </DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px"
dir=ltr>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Joan<BR><B>Sent:</B> Tuesday, February 14, 2012 4:23 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users]
ST_AsJpeg<BR></FONT><BR></DIV>
<DIV></DIV>The last two solutions result in a black square, which I think is
the tile. But it's a good start. I will have to look more into that.
<DIV>Regina, I have tried using \g to out put to a file but the file is
corrupted.<BR>
<DIV><BR></DIV>
<DIV>I appreciate the help.</DIV>
<DIV><BR></DIV>
<DIV>Joan</DIV>
<DIV><BR>
<DIV class=gmail_quote>On 14 February 2012 21:27, Paragon Corporation <SPAN
dir=ltr><<A href="mailto:lr@pcorp.us">lr@pcorp.us</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>We have this documented in the docs.<BR><BR><A
href="http://www.postgis.org/documentation/manual-svn/using_raster.xml.html#RT_Ras"
target=_blank>http://www.postgis.org/documentation/manual-svn/using_raster.xml.html#RT_Ras</A>ter_Applications<BR><BR>There
is an example for doing it with PHP, .NET, Java, and PLPython<BR><BR>Sorry
don't have one for psql only. Haven't tried using psql for
outputting<BR>files.<BR><BR>Regina<BR><A href="http://www.postgis.us"
target=_blank>http://www.postgis.us</A><BR>
<DIV class="im HOEnZb"><BR><BR><BR><BR><BR>> -----Original
Message-----<BR>> From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>>
[mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>]
On<BR></DIV>
<DIV class="im HOEnZb">> Behalf Of Pierre Racine<BR>> Sent: Tuesday,
February 14, 2012 11:05 AM<BR>> To: PostGIS Users Discussion<BR>>
Subject: Re: [postgis-users] ST_AsJpeg<BR>><BR></DIV>
<DIV class=HOEnZb>
<DIV class=h5>> So you might have to write a script connecting to
PostgreSQL<BR>> and writing the file for you. See an example
in:<BR>><BR>> <A
href="http://www.bostongis.com/blog/index.php?/archives/175-Minimali"
target=_blank>http://www.bostongis.com/blog/index.php?/archives/175-Minimali</A><BR>>
st-Web-based-PHP-PostGIS-2.0-Spatial-GeometryRaster-Viewer.html<BR>><BR>>
Pierre<BR>><BR>> > -----Original Message-----<BR>> > From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>>
> [mailto:<A href="mailto:postgis-users-">postgis-users-</A> <A
href="mailto:bounces@postgis.refractions.net">bounces@postgis.refractions.net</A>]
On<BR>> Behalf Of<BR>> > Joan<BR>> > Sent: Tuesday, February
14, 2012 10:10 AM<BR>> > To: PostGIS Users Discussion<BR>> >
Subject: Re: [postgis-users] ST_AsJpeg<BR>> ><BR>> > Yes, I want
to create a jpeg/tiff file. I am new to<BR>> cursors, how would<BR>>
> that work in a query.<BR>> ><BR>> ><BR>> > On 14
February 2012 15:39, Pierre Racine<BR>> <<A
href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</A>>
wrote:<BR>> ><BR>> ><BR>> > Thanks,<BR>>
><BR>> > Do you see any way to make the file directly
readable<BR>> (without having<BR>> > to edit it)?<BR>>
><BR>> ><BR>> > Pierre<BR>> ><BR>>
> > -----Original Message-----<BR>> >
> From: <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A><BR>>
> [mailto:<A href="mailto:postgis-">postgis-</A><BR>> >
users-<BR>> ><BR>> > > <A
href="mailto:bounces@postgis.refractions.net">bounces@postgis.refractions.net</A>]
On Behalf Of Sandro Santilli<BR>> > > Sent: Tuesday,
February 14, 2012 9:28 AM<BR>> > > To: PostGIS Users
Discussion<BR>> ><BR>> > > Subject: Re:
[postgis-users] ST_AsJpeg<BR>> > ><BR>> >
> On Tue, Feb 14, 2012 at 09:22:21AM -0500, Pierre Racine
wrote:<BR>> > > > Yes. You want to write the file
directly to the filesystem?<BR>> > Sandro wrote<BR>> >
> something in the list about this recently but I can't<BR>>
find it. Sandro?<BR>> > Robe?<BR>> > >
><BR>> > ><BR>> > > I ended
up binary-editing the result afterwards.<BR>> >
><BR>> > > Anyway, first step is declaring a
binary cursor,<BR>> redirecting output<BR>> > to a file and<BR>>
> > calling the function.<BR>> >
><BR>> > > For the binary editing, I looked at
another file with<BR>> my expected<BR>> > format to tell<BR>>
> > where it started (human eye pattern
matching).<BR>> > ><BR>> > >
> You can also use gdal_translate...<BR>> >
><BR>> > > Much better, when it works (it wasn't
working for me).<BR>> > ><BR>> >
> --strk;<BR>> > ><BR>> > >
,------o-.<BR>> > > | __/
| Delivering high quality PostGIS 2.0 !<BR>> >
> | / 2.0 | <A
href="http://strk.keybit.net"
target=_blank>http://strk.keybit.net</A><BR>> > >
`-o------'<BR>> > ><BR>> >
> _______________________________________________<BR>> >
> postgis-users mailing list<BR>> > > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> _______________________________________________<BR>>
> postgis-users mailing list<BR>> > <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> ><BR>><BR>>
_______________________________________________<BR>> postgis-users
mailing list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></DIV></DIV></BLOCKQUOTE></DIV><BR></DIV></DIV></BLOCKQUOTE></BODY></HTML>