Posted by & filed under Postgres & PostGIS, QGIS.

PostGIS 2.0 is out and the awesomness continues! You can install PostGIS 2.0 on Ubuntu using packages which is exactly what I am going to show you here. Read on for details on how to get up and running and do your first simple raster analysis!

Note: You should make good backups first!

 

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:

 select ST_Value(rast, geom, true) from places, srtm_4326;

It should produce something like this:

Doing a 'point on raster' query on our raster in QGIS

 

Further reading: A really good place to start is the Boston GIS cheatsheets – I am really looking forward to exploring all the new features that are available in PostGIS 2.0, thanks to all those involved in building it!

Sample data for the example listed

 

  • Aquila

    I believe this line

    psql -d postgres -c “UPDATE pg_database SET datistemplate=’true’ WHERE datname=’template_postgis2′

    is missing a “; at the end

    • http://linfiniti.com Tim Sutton

      Good catch thanks! I fixed it in the article above.

      Regards

      Tim

  • Pingback: Installing PostGIS 2.0 on ubuntu « bloco de notas

  • darkblue_b

    Hi Tim- the great news of PostGIS 2.0 is overshadowed here by the apparent collision of package names … How did this happen ? both PostGIS 1.5 and PostGIS 2.0 are called postgresql-9.1-postgis ? really ? There is no reason why both cant be installed and available.. they are designed for that.. loading PostGIS is “per-database” and each database is quite seperate.. this seems like a mess in the making..

    • http://linfiniti.com Tim Sutton

      Hi

      Yeah I would also like to be able to run both 1.5 and 2.0 at the same time. Hopefully the package maintainers will sort something out for us at some point. Also note that that I had to drop and reload all my databases (with PostGIS 2.0 templates installed) in order for QGIS to be able to access by databases. Anyway, I’m happy to do this for now so that I can test out all the new goodies.

      Regards

      Tim

  • Sharpie

    Thanks for the reference Tim! I think it is important to stress that the builds coming out of my `postgis-nightly` repository are not 2.0 stable builds—they are nightly snapshots of the SVN trunk. That means that bugs and instability may creep in due to further development and there may be problems reporting bugs as the behavior could be different than what a user of 2.0 stable will see.

    Maybe I will get around to packaging 2.0, but it would be great to see a more official repo like DebianGIS or UbuntuGIS get there first.

    > great news of PostGIS 2.0 is overshadowed here by the apparent collision of package names … How did this happen ? both PostGIS 1.5 and PostGIS 2.0 are called postgresql-9.1-postgis ? really ?

    @darkblue_b

    These are by no means “official” PostGIS packages. The nightly repository was created over the course of a weekend because I wanted to play with CartoDB and got tired of re-installing PostGIS 2.0-dev from source each time I wiped and re-built a virtual machine. This means several corners were cut as the only goal was to avoid a source compile.

    I would love to see an official package from UbuntuGIS, but since nothing existed at the time I stepped in and slapped something together with spit and duct tape. My repos on launchpad can be easily forked if you feel like improving the build. Many details of the packaging process can be found in the mailing list thread that Tim linked in his post.

  • afdal_fs

    hey sir, I have problem with my database,
    i use pgadmin3, after i create new database, i can’t see postgis shp file and dbf loader 1.5, please help me

    • http://linfiniti.com Tim Sutton

      Please use the help resources at http://qgis.org/en/community.html to get help – though I would really suggest that you make a more detailed and informative description of your problem as you dont really provide enough information for someone to help you.

      Regards

      Tim