Nick’s Blog

PostGIS on Ubuntu

by Nick on Jun.12, 2009, under GIS

I’d been called by an old client and they were after someone with OpenLayers experience. Now I do have some experience with OpenLayers but I decided that brushing up on the skills would do no harm. I created an application that connects to a GeoServer WMS instance on my Ubuntu machine and even managed to get the GetFeatureInfo working with a little effort (because the site and the GeoServer are in different domains it was necessary to add a proxy to the site to forward requests from the client). But what I am missing is the ability to edit data. For this to work I am going to need to turn to the WFS-T capabilities of GeoServer and that means having an editable source of data. So I’ve decided to create a PostGIS database. The rest of this article is really just about that experience and it may be a little disjointed as I’m writing it as I go along.

Creating a PostGIS installation

Step 1 was to confirm that my Ubuntu server did come with Postgres installed. It did but there was no GUI interface so I wanted to add pgAdmin III. I found a useful article on the web by Paolo Corti that covers the very same subject and made a start. He had been using an earlier version but asside from changing the numbers it seems to have made no difference. His article is here Because I already had a Postgres installation I skipped the first step and went straight to the installation of PostGIS which runs on top of Postgres. Paolo recommends using the following command line. sudo apt-get install postgresql-8.3-postgis And I guess that would have worked but I am quite a fan of the Synaptic Package Manager because of my Windows background and decided to find the relevant package in the list of packages and intall them.  I also added the pgAdmin III and a Postgres database design tool in the same way. Paolo then recommends creating a GIS database template and that seems very sensible so I resorted to using the command line to execute the following commands. sudo su postgres createdb postgistemplate createlang plpgsql postgistemplate psql -d postgistemplate -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql psql -d postgistemplate -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql and finally Paolo recommends executing a command to confirm that everything is up and running. $ psql -d postgistemplate -c “SELECT postgis_full_version();” � postgis_full_version                             � ———————————————————————————- POSTGIS=”1.2.1″ GEOS=”2.2.3-CAPI-1.1.1″ PROJ=”Rel. 4.5.0, 22 Oct 2006″ USE_STATS (1 row) I believe my results were slightly different (different versions) but essentially it confirmed that everything was working. Perhaps I should mention at this point that every time I entered a command to the postgres command line it responded with cannot change directory to /home/nick or something like that. I ignored these error messages and plodded on. However I really do now know now if I have some kind of corrupted system or if everything has worked OK but if you are following these instructions and get the same error messages then please let me know if you find out the cause.

What is the password?

I decided to take a look at the pgAdmin III interface to see what it made off my new installation. It required me to make a new connection to the database and part of that involved entering the user name and password. Trouble is I do not remember ever creating a user name and password during the install although I know that the default user is postgres so I tried a few logical guesses and when none of those worked I resorted to Googling “postgres ubuntu default password” and luckily came across another  helpful article this time by Antonio Cangiano at this url In the article he mentions that people may want to know what the default password is and instead of actually telling us he goes one better and shows how to change it. sudo su - passwd postgres su postgres psql template1 ALTER USER postgres WITH ENCRYPTED PASSWORD ‘mypassword’; Again I was getting error messages regarding not being able to change to a specific directory, in this case it was /root, and again I ignored them. Once I’d done this I went back to the pgAdmin III tool and entered the new password that I’d created. The tool showed me what I needed and that was that the local server had two databases, one was the default postgres database and the other was the template that was created using Paolo’s instructions.

Creating an administrative group

Now back to following Paolo’s instructions again. He goes on to create some administrative groups so that we are not connecting using the default administrative account. This is really quite important as indirectly we are connecting from the Internet and any weakness in GeoServer could render the database vulnerable. Step 1 is creating a gisgroup role that the gis users can belong to. I chose to use pgAdmin III to do this form me and the script it created was the following. CREATE ROLE gisgroup NOINHERIT CREATEDB VALID UNTIL ‘infinity’; Step 2 is creating a gis login account. Again I used pgAdmin III and it created the following script. CREATE ROLE gis LOGIN ENCRYPTED PASSWORD ‘md5……..’ NOINHERIT VALID UNTIL ‘infinity’; GRANT gisgroup TO gis;

Acessing the database from other machines

The next step was not strictly necessary but I wanted to be able to reach the database from other machines in the same network. By default Postgres is quite restrictive and only allows local access. To extend this you need to alter two files. Once again I’m getting frustrated that this means dropping to the command line because the logged in user does not have sufficient rights. pgAdmin III comes with an editor for the two files that should make life easy but sadly as soon as you click on Save it tells you that you do not have the rights to save to the file. But let’s get on with describing the changes. The first file to change is the postgresql.conf file found in /etc/postgresql/8.3/main/. Here we need to uncomment and edit the line that reads :- #listen_addresses = ‘localhost’ and change it to listen_addresses = ‘*’ Now this means that postgres will listen for incoming connections on all network adapters so if this is not what you need then you might want to be more specific and list the specific IP addresses you want to use. For me allowing any adapter was fine as I only have one. The second change is to the file pg_hba.conf in the same folder. Locate the following line and change it as shown. local all all md5 becomes local all all md5 Apparently this allows connections from any machine in the local network specified to connect so long as they are using an md5 encrypted password. Again this is probably not strictly necessary but it does no harm to be more secure to start with and only relax the rules if it becomes necessary. To ensure these changes came into effect I needed to restart the database with the following command. sudo /etc/init.d/postgresql-8.3 restart I was now able to connect using pgAdmin III running on the Vista machine.

More administrative stuff for the GIS database template

Now back to Paolo’s script for creating a gis database. The next step is to grant ownership of a couple of tables to the gis user and then create a schema for the data rather than using the default public schema. ALTER TABLE geometry_columns OWNER TO gis; ALTER TABLE spatial_ref_sys OWNER TO gis; CREATE SCHEMA gis_schema AUTHORIZATION gis;

Creating an instance

Now I’ve suggested that I used the scripts above to perform these actions but actually I wanted to use pgAdmin III from my Windows Vista machine. So now we have a template that could be used to create one or more GIS databases and that is the very next step. Again Paolo prefers to use a command line to do this but I was happy to trust the database creation to pgAdmin III. This is the sql that the application created. CREATE DATABASE gisdb WITH ENCODING=’UTF8′ OWNER=gis TEMPLATE=postgistemplate; But sadly that did not work. It complained that the source template postgistemplate was in use. I tried everything and eventually resorted to using the command line that Paolo suggests :- createdb -T postgistemplate -O gis gisdb But that too failed and with the same error. I closed down pgAdmin III and tried again and now it worked. So I guess that the template was being held open by pgAdmin III and that is not allowed.

Creating a table

OK so once I had a working database it was time to add some data. I decided to broadly speaking following some guidelines that I found here It seems that in PostGIS you are supposed to create the table without the geometry column and then invoke another script to add the geometry column. This seems OK if slightly long winded but it takes care of the meta data for you and some constraints too. Here is the script to create the table. I used pgAdmin III again to create this after the table was already created and you will see that there is no geometry column included in the create script. That is not because I edited it out, it is simply not included and presumably that is because the GIS stuff is a bolt on layer so you cannot include the geometry type in the create script. CREATE TABLE gis_schema.sketch ( created timestamp without time zone, remark character varying(100), identifier bigserial NOT NULL, CONSTRAINT pk PRIMARY KEY (identifier) ) WITHOUT OIDS; ALTER TABLE gis_schema.sketch OWNER TO gis; GRANT ALL ON TABLE gis_schema.sketch TO gis; GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE gis_schema.sketch TO gisgroup; Having created the table the next stage is to add the geometry column and to index it. select AddGeometryColumn(‘gis_schema’,'sketch’,'geometry’,900913,’GEOMETRY’,2); create index idx_sketch_geometry on gis_schema.sketch using GIST (geometry); So this creates a column called geometry on the table of type GEOMETRY using the coordinate system 900913 and supporting two dimensions. I connected with UDig and all was well until I noticed that UDig does not seem to support the 900913 coordinate system. So I’ve switched to 4326 which is supported. This might come back and bite me when I try to get the GeoServer to connect but hopefully it is smart enough to transform the coordinates between 900913 on the client and 4326 as it will be in the database. Having created the tabel I thought great and connected with uDig, a spatial viewer. I hesitate to call it a GIS tool because so far I’ve seen no great evidence that it supports attribute data in any way and that is really the heart of GIS, coupling spatial information with associated data. But uDig is free and seems to work quite well with WMS servers and I had seen that it supports postGIS. Creating the connection was straightforward and it allowed me to select the table I’d created. However it threw a bunch of errors when I tried to commit any changes to the database. I concluded that I’d probably got something wrong in the structure of the table. Perhaps adding columns that uDig does not support and decided instead to download the demo data from Paolo’s web site. I ran the SQL rather than bothering to import the data from the shape files, however it is great that there is a utility provided to allow shape files to be read into a form that can be used with PostGIS. I added the layers to my map and fairly quickly I could see the data. So I tried again to add a feature and again I was getting the same kinds of errors. I’ll have to continue digging to find out why this is not working but to be honest it was never the main focus of the project. I do not need to edit directly from uDig. What I need is to create a WFS-T server under my GeoServer installation and have that connect to the PostGIS installation and then connect to that from OpenLayers and possibly uDig. So this is where I’ll be focussing my efforts.

:, , , , ,

1 Comment for this entry

  • Balazs


    I stuck with the postgresql, I can’t access from other machine. Can you help me?

    I use local network, my router local ip is
    the “database machine” local ip adress is
    and my laptop local ip adresse is

    What should I write in the pg_hba.conf file?
    I use Ubuntu Karmik koala 8.3 Postgresql.


Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!