PostGIS on Ubuntu

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

-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