Database Reference
In-Depth Information
Organizing the database
Oneofthemostimportantthingstoconsiderwhencreatingandusingadatabaseis
how to organize the data. The layout of the database should be decided when you
firstestablishthedatabase.Thelayoutcanbedecidedorchangedatalaterdate,but
thisisalmostguaranteedtobeatedious,ifnotdifficult,task.Ifitisneverdecided,a
databasewillbecomedisorganizedovertimeandintroducesignificanthurdleswhen
upgrading components or running backups.
Bydefault,anewPostgreSQLdatabasehasonlyone
schema
,thatis,
public
.Most
users place all the data (their own and third-party modules such as PostGIS) in the
public
schema.Doingsomixesinformationofdifferentorigins.Aneasymethodto
separatetheinformationistouseschemas.Thisenablesusingoneschemaforour
data and a separate schema for everything else.
Getting ready
Inthisrecipe,wewillcreateadatabaseandinstallPostGISinitsownschema.Wewill
alsoloadsomegeometriesandrastersforfurtherusebyotherrecipesinthischapter.
The following are the two methods to create a PostGIS-enabled database:
• Using the
CREATE EXTENSION
statement
• Running the installation SQL scripts with a PostgreSQL client
The
CREATE EXTENSION
methodisavailableifyouarerunningPostgreSQL9.1or
a higher version and is the recommended method for installing PostGIS.
How to do it...
Carry out the following steps to create and organize a database:
1. Create a database named
chapter10
by executing the following command:
CREATE DATABASE chapter10;
2. Create a schema named
postgis
in the
chapter10
database, where we
will install PostGIS; execute the following command: