1

I'm using raster2pgsql to upload rasters to a postgres database I execute the following command in cmd to create a sql that will add the raster

"C:/Program Files/PostgreSQL/9.5/bin/raster2pgsql" -s 32630 -a -f raster "Y:/Sen2R_Download/prueba_sergio/raster3/SCL/S2B2A_20180731_137_sen2r_SCL_10.tif" sentinel > Y:/Sen2R_Download/prueba_sergio/rastersql27.sql

This sql file will upload the raster to the field raster in the table sentinel My doubt is if there is a way to add more parameters to upload to the database in the same table in other fields. Because I want that the sql file created not only upload the raster but it upload a path and a company id in other fields of the table at the same time

  • As PostGIS rasters are usually tiled this will likely lead to a lot of repeated data, or slow performance from the raster because the individual rasters are so large. Unless you are adding different rasters to the same table you may just want to use a PostgreSQL comment on the table itself. COMMENT ON TABLE raster_table IS 'path/to/file;company_id'. Or, even better, have a separate table handle the metadata details for each raster table. CREATE TABLE raster_meta (uid serial, table_name text, file_path text, company_id int); – thayer May 14 at 15:08
  • Yes, I will add diferent rasters to the same table but all this will be authomatic. I'm programming a R script to download sentinel rasters and then for each raster use raster2pgsql to create de sql file and then execute it so I need to have some data asociated to each raster – scf May 14 at 16:00
0

It seems that you can add a new column and fill it with filename by using option -F https://postgis.net/docs/using_raster_dataman.html#RT_Raster_Loader.

For your other needs you can

  1. Create an empty raster table with parameter -p Prepare mode, only create the table.
  2. Alter the new table and add the fields that you will need with SQL.
  3. Append raster data into the new table raster2pgsql by using mode -a Append raster(s) to an existing table.
  4. Update the table with SQL after running raster2pgsql and write your metadata into the fields that you created.

Another option would be to to create a separate table for metadata and insert a new row into it with SQL after running raster2pgsql. Primary key of the raster table would be best for joining the tables and you should create a foreign key contraint. It is up to you if you would rather run the extra SQL commands afterwards with psql or if you would rather edit the .sql file that raster2pgsql generates.

| improve this answer | |

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.