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

PostGIS and Templates
There is an easier way to create additional PostGIS-enabled
databases. When PostgreSQL creates a database, it does it by
copying an existing database. Usually this is the standard sys-
tem database template1 . Anything in the template database
ends up in your newly created database. We can use this
capability to create a PostGIS template database that can be
used when creating a new PostGIS-enabled database.
To create a template, simply create an empty database from
the standard template using createdb -E UTF8 postgis_template
from the command line. Then follow the example in this chap-
ter to load the lwpostgis.sql and spatial_ref_sys.sql scripts. Once
you have the template, use createdb -E UTF8 -T postgis_template
myNewDb to create a new PostGIS-enabled database.
If you installed PostgreSQL on Windows with the PostGIS option,
it should have created a postgis_template database for you. In
this case, you are ready to start creating your own PostGIS-
enabled databases.
Now that we have a database set up and properly configured, the next
step is to load the PostGIS extension into our database. The commands
to do this are provided with PostGIS in the lwpostgis.sql file. We simply
execute this SQL in our newly created database. There are a number
of ways to do this (for example, from a database client tool such as
PgAdminIII); however, we chose to use the PostgreSQL interactive ter-
minal psql . In psql , we use the \i command to read the file from disk
and execute the SQL statements. In our example, we had changed to
the directory containing lwpostgis.sql . If we hadn't, the full path to lwpost-
gis.sql would be required. This creates the types and functions. At this
point we have a PostGIS-enabled database, but we aren't done yet.
The final step is to create the spatial references table that contains
more than 2,600 coordinate systems. To do this, we executed the spa-
tial_ref_sys.sql file, also provided with PostGIS. Our database is now ready
to use for PostGIS data. Using the \d command in psql gives us a list
of the tables in our new database. In addition to the spatial_ref_sys table,
you'll notice the geometry_columns table. Let's look at it in a bit more
detail.