Install

You can install PostGIS as a package or from sources. Sometimes the version available from Linux distributions is a bit out of step with the PostgreSQL version that gets installed (i.e. Ubuntu currently installs PostgreSQL 8.4, but only has PostGIS 8.3)

From Sources

Choose which version to download

$ wget http://postgis.refractions.net/download/postgis-1.4.1.tar.gz
$ tar -xkzf postgis-1.4.1.tar.gz
$ cd postgis-1.4.1
$ ./configure
$ make
$ sudo make install

On my system (Ubuntu), the net result is that you get various files copied into /usr/share/postgresql/8.4/contrib:

$ ls /usr/share/postgresql/8.4/contrib
postgis.sql                   postgis_upgrade_13_to_14.sql  postgis_upgrade.sql  uninstall_postgis.sql
postgis_upgrade_12_to_14.sql  postgis_upgrade_14_minor.sql  spatial_ref_sys.sql

Create a PostGIS Template Database

If you create a specific template with PostGIS, and use it instead of template1 (the default) template, you will be able to create PostGIS-enabled databases more quickly.

$ sudo -u postgres createdb template_postgis -E UTF-8

Set the template flag

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';

Make sure pl/pgsql is installed

$ sudo -u postgres createlang -d template_postgis plpgsql

Insert PostGIS functions and tables

$ sudo -u postgres psql template_postgis < /usr/share/postgresql/8.4/contrib/postgis.sql
$ sudo -u postgres psql template_postgis < /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql

Correct Permissions

The PostGIS tables need to be writeable by the owner of the database

$ sudo -u postgres psql template_postgis
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;

Create a PostGIS-Enabled Database

$ sudo -u postgres createuser [USER NAME]
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
$ sudo -u postgres createdb [DATABASE NAME] -O [USER NAME] -T template_postgis

Check it works

$ psql -U [USER NAME] [DATABASE NAME]

First let's create a 'locations' table:

CREATE TABLE locations (id serial, name character varying);
SELECT AddGeometryColumn('', 'locations', 'geom', 32661, 'POINT', 2);

Insert a record:

INSERT INTO "locations" ("name", "geom") VALUES(E'Tokyo International Airport', ST_GeomFromText('POINT(139.783333 35.55)', 32661)) RETURNING "id";
 id
----
  1
(1 row)

INSERT 0 1

Find all results in a given box:

SELECT * FROM "locations" WHERE ("locations"."geom" && SetSRID(E'BOX3D(130.0 30.0, 140.0 40.0)'::box3d, 32661));
 id |            name             |                        geom
----+-----------------------------+----------------------------------------------------
  1 | Tokyo International Airport | 0101000020957F0000151C5E10117961406666666666C64140

(1 row)