2

I have been able to load rasters into my PostGIS database using the raster2pgsql tool and running it via the OSGeo4W shell. Files load where I want them to but appear in the database as one line files rather than sets of rows containing the pixel values that I want to be able to access. This a DTM raster and does appear to be of a format that is acceptable to PostGIS

raster2pgsql -s 27700 -C D://TEST_RUBBISH/terrain-5-dtm_3568783/nz/NZ25NE.asc staging.nz25ne|psql -h localhost -U postgres -p 5433 -d Init_Study_Areas_from10

Additionally, these rasters will not display in QGIS whether I drag them in directly or via the PostGIS database

Can anyone suggest where I am going wrong?

3

That is how rasters are stored in PostgreSQL/PostGIS.

You will have a line with an id, and a raster field. Usually rid and rast. If you tile the rasters (speeds up queries considerably) with -t 128x128 for example, you would have one line per tile.

You can add them to QGIS, using the DB manager. You right click on the layer in the DB manager and choose "Add to Canvas".

If you want to find out the raster value you can query the raster field:

PostGIS raster geoTiff simple Lat/Lon query example

SELECT ST_Value(rast, ST_SetSRID(ST_MakePoint(0,51.5),4326)) AS val
FROM my_raster
WHERE ST_Intersects(rast, ST_SetSRID(ST_MakePoint(0,51.5),4326));
| improve this answer | |
  • Thankyou .. this has worked and enabled me to get DTM values at the intersection with a point feature I have in the same geographic space. I am finding DB Manager a bit clunky but will persevere. I had hoped it would be rather more interactive in displaying the results of queries run in DB manager directly.rather than having to 'load as new layer' . Can you recommend any other visualisation tool that works well with Postgres/PostGIS? Thanks – TrevP Jun 16 at 14:44
  • Even QGIS is extremely unstable when using PostGIS rasters. I find them useful because all of our data is in PostGIS to do queries. But we visualize our rasters from the raw files on the file system. – HeikkiVesanto Jun 17 at 10:11
3

I highly recommend you add the -I command which will allow you to load the rasters into QGIS faster. this is a command I've used very succesfully to load a 90GB DEM into postgres and display it in QGIS. also the -t flag will help

raster2pgsql.exe -s 2263 -d -C -I -M -l 2,4,6,8,10,12,14,16,20,26,32,64,132 B:\dem.tif -F -t 100x100 dem | psql -d thedb -U me -p 5432 -h host

this is also a decent resource I found for beginers in PostGIS Rasters

https://github.com/lcalisto/workshop-postgis-raster

| improve this answer | |
  • 1
    thanks for this. great to get a resource recommended by someone who has been through the pain – TrevP Jun 16 at 14:40
  • PostGIS Raster is like the wild west -- scant docs and examples on the internet but once you get the basics down its super smooth especially querying between raster and vector datasets – ziggy Jun 16 at 16:54

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.