[postgis-users] postgis raster st_value

[John Callahan]

Thanks for the advice.  I did retile my rasters down to 100x100 simply be
dropping and reloading (easy enough through a simple script.)   I didn't
notice an appreciable difference although I didn't check the logs for exact
times.

Interesting thought on putting all tiles for all layers in the same table.
 I had to read that a few times to understand it.  I will try that and
compare to what I have now, which is a php script that queries each of my
17 raster layers and returns those results in a couple of seconds.  Good
enough for now.

- John

***********************************************
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
*************************************************



On Tue, Feb 21, 2012 at 10:32 AM, Pierre Racine <Pierre.Racine at sbf.ulaval.ca
> wrote:

> > Thank you.  It works great!  The following works for a single point
> against a
> > single raster:
> >
> > SELECT
> > ST_Value(rast, ST_SetSRID(ST_Point(187251.0, 119422.0), 26957)) FROM
> geol1
> > WHERE ST_Intersects(rast, ST_SetSRID(ST_Point(187251.0, 119422.0),
> > 26957)::geometry, 1);
>
> It will work even faster if you retile everything to smaller tiles and
> create an index on your raster column (which I have no doubt you did). You
> can use the new ST_Tile() plpgsql prototype to retile everything without
> reloading:
>
>
> http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_tile.sql
>
> It's a bit slow but can work overnight...
>
> > Could anyone recommend the best way to query multiple rasters using the
> same
> > point for each?  I've seen examples pulling multiple points from the
> same rasters
> > but not the same point from multiple rasters.  I have about 10-20
> rasters (geol2,
> > geol3..) I need to find the value of at a single point.
>
> If you have an application that want to do that often I would recommend to
> store all the tiles for all the layers in the same table with a column
> identifying the semantic of each set of tile. You then just do a query like
> you did, adding the point id and the semantic column. For each point you
> will get as many rows as you have layers. Transforming everything in a one
> multiple column row is another story: You have to JOIN the table with
> itself as many time as you have layers.
>
> Pierre
> _______________________________________________
> 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/20120222/0498d9dd/attachment.html>