Thinking in GIS

With this post I will show how to install PostGIS 1.2.1 on Postgres 8.2.5 in Ubuntu 7.10 (but this procedure should work also for previous PostGIS/Postgres/Ubuntu versions) from repositories.
I will also show you how to load and secure GIS data and how to access them with some cool OS GIS Client (QGIS, UDig and gvSIG).
If you use this instructions together with my previous post, you will have a fully functional GIS Server Open Source Ubuntu workstation!

-1- Install Postgres

If you haven’t Postgres, you need to install it (PostGIS runs on top of it). Open an Ubuntu terminal, and type:

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

Postgres (8.2.5) will be now on your Ubuntu box.

-2- Install PostGIS

Still from the terminal, type:

sudo apt-get install postgresql-8.2-postgis

PostGIS (1.2.1) will now be installed, to be precise this 2 packages are installed:

postgresql-8.2-postgis
postgis

under file:///usr/share/doc/postgis/postgis.html you will find the PostGIS Manual, for more help about installation and configuration.

-3- Create a PostGIS database template

Creating a PostGIS database template is the way to go if you want to make it easy the creations of many GIS database on the same server. Without creating this template, you would need to repeat this steps every time you need to create a PostGIS database.

sudo su postgres
createdb postgistemplate
createlang plpgsql postgistemplate
psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/lwpostgis.sql
psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/spatial_ref_sys.sql

The template is now ready (a lot of functions and two tables - geometry_columns and spatial_ref_sys - were created in it).
Now we can test of postgistemplate we just created:

$ 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)

-4- Create group role and user

Generally the best way is to create the GIS data in the PostGIS database by using a different role and user than using the postgres one, that should be used only for administrative tasks.
Typically I use to create a GIS role and user for managing data in the PostGIS database. You can even create more GIS users with different rights (SELECT, INSERT, UPDATE, DELETE on the different GIS feature classes), to generate a more safe environment. This depends on the configuration of your GIS scenario.

Connect to postgres (with postgres user): psql and enter in the command prompt:

type this to create the group role, that here i name gisgroup (choose less permissions if needed for security reasons):

CREATE ROLE gisgroup NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

type this to create the login role, here named gis (feel free to change it):

CREATE ROLE gis LOGIN PASSWORD 'mypassword' NOINHERIT;

assign the gis login role to the gisgroup group role:

GRANT gisgroup TO gis;

-5- Assign permissions

We need to assign permissions for the postgistemplate tables (geometry_columns and spatial_ref_sys will be owned from the gis user):

exit from the previous connection (type \q), and connect to the postgistemplate database as the postgres user:

psql -d postgistemplate

assign the permissions:

ALTER TABLE geometry_columns OWNER TO gis;
ALTER TABLE spatial_ref_sys OWNER TO gis;

Create a schema for your gis data (we shouldn’t create the gis data in the public schema):

CREATE SCHEMA gis_schema AUTHORIZATION gis;

exit from the connection (\q)

-6- Database creation

Now we are ready to create the database (or more databases) where to load the data (named gisdb), using the createdb command, from the postgistemplate we just have created:

$ createdb -T postgistemplate -O gis gisdb

-7- Data loading

Download this test data: there are 4 shapefiles that we will load in the new PostGIS database we have created.
We can import shapefiles in PostGis with the shp2pgsql command.
First we will create the sql files with this command, and then we will run this files with Postgres to import the data in PostGIS.

To create the sql files (if you want to avoid this step, the zip file already contains this *.sql files we are generating):

$   shp2pgsql -I -s 32633 POI.shp gis_schema.poi > poi.sql
Shapefile type: Point
Postgis type: POINT[2]
$   shp2pgsql -I -s 32633 vestizioni.shp gis_schema.vestizioni > vestizioni.sql
Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
$  shp2pgsql -I -s 32633 compfun.shp gis_schema.compfun > compfun.sql
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
$  shp2pgsql -I -s 32633 zone.shp gis_schema.zone > zone.sql
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]

Note that we used 2 options of the shp2pgsql:
-I will also create a GiST index on the geometry column
-s will give to PostGIS the information of the srid of the data (srid=32633 is for gis data with a spatial reference WGS84, UTM 33 N)

Now it is time to execute the *.sql scripts with the gis user:

$ psql -d gisdb -h localhost -U gis -f poi.sql 
 
BEGIN
psql:poi.sql:4: NOTICE:  CREATE TABLE will create implicit sequence "poi_gid_seq" for serial column "poi.gid"
psql:poi.sql:4: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "poi_pkey" for table "poi"
CREATE TABLE
                  addgeometrycolumn                   
------------------------------------------------------
 gis_schema.poi.the_geom SRID:32633 TYPE:POINT DIMS:2
 
(1 row)
 
CREATE INDEX
COMMIT

Do the same with the other 3 sqls generated from the previous step:

$ psql -d gisdb -h localhost -U gis -f compfun.sql 
$ psql -d gisdb -h localhost -U gis -f vestizioni.sql 
$ psql -d gisdb -h localhost -U gis -f zone.sql

-8- Install QGIS, uDig or gvSIG to test the data

We will now install on Ubuntu some cool OS GIS Desktop application you may use to view/edit the PostGIS data we just have loaded.

QGIS

QGIS is a light GIS application (my favorite) that will allow you to view/edit gis data in the following vectorial formats:

file: shape, GML, MapInfo, catd.ddf
database: PostGIS
web: WMS

sudo apt-get install qgis

(will install qGis 0.8.0)

Run QGis (Application > Education Quantum GIS) and add PostGis data (Layer > Add a PostGis layer) from your new gis database!

UDig

UDig is a nice GIS client that I use to test, view and edit many vectorial data formats:

file: shape, GML
database: PostGIS, ArcSde, Oracle Spatial
web: WFS, WMS

Download the udig zip file (current is 1.1-RC13) and unzip it, then run the udig executable:

./udig

udig will start
File > New Map
then go to Layer > Add > PostGIS.

gvSIG

gvSIG is a very interesting GIS application that will let you view and edit a lot of vectorial data formats like:

file: shape, DGN, DWG, DXF, GML
database: PostGIS, MySQL Spatial, HSQLDB, ArcSde, Oracle Spatial
web: WFS, WMS, WCS

Download the bin installation file, make it executable and run it. The gvSIG installation wizard will start letting you to install gvSIG.
Then start gvSIG, create a new view, and add PostGIS data in it by pressing the “Add Layer” button (the oldest of you will realize that it is in the same fashion like ArcView 2-3.x!). From the “Add Layer” dialog, choose the “GeoBD” tab and connect to PostGIS.