[postgis-users] Retrieving Data from PostGIS Raster using ST_Value

[Pierre Racine]

If you want to get the pixel value under a point just do:

SELECT ST_Value(rast, ST_Transform(ST_SetSRID(ST_Point(yourLongitude, yourLatitude), 4326),  ST_SRID(rast))
FROM yourTable

That should be pretty fast.

In any case you should load your raster when loading it (-k100x100 or -k 20x20) and then index it:

CREATE INDEX srtm_tiled_rast_gist_idx  ON srtm_tiled USING GIST (ST_ConvexHull(rast));

Interpolating the elevation of a point is another story. You could build a grid of 8, 15 or 24 points around your lat/long, query the elevation at those points and use your algorithm to compute the interpolation.

Really getting the elevation for 1 or 25 points should be a matter of microseconds.

Pierre

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of elliott
> Sent: Thursday, October 13, 2011 9:38 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Retrieving Data from PostGIS Raster using ST_Value
> 
> The table contains SRTM3 data.  What I would really like to do is retrieve the
> altitude for any given lat/lon.  We currently have scientific codes that read the
> SRTM3 flat file into an array and interpolate the value for the lat/lon pair.  We
> would like to retrieve the data from a database instead of flat file thinking the
> performance would be better.  However, the times retrieving the array from the
> database are many orders of magnitude worse than just reading the array in
> from a flat file.
> 
> On 10/12/2011 8:34 PM, Pierre Racine wrote:
> > If you want to get all the values for every pixel do:
> >
> > SELECT (ST_DumpAsPolyogns(rast)).val
> > FROM your table
> >
> > Can I ask why you want to do that? Normally people want to extract values
> intersecting with a polygon or a point.
> >
> > You can also get the histogram of the raster using ST_Histogram().
> >
> > Pierre
> > ________________________________________
> > From: postgis-users-bounces at postgis.refractions.net
> > [postgis-users-bounces at postgis.refractions.net] On Behalf Of elliott
> > [elliott at cpi.com]
> > Sent: Wednesday, October 12, 2011 9:04 AM
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] Retrieving Data from PostGIS Raster using
> > ST_Value
> >
> > Hello Everyone,
> >
> > I am very new to using PostGIS Raster.  I need to retrieve data fields
> > from a tile loaded into the database.  Using ST_Height and ST_Width, I
> > get the number of rows and columns and iterate over these using the
> > ST_Value to retrieve the data.  This is extremely slow taking 1/2 hour
> > to get all of the data required.  Is there a faster way to accomplish this?
> >
> > Thanks!
> > _______________________________________________
> > 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