2

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?

  • Have you tried removing a number of flags in your command? e.g. removing -Y which specifies using a copy statement – raphael Oct 12 '14 at 23:12
1

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
  • Yes, the typo was a thing. I got to to work with the following flags: raster2pgsql -s 32739 -I -F -C -M -a Map – Curlew Oct 13 '14 at 9:13

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.