Instalation and basic usage of PostGIS

Hey!

I'm gonna be writing about how can you install and do some basic usage on PostGIS in Linux. If you've stumbled here, you probably already know what PostGIS is, but if you don't, is an extension of PostgreSQL specifically built to deal with GIS data (Geographical Information Systems). It is specially useful for storing points that represent shapes (each point having latitude, longitude) and you can perform nifty calculations like obtaining the shape surface, distance between shapes or point, etc.

Yeah, the logo is not the best part about PostGIS

I did this a long time ago, and had to do it again and just realized that it can be somewhat difficult, so I decided to write a post to help other and my own memory (whenever I need this again :D). Let's get to it then!

Installation

Since PostGIS is an extension of PostgreSQL, we obviously need to have it installed first. In my bundled Ubuntu repos I had version 1.5, which is fine, but it complicates a bit the PostGIS setup afterwards than using the more recent 2 version. Thus, you have to options here: to use version 1.5 and have a bit more complicated setup and outdated version or do some repository update and go for version 2 of PostGIS. I'll explain both, since I've tried both and seem to work.

Postgis 1.5

This is the most straight forward approach, since all you need is in the repositories of any modern Ubuntu distro. Thus, first we need to install postgreSQL:

sudo apt-get update
sudo apt-get install postgresql postgresql-client postgresql-contrib pgadmin3

Once finished, we can proceed to install postgis. At the time of writing this post, it was version 1.5 in the repos and 9.1 for postgreSQL, if by the time you try this it has changed to version 2, you can skip the rest and go to the Postgis 2.0 version.

sudo apt-get install postgresql-9.1-postgis

First thing you should do is create a postgis template that you can use every time you need to create a PostgreSQL database. This is not mandatory, but recommended, thus you don't have to follow this steps to give postGIS support to each db.

sudo su postgres
createdb postgis_template
psql -d postgis_template -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -d postgis_template -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql

Obviously, you can specify the template name you want here. Normally the paths to those sql scripts should be as specified above, but maybe you'll have to look for them in your filesystem. Now to check if the the db template has correctly installed support for postGIS, run this:

psql -d postgis_template -c "SELECT postgis_full_version();"

If you get this output (with different version probably) everything is ready to go, we can create our first postGIS enabled database.

                                         postgis_full_version                                          
-------------------------------------------------------------------------------------------------------
 POSTGIS="1.5.3" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.8.0" USE_STATS
(1 row)

Postgis 2.x

For versions of postGIS >= 2.0, the creation of the template is much simpler, but first you need to install a few packages that you usually won't find in default repositories. First, install postgreSQL as before, but a newer version (9.3). Remember we are doing this on Ubuntu Saucy, but you can change it to your distro name instead:

echo "deb http://apt.postgresql.org/pub/repos/apt/ saucy-pgdg main" | sudo tee /etc/apt/sources.list.d/postgis.list
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.3 postgresql-9.3-postgis-2.1 postgresql-client-9.3

Now the process to create a postGIS template is much simpler. We'll only need to login with postgres user, create the template database and use the EXTENSION procedure.

sudo su postgres
createdb postgis_template
psql
\c postgis_template
 CREATE EXTENSION postgis;
 CREATE EXTENSION postgis_topology;

You should be good to go now. Let's create our first postGIS enabled database.

Create your first database

Before creating our first db from the template we created before, it is best to create a user, instead of using root user "postgres". Thus, we create a group, a user and assign the user to the group.

sudo su postgres
psql
CREATE ROLE gis_group NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;
CREATE ROLE gis LOGIN PASSWORD 'supersecurepassword' NOINHERIT;
GRANT gis_group TO gis;

Now change ownership of the postgis template to the gis user we just created.

\c postgis_template
ALTER TABLE geometry_columns OWNER TO gis;
ALTER TABLE spatial_ref_sys OWNER TO gis;

It is also a good idea to create a specific schema for this new user:

CREATE SCHEMA gis_schema AUTHORIZATION gis;

Now we're ready to create our first database:

createdb -T postgis_template -O gis test_gisdb

If you have troubles creating the database with the new user, just change peer to md5 in /etc/postgre/9.x/main/pg_hba.conf, then you should be able to execute postgre commands with other users besides postgres.

Testing postGIS

Let's create a simple table that contains only 2 fields (id, name) and we'll add a column that holds the location, which should be a shape with any number of points. Each point is a {latitude, longitude} pair. Here are the commands to do so:

psql -d test_gisdb -U gis
CREATE TABLE test_table (id SERIAL NOT NULL PRIMARY KEY, name VARCHAR(100));
# Add a geometry column to existing table
SELECT AddGeometryColumn('public','test_table','location',4269,'POLYGON',2);

We're basically telling postGIS to create a column named location in database test_table that will be a geometry column (a polygon) and that will hold 2 values (latitude, longitude) for each polygon point.

Now we can insert some data in this new table:

INSERT INTO test_table (name, location) 
VALUES ('loremipsum', ST_GeomFromText('POLYGON((52.62715 1.77340, 52.66197 1.77378, 52.66172 1.80750, 52.62692 1.80711, 52.62715 1.77340))',4269));

In order to create a polygon, you have to use the postGIS function ST_GeomFromText, which requires a polygon as parameter. In this case the polygon is made of 4 points, even tough 5 appear. Reason for this is that you need to "close" the polygon, thus, first and last point must be te same.

Now when querying that databse, you need to add some special clauses too, supposing you want to obtain the location column as a POLYGON too. Here is a SELECT query that will get all the rows from that table:

SELECT id, name, ST_AsText(location) AS polygon, opening FROM test_table;

And that should be it, you should be up & running to do cool stuff with postGIS! Enjoy!