Adding raster to PostgreSQL database using raster2pgsql?

Question

I added a raster to my PG database using the raster2pgsql and when I try to load it from QGIS it says that I need to specify it's geometry. Is that a common problem, and if so, how can I solve it?

The command that I used was

raster2pgsql -s 2100 -c C:\Users\user\Desktop\data\raster_2100.tif public.raster2 | psql -h localhost -U user1 -p

and SELECT * FROM raster_column prints this:

enter image description here

• scabecks: Did you import as an in- or out-of-database raster? Does it show as registered in the raster_columns catalog?
• Ka_Papa: The raster that I am referring to was at my local disk, so I imported in to my database at first and then out of my database into my QGIS session. Now, about that raster_columns are you referring to my database? Because I have no column named like that.
• scabecks: What does it return if you run 'SELECT * FROM raster_columns' ?
• Ka_Papa: @scabecks see my edit
• scabecks: From that you can tell it's not registering properly. Try re-importing the raster, adding the -C flag (-I to build an index too is a good idea often).

Answer 1

When I first attempted to upload the raster to my database I was convinced that raster2psql was the best way to do that, but I decided to find another way through R, mainly from the library rpostgis, now I haven't solved it using raster2psql but because I am working with R as well, I have to say that the command pgWriteRast does the same work.

Answer 2

Well if you have the raster in your filesystem you can create the sql file with

raster2pgsql <rasteroptions> <raster_file> <some_schema.sometable> > out.sql
E.g. raster2psql -s 4326 -I -C -M coast.tif -F -t 100x100 public.coast > coast.sql

You can see all the rasteroptions here: https://postgis.net/docs/using_raster_dataman.html

After you created the SQL-file you need to add the data to the database/table: This is the syntax to connect to the database and enter the data by the SQL-file to the database.

psql -h localhost -d dbname - U user -f path/to/file.sql

At least this is how it works for me.

And if you are a Windows user you should split this commend into two

raster2pgsql -s 2100 -c C:\Users\user\Desktop\data\raster_2100.tif public.raster2 | psql -h localhost -U user1 -p

• juraj murcko: thanks, and why Windows users should split this into two commands? any difference if I use it in a pipe in one liner command?