6

I have a large raster map which I loaded into postgis with 1000x1000 meter tiles... the corresponding table has about 200 rows, each row representing a tile. I want to get the raster value for a specific coordinate, but I don't know how to do that most efficiently.

I did something like:

select ST_Value(rast, ST_Transform(ST_GeomFromText('POINT(lat lon)',4326),mysrid)) from rasterdata;

but it gives me a lot of:

Attempting to get pixel value with out of range raster coordinates:...

messages because finally only one tile contains the coordinate that I'm searching for.

  • Good reply by nickves, but lat and long seem switched in 'ST_GEOMFROMTEXT()'. – Franck Theeten Jun 15 '15 at 19:42
10

Add a where constrain. that way you will make use of postgis powerful gist indexes.

select 
     ST_Value(rast, ST_Transform(ST_GeomFromText('POINT((lon lat)',4326),mysrid)) 
from 
     rasterdata 
where
     ST_Intersects(rast,ST_GeomFromText('POINT(lon lat)',4326),mysrid))
 ;

Also if you haven't build the index table yet you can do this way:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometrycolumn] );

then run VACUUM ANALYSE to populate the index:

VACUUM ANALYSE rasterdata

EDIT: Changed the order of params in function ST_POINT() since they are (lon lat) and not the other way around as Franck Theeten noticed above.

  • user16794: Can you please mark this answer up, and mark it as "answered"? Thanks. – BradHards Apr 4 '13 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.