Rasters to PostGIS

Question

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?

Answer 1

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

Answer 2

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));