1

I'm working on setting up a server for my university's agriculture program to manage data from many of the experiment stations around the state. Part of this effort is loading and making raster data (multispectral .tif files) from the growing season available to all the professors and researchers. I am new to SQL servers and the postgis extension, but I have figured out how to create roles, create servers, add extensions, and all of that good stuff. What I cannot discover is how to upload raster data into the server. Currently I am testing on a localhost, but will eventually need to be able to load into a server running Ubuntu.

I have tried to use the SQL command raster2pgsql, but I cannot get it to execute. Is this function standard in all distributions of postgis? Or do I need to reinstall the extension from another source (homebrew)?

I have also tried to load rasters through the QGIS plugin "Load them all", which also seems to be unable to load my raster (or vector) data to the local server. Is this the simplest way to load batches of data into a server, or is this unnecessary?

I'm sure I'm probably missing some simple step in this entire process since I am so new to it, but I appreciate your help.

J.Oldag
  • 11
  • 1
  • 2

1 Answers1

3

raster2pgsql is pretty much the way to import raster files to PostGIS.

You can use it to upload directories of files, though I sometimes prefer to first stitch related tiles together by using QGIS's Raster->Miscellaneous->Merge method and then just uploading the merged tiles as a single geotif.

When you say that you can't get raster2pgsql to execute, I assume that you mean you are getting an error along these lines?

bash: raster2pgsql: command not found

If so, there are two possible issues:

1) Is PostGIS installed locally?

2) Is raster2pgsql on your path?

It sounds like you already have PostGIS installed locally via homebrew. raster2pgsql comes standard (at least in my experience), so the issue is likely then that your bash environment can't resolve the raster2pgsql executable path.

You could try find the path to postgres using something along the lines of:

brew info postgres

Note that I don't use home-brew so you may need to fiddle with this a bit, and then investigate the resultant path to find the postgres bin directory, which may be somewhere such as:

/path/to/homebrew/postgres/Versions/9.6/bin/raster2pgsql

Alternatively, you could try:

sudo find ~/ -name raster2pgsql

If you still can't find it, try downloading and installing the postgres app for mac, in which case you'll find raster2pgsql located at:

/Applications/Postgres.app/Contents/Versions/9.6/bin/raster2pgsql

If you go the postgres app route, you don't have to actually install and run a second or substitute Postgres database via the app, it is simply a convenient way to get your hands on the raster2pgsql executable.

Once you find the full path, you can then simply call the raster2pgsql command with the full path, e.g.

/Applications/Postgres.app/Contents/Versions/9.6/bin/raster2pgsql ...etc...

Or else add the postgres bin directory to your environment PATH variable.

songololo
  • 4,724
  • 5
  • 35
  • 49