PostGIS Raster – Loading Raster in PostGIS and Linking with Existing Table

postgispostgresqlrastersql

Relatively new to PostGIS and I am currently exploring the options to make one of my PostgreSQL Tables spatial. Essentially I want to store a raster object in a table that also contains essential metadata. Every raster in the database has to be uniquely linked to the ID´s and information stored in the table.

I was under the impression that I could just write the raster into the same table (similar as with vectors where I could just add geometry columns)? With a query like this I can only write a raster to a new (non-existing) table, thus creating hundreds of tables if my raster files vary in extent, origin and projection (very likely).:

raster2pgsql -s 32739 -I -C -F -M -Y myfile.tif -t 100x100 Map > rasQuery.sql
psql -U test -h localhost -d test -f rasQuery.sql

My existing table looks like this, where I created a raster column.

CREATE TABLE Map(
    id serial PRIMARY KEY,  
    second_id INT references AreaOfInterest,
    RasterFile raster,
    RasterType char(250),
    RasterAuthor char(250),
    Acquisition date,
);
  • What is the standard way to load and link rasters with existing tables? raster2psql fails for me stating that the table map already exists, so I guess I have to specify a column as well? Or did I miss a mandatory command here?

The currently working alternative for me would be to just store the filepath in a text column in the Map Table.

Running PostgreSQL 9.4dev + PostGIS 2. on Debian Linux in a test-environment.

EDIT:
I am already a bit further.
After adding 

rast raster,
filename raster,

to my table and the -a flag in front of map in the raster2pgsql command it returns some new errors.
Now it says:

 rt_raster_from_wkb: wkb size (9)  < min size (61)
CONTEXT:  COPY map, row 1, column filename: „myfile.tif“

Any ideas?

Best Answer

I tried your commands and changed 

filename raster,

to 

filename char(250),

, assuming you meant to use a string for that (and declaring it as a raster is a typo??).

The commands seem to work without -C. With -C, I got some warnings/notices about numeric field overflow and no_data. But I guess that's just something in my data.

What I tried is:

echo  " \
CREATE TABLE Map( \
    id serial PRIMARY KEY, \
    rast raster, \
    filename char(250), \
    RasterType char(250), \
    RasterAuthor char(250), \
    Acquisition date \
);" | psql -U postgres -h localhost -d opengeo

#    second_id INT references AreaOfInterest, \   # AreaOfInterest not defined

raster2pgsql -a -s 4326 -I -M -F -C tif_dir/*.tif -t 100x100 public.map | psql -U postgres -h localhost -d opengeo
Related Question