Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I would like to save a forecast which is saved as geoTiffs into a postgresQL database. I've been able to import the rasters using:

raster2pgsql -s 0 -I -d tiff/field*.tiff -F public.latest | psql -d optigrid

THis gives me a table latest which looks like

rid  | rast  | filename
1      ...     field1.tiff
2      ...     field10.tiff

and so forth.

However I would like to add another column which stores the time the forecast, something like

rid  | rast  | filename      | forecast_date
1      ...     field1.tiff      2012-08-09
2      ...     field10.tiff     2012-08-10

Is this possible?

Thanks Matt

share|improve this question

2 Answers 2

Try with something like ALTER TABLE followed by UPDATE. I mean:

ALTER TABLE public.latest ADD COLUMN forecast_date date;

UPDATE TABLE public.latest SET forecast_date = (...)
share|improve this answer

Perhaps too late for OP, but might help others: why not do it by a batch file? I assume OP's situation and nullable forecast_date column:

@echo off

setlocal
SET PGPORT=5432
SET PGHOST=localhost
SET PGUSER=postgres
SET PGPASSFILE=*[path_to_your_.pgpass_file][1]*

if "%1"=="" ( 
  set /p %1= "Set path to the image:" 
)

raster2pgsql -a -d tiff/field*.tiff -F %1 public.latest | psql -d my_db

if "%2"=="" ( 
  set /p %2= "Set forecast date:" 
)

psql -d my_db -c "UPDATE public.latest SET forecast_date = %2 WHERE forecast_date IS NULL;"

pause 
endlocal

Comments:

  • @echo off should remove unnecessary noise (printing the code)

  • setlocal is not mandatory, if you don't need to use more databases through the same command line; it would be very useful if you had set your password there; however, I assume you do it right and store your password in the .pgpass file

  • the "if" parts are there to allow you call the script both with and without parameters

  • my_db is your database

  • among raster2pgsql parameters, -a is important - this way you add to already established database; otherwise, you would have to replace the following SQL statement with the one jorgeas80 suggested

  • that pgsql statement contains SQL UPDATE. Watch out for badly imported rows, otherwise they would get the same date as the new raster; this could be improved, but I assume that this version suffices

  • pause causes the script to wait until you hit any key; useful if you run the script from anywhere but the command line

This is a result of a day or two of trying to solve roughly the same problem as the OP had; I can't confess I really understand command line, batch files, raster2pgsql and psql (I didn't know them at all before I started), so something might be done much better some other way. But this solution works, at least for me.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.