Spatial Databases - Desktop GIS: Mapping the Planet with Open Source Tools - page 91

Creating a PostGIS-Enabled Database
Since PostGIS is an extension to PostgreSQL, you have to add it to a
database in order to use the geometry types and functions. Let's look
at a session that creates a new database and enables PostGIS:

$ createdb -E UTF8 desktop_data
CREATE DATABASE
$ createlang plpgsql desktop_data
$ psql desktop_data
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

desktop_data= # \i ./lwpostgis.sql

BEGIN
psql:./lwpostgis.sql:39: NOTICE: type "histogram2d" is not yet defined
DETAIL: Creating a shell type definition.
CREATE FUNCTION
...
CREATE FUNCTION
COMMIT
desktop_data= # \i spatial_ref_sys.sql

INSERT 0 1
...
INSERT 0 1
COMMIT
VACUUM
desktop_data= # \d
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | geometry_columns | table | gsherman
public | spatial_ref_sys | table | gsherman

(2 rows)

desktop_data= #

Let's breakdown what we did and explain the steps. First we created
a database using the createdb command, specifying an encoding type
of UTF8 (Unicode) using the -E switch. Using Unicode for the database
encoding provides us with the most flexible solution, especially when
storing non-ASCII data. Once the database is created, we add the Post-
greSQL procedural language (PL/pgSQL) to the database using the cre-
atelang command and the plpgsql keyword. PostGIS needs PL/pgSQL in
order to implement its spatial types and functions.