[postgis-users] ST_AsJpeg

[Paragon Corporation]

Yah psql doesn't make it easy.  I've put in an example of doing it in docs.
Which takes tips from this article:
http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-tri
ck-challenge.html
 
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.
 
I think strk has one too though his probably requires perl or some unixy
thing.
 
 
Docs I don't think have rebuilt yet, but basic script is this:
 
SELECT oid, lowrite(lo_open(oid, 131072), png) As num_bytes
 FROM 
 ( VALUES (lo_create(0), 
   ST_AsPNG( (SELECT rast FROM aerials.boston WHERE rid=1) ) 
  ) ) As v(oid,png);
-- you'll get an output something like --
   oid   | num_bytes
---------+-----------
 2630819 |     74860
 
-- next note the oid and do this replacing the c:/test.png to file path
location
-- on your local computer
 \lo_export 2630819 'C:/temp/aerial_samp.png'
 
-- this deletes the file from large object storage on db
SELECT lo_unlink(2630819);
 
Thanks,
Regina
h <http://www.postgis.us> ttp://www.postgis.us

 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Joan
Sent: Tuesday, February 14, 2012 4:23 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ST_AsJpeg


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.  
Regina, I have tried using \g to out put to a file but the file is
corrupted.


I appreciate the help.

Joan

On 14 February 2012 21:27, Paragon Corporation <lr at pcorp.us> wrote:


We have this documented in the docs.

http://www.postgis.org/documentation/manual-svn/using_raster.xml.html#RT_Ras
ter_Applications

There is an example for doing it with PHP, .NET, Java, and PLPython

Sorry don't have one for psql only.  Haven't tried using psql for outputting
files.

Regina
http://www.postgis.us






> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On

> Behalf Of Pierre Racine
> Sent: Tuesday, February 14, 2012 11:05 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] ST_AsJpeg
>

> So you might have to write a script connecting to PostgreSQL
> and writing the file for you. See an example in:
>
> http://www.bostongis.com/blog/index.php?/archives/175-Minimali
> st-Web-based-PHP-PostGIS-2.0-Spatial-GeometryRaster-Viewer.html
>
> Pierre
>
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users- bounces at postgis.refractions.net] On
> Behalf Of
> > Joan
> > Sent: Tuesday, February 14, 2012 10:10 AM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] ST_AsJpeg
> >
> > Yes, I want to create a jpeg/tiff file. I am new to
> cursors, how would
> > that work in a query.
> >
> >
> > On 14 February 2012 15:39, Pierre Racine
> <Pierre.Racine at sbf.ulaval.ca> wrote:
> >
> >
> >     Thanks,
> >
> >     Do you see any way to make the file directly readable
> (without having
> > to edit it)?
> >
> >
> >     Pierre
> >
> >     > -----Original Message-----
> >     > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-
> > users-
> >
> >     > bounces at postgis.refractions.net] On Behalf Of Sandro Santilli
> >     > Sent: Tuesday, February 14, 2012 9:28 AM
> >     > To: PostGIS Users Discussion
> >
> >     > Subject: Re: [postgis-users] ST_AsJpeg
> >     >
> >     > On Tue, Feb 14, 2012 at 09:22:21AM -0500, Pierre Racine wrote:
> >     > > Yes. You want to write the file directly to the filesystem?
> > Sandro wrote
> >     > something in the list about this recently but I can't
> find it. Sandro?
> > Robe?
> >     > >
> >     >
> >     > I ended up binary-editing the result afterwards.
> >     >
> >     > Anyway, first step is declaring a binary cursor,
> redirecting output
> > to a file and
> >     > calling the function.
> >     >
> >     > For the binary editing, I looked at another file with
> my expected
> > format to tell
> >     > where it started (human eye pattern matching).
> >     >
> >     > > You can also use gdal_translate...
> >     >
> >     > Much better, when it works (it wasn't working for me).
> >     >
> >     > --strk;
> >     >
> >     >   ,------o-.
> >     >   |   __/  |    Delivering high quality PostGIS 2.0 !
> >     >   |  / 2.0 |    http://strk.keybit.net
> >     >   `-o------'
> >     >
> >     > _______________________________________________
> >     > postgis-users mailing list
> >     > postgis-users at postgis.refractions.net
> >     > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >     _______________________________________________
> >     postgis-users mailing list
> >     postgis-users at postgis.refractions.net
> >     http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120214/0ebf50e1/attachment.html>