2

I have used PostGIS's raster2pgsql to import my multi-band raster data into my PostGIS database. Here is the code that can do this operation:

raster2pgsql -I -C -e -Y -F /home/jj_user/input.tif output_table_name | psql -U my_db_username -d my_db_name -h localhost -p 5432

Here is it's SQL auto-generated codes in pgAdmin4:

-- Table: public.modis_5_band

-- DROP TABLE public.modis_5_band;

CREATE TABLE public.modis_5_band
(
    rid integer NOT NULL DEFAULT nextval('modis_5_band_rid_seq'::regclass),
    rast raster,
    filename text COLLATE pg_catalog."default",
    CONSTRAINT modis_5_band_pkey PRIMARY KEY (rid),
    CONSTRAINT enforce_srid_rast CHECK (st_srid(rast) = 0),
    CONSTRAINT enforce_scalex_rast CHECK (round(st_scalex(rast)::numeric, 10) = round(231.656358264, 10)),
    CONSTRAINT enforce_scaley_rast CHECK (round(st_scaley(rast)::numeric, 10) = round(- 231.656358264, 10)),
    CONSTRAINT enforce_width_rast CHECK (st_width(rast) = 41),
    CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 41),
    CONSTRAINT enforce_same_alignment_rast CHECK (st_samealignment(rast, '0100000000DCCA0BE300F56C40DCCA0BE300F56CC0E17A146EA77552410358CBAA7D8A4F41000000000000000000000000000000000000000001000100'::raster)),
    CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 6),
    CONSTRAINT enforce_pixel_types_rast CHECK (_raster_constraint_pixel_types(rast) = '{8BUI,8BUI,8BUI,8BUI,8BUI,8BUI}'::text[]),
    CONSTRAINT enforce_nodata_values_rast CHECK (_raster_constraint_nodata_values(rast) = '{0.0000000000,0.0000000000,0.0000000000,0.0000000000,0.0000000000,0.0000000000}'::numeric[]),
    CONSTRAINT enforce_out_db_rast CHECK (_raster_constraint_out_db(rast) = '{f,f,f,f,f,f}'::boolean[]),
    CONSTRAINT enforce_max_extent_rast CHECK (st_envelope(rast) @ '01030000000100000005000000E17A146EA775524175E439B6F0774F41E17A146EA77552410358CBAA7D8A4F41A8345DE8ED7E52410358CBAA7D8A4F41A8345DE8ED7E524175E439B6F0774F41E17A146EA775524175E439B6F0774F41'::geometry) NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE public.modis_5_band
    OWNER to postgres;
-- Index: modis_5_band_st_convexhull_idx

-- DROP INDEX public.modis_5_band_st_convexhull_idx;

CREATE INDEX modis_5_band_st_convexhull_idx
    ON public.modis_5_band USING gist
    (st_convexhull(rast))
    TABLESPACE pg_default;

This creates a table like below:

enter image description here

input.tif file has 5 bands. The above code saves all of the bands in one row.

What I want is to save each band in a new row with it's name. Each band's name is a string from a to e.

The desired table should look like this:

enter image description here

  • didn't see anything like this in documentation of raster2pgsql. But you can load one band at a time by specifying the band value in the command and name the band value in db. – Arun Jul 6 at 7:37
  • Yeah. I was thinking the same thing. It seems that there is no other option for this. – Muser Jul 6 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.