Windows 10, PostgreSQL 11

I successfully managed to export a .tif raster into PostgreSQL using raster2pgsql:

raster2pgsql -s 27700 -I -C -M -Y "D:\Data_Location\country.tif" -F -t 50x50 public.country_rast | psql -U username -d dbname -h hostaddress -p 5432

Now I'd like to query the raster using a function which takes an XY coordinate and returns the value of the raster cell it intersects.

I use this:

SELECT ST_VALUE (rast,1,(ST_GeomFromText('POINT(319006 212230)',27700))) 
FROM public.country_rast

However the query returns ALL 1 million rows of the raster, wheras I only want the value that the given XY intersects. How can I do this?

  • @Taras I think that's unrelated. It doesn't do anything but throw a syntax error. – Theo F Jun 19 at 13:59
  • I am not sure if this will help, but remove the parenthesis around ST_GeomFromText. Otherwise I can't see anything wrong with your approach – John Powell Jun 20 at 15:36
  • @JohnPowell I'm afraid that makes no difference at all. – Theo F Jun 20 at 15:50
  • OK, I have run similar queries many times, and can't see anything wrong. Are you able to share the tif? – John Powell Jun 20 at 16:05
  • @JohnPowell sure. here is a sample of it (i still have the same issues with this sample as with the main tif) we.tl/t-rsRT17wMwo upload that in the way I have and let me know what you find. – Theo F Jun 20 at 16:52
SELECT ST_VALUE (rast,1,(ST_GeomFromText('POINT(319006 212230)',27700))) 
FROM public.country_rast
WHERE ST_Intersects(rast, ST_GeomFromText('POINT(319006 212230)',27700))

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.