A lot of people ask me about how to use PostGIS Raster and the GDAL PostGIS Raster driver in Windows systems. And most of them are, comprehensibly, using the last stable releases of both libraries (PostGIS and GDAL). The problem is PostGIS Raster and GDAL driver are being developed in these moments. There’s no stable release, so far.
PostGIS Raster was included as official part of PostGIS in October 2010. The old version, formerly named WKT Raster has been abandoned. So, if you want to use PostGIS Raster, with new functionalities and many bugs fixed, you’ll have to use the latest PostGIS development snapshot. The use of the old WKT Raster extension is strongly unrecommended, and there’s no support for it.
About GDAL, there was an important change in the driver on October 20st, 2010. A new version was committed, and this is the only version that works with PostGIS Raster. This new driver version was included in the release 1.8.0, currently the stable release. So, the use of an older release to connect with PostGIS Raster is also unrecommended.
With these facts in mind, I understand the Windows users who want to use PostGIS Raster and the GDAL driver are a bit unprotected. I’m not a Windows user, but I consider it my fault. For this reason, I’ve decided to create this howto. I’m going to install and configure PostGIS Raster and GDAL in Windows. And fortunately, you don’t need to compile anything. Let’s go for it:
Enviroment: Windows 7 32 bits
Step 1: Installing Python and Numpy
PostGIS Raster loader uses GDAL Python bindings and numpy. So, we’ll first install Python and numpy. Several options here:
- Python 2.5, unofficial numpy 1.5.1 (download numpy-1.5.1.win32-py2.5.exe from here)
- Python 2.6, unofficial numpy 1.5.1 (download numpy-1.5.1.win32-py2.6.exe from here)
- Python 2.7, unofficial numpy 1.5.1 (download numpy-1.5.1.win32-py2.7.exe from here)
- Python 2.6.6
- Numpy 1.5.1 (numpy-1.5.1.win32-py2.6.exe)
Step 2: Installing GDAL
I think the easiest way to have a complete GDAL installation (library + binaries + Python bindings) working on Windows is using the GDAL binaries mantained by Tamas Szekeres, here. Last GDAL versions from trunk are compiled daily with MSVC 2003, MSVC 2005 and MSVC 2008. Pick one. For example, the version compiled with MSVC 2003, here.
The faster way now would be to download the generic installer for the GDAL core components, and choose the complete installation
After the installation, you will need to add C:Program FilesGDAL to the PATH enviroment var. And create this new var:
UPDATE 2011-10-07: About the GDAL Python bindings
After installing GDAL core components, you must install the Python bindings separately. You can use this installer (for Python 2.7 and GDAL 1.8.0), or grab one of the executables existent at http://www.lfd.uci.edu/~gohlke/pythonlibs/
Depending on your Python installation, you can choose GDAL-1.8.1.win32-py2.6.exe, GDAL-1.8.1.win32-py2.7.exe, GDAL-1.8.1.win32-py3.1.exe or GDAL-1.8.1.win32-py3.2.exe
UPDATE 2011-03-25: Alicia Duarte, from IDESF, told me she was to additionally set the next enviroment vars to make it work:
GDAL_DRIVER_PATH=C:Program FilesGDALgdalplugins PROJ_LIB=C:Program FilesGDALprojlib
WARNING: This directory (GDAL Python bindings location) may not exist in your GDAL installation. In my case, the bindings are in C:Python27Libsite-packagesosgeo, and I didn’t need to define this variable.
UPDATE 2011-07-19: I think I introduced some confusion in the comments. I apologize for that. Several people are having problems with GDAL Python bindings. I’ll try to make it clearer: On one hand, there’s a sure thing: you need GDAL library + GDAL Python bindings installed in your system. There’s an easy way of doing it, by executing the generic installer: gdal-18-1310-core.msi. This installer includes the Python bindings, and they’re installed at C:Program FilesGDALpython directory by default. This should be enough. If you previously have a Python interpreter and a GDAL version installed on your system (check it from a console, executing gdalinfo –formats), you may want to simply install the proper GDAL Python bindings. Let’s assume your GDAL version is 1.8 (lower versions won’t work with PostGIS Raster), and your Python version is 2.7. Then, you simply need to install GDAL-1.8.0.win32-py2.7.msi. This installer will put GDAL Python bindings at C:Python27Libsite-packages by default. This is a different location than above, but at the end, you should have a working version of GDAL Python bindings on your system. Finally, the PostGIS Windows experimental binaries with PostGIS Raster support (step 4) are packed with their own version of GDAL library, used only by the raster2pgsql loader.
GDAL installed. Now, let’s go with step 3
Step 3: Installing PostgreSQL
I recommend Postgresql 8.4.x or higher. You can download the 1-click-installer from here, and install it in the common Windows’ way.
During the installation, you’ll be asked to install some nice additional stuff using the Application Stack Builder, like PostGIS 1.5.x or PostGIS 1.4.x. Don’t install anything. The PostGIS versions that can be installed doesn’t include PostGIS Raster. And you can’t install it separately, because, remember, now is part of PostGIS. We’re going to install last PostGIS version by hand. Don’t be afraid, it’ll be easy.
Step 4: Installing PostGIS
Once uncompressed, you can see this
Following the instructions of README.txt file, let’s edit makepostgisdb.bat to adapt the vars to our enviroment. We may want to change these vars:
set PGPORT=5432 set PGHOST=localhost set PGUSER=postgres set PGPASSWORD=password_of_postgres_user set THEDB=template_postgis20 set PGBIN=C:Program FilesPostgreSQL8.4bin set PGLIB=C:Program FilesPostgreSQL8.4lib After that, we make a backup of our PostgreSQL bin and lib directories (PGBIN and PGLIB). Just in case.
We now execute makepostgisdb.bat. Take into account you may want to execute the script as administrator, in case your Windows user doesn’t have needed privileges (it was my case).
UPDATE 2011-06-08: For all Windows XP Professional SP2 users:
when you run makepostgisdb.bat from the binaries of POSTGIS SVN2.0, please rename the libiconv-2.dll in bin folder of Postgresql/8.4/bin folder to any other name. Now run the makepostgisdb.bat file. You will not get any error of “operation aborted …. transaction block” (Thanks to C Patnaik)
Once executed, we have PostGIS 2.0 + PostGIS Raster installed and configured. A new database called template_postgis20 is created, enabled with PostGIS and PostGIS Raster. Easy! Let’s test it with the next query:
select postgis_full_version(), postgis_raster_lib_build_date(), postgis_raster_lib_version()
And the result:
Of course, you can create your own database using template_postgis20 as template. From the command line:
createdb -U postgres -T template_postgis20 your_database
Or from pgAdmin III:
If you want to use the SHP2PGSQL GUI, you must copy the postguisgui folder (inside bin folder in the downloaded PostGIS experimental binaries file) into pgAdmin III folder (mine is C:Program FilespgAdmin III1.12) and add the next lines to plugins.ini file, inside pgAdmin III folder too:
; ;PostGIS shp2pgsql-gui (Windows): ; Title=PostGIS Shapefile and DBF loader Command="$$PGBINDIRpostgisguishp2pgsql-gui.exe" -h "$$HOSTNAME" -p $$PORT -U "$$USERNAME" -d "$$DATABASE" -W "$$PASSWORD" Description=Open a PostGIS ESRI Shapefile or Plain dbf loader console to the current database. KeyFile=$$PGBINDIRpostgisguishp2pgsql-gui.exe Platform=windows ServerType=postgresql Database=Yes SetPassword=Yes
After that, you can use the GUI inside pgAdmin III:
Now you can load raster data into your database in 2 steps. In the first step, you will generate a SQL file. Open a command line and execute this:
python C:Program FilesPostgresql8.4binraster2pgsql.py -r image.tif -t tablename -o image.sql
As result of this step, a SQL file named image.sql will be created in the directory where you executed the Python line above.
In the second step, you’ll need to load the SQL file in your_database, by using your favourite PostgreSQL client. For example, pgAdmin. If your use the psql command line client, the line to execute is:
psql -h localhost -U postgres -d your_database -f image.sql
If you want to translate your raster data from the database to image files at disk again, execute gdal_translate.
gdal_translate PG:"dbname=your_database user=postgres table=your_table mode=2" your_file.tif
Basically, this is all what you need to work with PostGIS Raster in Windows. Anyway, take into account the next additional notes:
In step 2, I installed the whole GDAL library, including binaries and Python bindings. In step 4, I installed PostGIS 2.0 with all its pre-requisites. One of them is GDAL. The optimal way would be to use the existent GDAL library, but we’re working with pre-compiled binaries and DLLs. So, we have 2 copies of GDAL library:
- C:Program FilesGDALgdal18.dll: Installed with generic installer for GDAL, in step 2. Used by gdal_translate, when reading data from database, and in any operation involving GDAL, except the raster loading with raster2pgsql.py script
- C:Program FilesPostgreSQL8.4binlibgdal.dll: Installed with PostGIS Windows experimental binaries, in step 4. Used by raster2pgsql.py script, to load raster data into PostgreSQL.
Both library files were compiled from GDAL 1.8.0 from trunk. But they will be slightly different, for sure. This is a suboptimal solution, of course, but it shouldn’t cause problems, because libgdal.dll is only used when loading raster data into the database. The rest of operations involving GDAL will be executed linking with gdal18.dll
If you want to translate the raster data to any image format different from TIFF, you can use the -of option in gdal_translate. For example:
gdal_translate -of "PNG" PG:"dbname=your_database user=your_user password=your_password table=your_table mode=2" your_file.png
To get available raster formats:
UPDATE 2011-06-11: I changed the gdal_translate line. I’m using the ‘postgres’ user and the ‘trust’ method to authenticate this user (so, no password is needed), but it’s only a simplification. In general, you should use a different user and a password-enabled authentication method, like md5. Thanks to C.Patnaik again, because his comment remainded me this issue
It’s possible to work with the last version of PostGIS Raster and GDAL PostGIS Raster driver in Windows, but assuming that:
- You’re using experimental build. Not recommended for production enviroments.
- The previous versions of PostGIS Raster (WKT Raster) and GDAL PostGIS Raster driver (before GDAL 1.8.0) are discontinued. Don’t use them.
- It’s a suboptimal solution, because you install 2 copies of GDAL library. But I think this is the best available solution right now.
Do you have problems, something to add, modify or correct? Let me know it in the comments