You are here: TUTWiki>SigALa>SpectralLibrary>PostgisDatabase (revision 4)


PostGIS? is an extension to the PostgreSQL? database. Extension provides support for the GIS datatypes such as georeferenced points, lines, polygon, rasters.

Setting up PostGIS? database

Setting up database in Windows is recommended with prebuilt binary distributions as building from the source is a lot more difficult than on unix systems.

On linux install or build latest PostgreSQL? database, currently 9.2. Download latest sources of PostGIS?. Install or build latest proj4, GEOS and GDAL libraries (1.10 recommended). Compile Postgis and install.

Easiest way to create database is to create the database with a pgadmin gui tool and after creating the database add the extensions postgis and postgis topology.

Query performance

There is some issues with default settings on using PostgreSQL? with PostGIS? extension. With default settings Postgresql tries to use sequential scans on raster tables, which means really slow queries on raster tables. The point on using GIS types for this kind of data is fast lookup with queries. To get PostgreSQL? to use GiST? index lookups some settings need to be modified in the postgresql.conf setting random_page_cost from the default 4 to 1 should help with raster tables index scans. Be sure to check postgresql memory settings and read a few postgis/postgresql performance tuning guides.

Raster, InDB? and OutDB? rasters

Rasters can be loaded into Postgis database with raster2pgsql tool. If your rasters need modification or transforms use gdal tools to make scripts and process files into gdal vrt format if possible.

Storing rasters directly into database as inDB raster is convenient, but it comes with few problems. First you have to transform data into suitable format before uploading data. Secondly backing up/restoring database with huge amount of data is very slow. OutDB? rasters are stored as file references, only image slicing information is stored in the database.


Viewing material from the PostGIS? database is easy with Quantum GIS. Getting it to work fast with PostGIS? is another story. Quick guide to access PostGIS? data. Create PostGIS? connection in the Add PostGIS? layer tool. User DB Manager to add raster layers.

Compile QGIS 1.9 on Windows

Follow instructions in with some modifications. Install Bison and Flex into simple directory like under c:/osgeo4w. Add WITH_INTERNAL_SPATIALITE checkbox. Fix setupapi.lib library directory by finding the file. Download Postgresql x86 release and point POSTGRESQL_INCLUDE_DIR and POSTGRESQL_LIBRARY to the directories under PostgreSQL? x86 install. Download GDAL 1.10 or later from MSVC2008 Win32 build and copy files into osgeo4w lib and include folders. Try building and get frustrated on failures...

-- TeemuKumpumaeki - 10 Jul 2013
Print version |  PDF  | History: r4 < r3 < r2 < r1 | 
Topic revision: r4 - 01 Aug 2013 - 13:25:03 - TeemuKumpumaeki


Copyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TUTWiki? Send feedback