Install PostGIS
Before we begin, you should uninstall your existing postgis packages:
sudo dpkg --purge postgis postgresql-9.1-postgis
Then add a new repository and install PostGIS from there (based on this post):
sudo apt-add-repository ppa:sharpie/for-science # To get GEOS 3.3.2 sudo apt-add-repository ppa:sharpie/postgis-nightly sudo apt-get update sudo apt-get install postgresql-9.1-postgis
Next we should create a new template database (optional but recommended).
createdb -E UTF8 template_postgis2 createlang -d template_postgis2 plpgsql psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis2'" psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.0/spatial_ref_sys.sql psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.0/rtpostgis.sql psql -d template_postgis2 -c "GRANT ALL ON geometry_columns TO PUBLIC;" psql -d template_postgis2 -c "GRANT ALL ON geography_columns TO PUBLIC;" psql -d template_postgis2 -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;" createdb training -T template_postgis2
Ok now we can load a raster (see sample data download below):
raster2pgsql -s 4326 srtm_4326.tif | psql training shp2pgsql -s 4326 -d -g geom -I places.shp places| psql training
Good – now our spatial database is ready to use – and has raster support! Here is a nice example of what you can do. The query looks up the altitude from the SRTM raster for each place listed using the ST_Value function:
psql -U postgres training -c "select ST_Value(rast, geom, true) from places, srtm_4326;"
It should produce something like this:
st_value
----------
139
262
103
131
161
137
143
73
138
366
368
(11 rows)
credit: Tim Sutton
refs:
Installing PostGIS 2.0 on ubuntu (used in this wiki)
http://postgis.refractions.net/documentation/manual-1.5/ch02.html#id417461 (alternative reference)