raster2pgsql is the default tool for importing rasters to PostGIS. Even though GDAL itself does not provide means to load rasters to the database, raster2pgsql is compiled as a part of PostGIS and therefore supports the very same formats as the GDAL version appropriate for given version of PostGIS.
raster2pgsql is a command-line tool. In order to review its parameters, simply type in the console:
raster2pgsqlWhile taking a while to get familiar with the raster2pgsql help is an advised approach, here are some params that worth highlighting:
-G: Prints a list of GDAL formats supported by the given version of the utility; the list is likely to be extensive.-s: Sets the SRID of the imported raster.-t: Tile size - expressed as width x height. If not provided, a default is worked out automatically in the range of 32-100 so it best matches the raster dimensions. It is worth remembering that when importing multiple files, tiles will be computed for the first raster and then applied to others.-P: Pads tiles right / bottom, so all the tiles have the same dimensions.-d|a|c|p: These options are mutually exclusive:d: Drops and creates a table.a: Appends data to an existing table.c: Creates a new table.p: Turns on prepare mode. So no importing is done; only a table is created.
-F: A column with raster name will be added.-l: Comma-separated overviews; creates overview tables namedo_<overview_factor>_raster_table_name.-I: Creates GIST spatial index on the raster column.-C: Sets the standard constraints on the raster column after the raster is imported.
For the examples used in this section, we'll use Natural Earth's 50M Gray Earth raster.
As you remember, ogr2ogr has a ogrinfo tool that can be used to obtain the information on a vector dataset. GDAL's equivalent for raster files is called gdalinfo and is as worthy as its vector brother:
gdalinfo GRAY_50M_SR_OB.tifYou should get a similar output:
Driver: GTiff/GeoTIFF Files: GRAY_50M_SR_OB.tif GRAY_50M_SR_OB.tfw Size is 10800, 5400 Coordinate System is: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]] Origin = (-179.999999999999970,90.000000000000000) Pixel Size = (0.033333333333330,-0.033333333333330) Metadata: AREA_OR_POINT=Area TIFFTAG_DATETIME=2014:10:18 09:28:20 TIFFTAG_RESOLUTIONUNIT=2 (pixels/inch) TIFFTAG_SOFTWARE=Adobe Photoshop CC 2014 (Macintosh) TIFFTAG_XRESOLUTION=342.85699 TIFFTAG_YRESOLUTION=342.85699 Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left (-180.0000000, 90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"N) Lower Left (-180.0000000, -90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"S) Upper Right ( 180.0000000, 90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"N) Lower Right ( 180.0000000, -90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"S) Center ( -0.0000000, 0.0000000) ( 0d 0' 0.00"W, 0d 0' 0.00"N) Band 1 Block=10800x1 Type=Byte, ColorInterp=Gray
Before we get down to importing the raster, let's splits into four parts using gdalwarp utility. This way, we'll be able to show how to import a single raster and a set of rasters:
gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te -180 -90 0 0 GRAY_50M_SR_OB.tif gray_50m_partial_bl.tif gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te -180 0 0 90 GRAY_50M_SR_OB.tif gray_50m_partial_tl.tif gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te 0 -90 180 0 GRAY_50M_SR_OB.tif gray_50m_partial_br.tif gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te 0 0 180 90 GRAY_50M_SR_OB.tif gray_50m_partial_tr.tif
For each command, you should see a similar output:
Creating output file that is 5400P x 2700L. Processing input file GRAY_50M_SR_OB.tif. 0...10...20...30...40...50...60...70...80...90...100 - done.
Having prepared the data, we can now move onto importing it.
In order to import a single raster file, let's issue the following command:
raster2pgsql -s 4326 -C -l 2,4 -I -F -t 2700x2700 gray_50m_sr_ob.tif data_import.gray_50m_sr_ob | psql -h localhost -p 5434 -U postgres -d mastering_postgisYou should see a similar output:
Processing 1/1: gray_50m_sr_ob.tif BEGIN CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 1 (...) INSERT 0 1 CREATE INDEX ANALYZE CREATE INDEX ANALYZE CREATE INDEX ANALYZE NOTICE: Adding SRID constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding scale-X constraint (...) ---------------------- t (1 row) addoverviewconstraints ------------------------ t (1 row) addoverviewconstraints ------------------------ t (1 row) COMMIT
The executed command created 3 tables: the main raster table called data_import.gray_50m_sr_ob and two overview tables called data_import.o_2_gray_50m_sr_ob and data_import.o_4_gray_50m_sr_ob. The command also created the GIST index and brought in the filename. The raster has been split into tiles of 2700 x 2700 pixels.
Let's import a directory of rasters now. We have four files with the file name mask gray_50m_partial*.tif. In order to import all the files at once, we'll issue the following command:
raster2pgsql -s 4326 -C -l 2,4 -I -F -t 2700x2700 gray_50m_partial*.tif data_import.gray_50m_partial | psql -h localhost -p 5434 -U postgres -d mastering_postgisYou should see a similar output:
Processing 1/4: gray_50m_partial_bl.tif BEGIN CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 1 INSERT 0 1 INSERT 0 1 Processing 2/4: gray_50m_partial_br.tif (...) Processing 3/4: gray_50m_partial_tl.tif (...) Processing 4/4: gray_50m_partial_tr.tif (...) CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN NOTICE: Adding maximum extent constraint CONTEXT: PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN addrasterconstraints ---------------------- t (1 row) addoverviewconstraints ------------------------ t (1 row) addoverviewconstraints ------------------------ t (1 row) COMMIT
The command used to import multiple rasters was very similar to the one we used to import a single file. The difference was a filename mask used in place of a filename: gray_50m_partial*.tif. If we had used a bit more griddy pattern such as *.tif, all the TIF files present in a directory would be imported.
When processing multiple files, one can pipe the output to psql without the connection info specified as psql params, but in such a case, equivalent environment variables will have to be set (on Windows, use the set command, and on Linux, export):
set PGPORT=5434 set PGHOST=localhost set PGUSER=postgres set PGPASSWORD=somepass set PGDATABASE=mastering_postgis raster2pgsql -s 4326 -C -l 2,4 -I -F -t 2700x2700 gray_50m_partial*.tif data_import.gray_50m_partial | psql