OpenGeo

Introduction to PostGIS

Previous topic

Section 2: Installation of PostgreSQL

Next topic

Section 4: Loading spatial data

Section 3: Creating a Spatial DatabaseΒΆ

Now that the database server is installed and running, we will create a spatial database – a standard database instance with the extra PostGIS types, functions, and indexes added.

PostgreSQL has the notion of a “template database” that can be used to initialize a new database – the new database automatically gets a copy of everything from the template. When you installed PostGIS, a spatially enabled database called template_postgis was created. If we use template_postgis as a template when creating our new database, the new database will be spatially enabled.

The PostGIS installer has created a Start Menu group with links to the PostGIS documentation. If you ever get lost or need to check the description or syntax of a spatial function, this is the fastest route to the knowledge you seek.

_images/install-23.png
  1. PostgreSQL has also created a program group. Navigate to it and select the pgAdmin III item.

    _images/install-24.png
  2. The pgAdmin splash screen will display for a few seconds while the application loads.

    _images/install-25.png
  3. After pgAdmin loads, double-click on the default database named PostgreSQL 8.4 (localhost:5432). This is the instance you have just installed. pgAdmin will ask you to enter the password you created for the user “postgres” when installing PostgreSQL. Enter the password and check the Store password box to avoid being prompted for the password again.

    _images/install-25.2.png
  4. You may see a warning about saving passwords. For the purposes of this workshop, we will not heed its warning. Check the Do not show this hint again box.

    _images/install-26.2.png _images/install-26.png
  5. Open the Databases tree item and have a look at the available databases. The postgres database is the user database for the default postgres user and is not too interesting to us. The template_postgis database is what we are going to use to create spatial databases.

  6. Right-click on the Databases item and select New Database.

    _images/install-27.png

    Note

    If you receive an error indicating that the source database (template_postgis) is being accessed by other users, this is likely because you still have it selected. Right-click on the PostgreSQL 8.4 item and select Disconnect. You can then double-click the same item to reconnect and try again.

  7. Fill in the New Database form as shown below and click OK.

    Name

    workshop

    Owner

    postgres

    Encoding

    UTF8

    Template

    template_postgis

    _images/install-28.png
  8. Select the new workshop database and click on the SQL query button indicated below (or go to Tools > Query Tool).

    _images/install-28.1.png
  9. Enter the following query into the query text field:

    SELECT postgis_full_version();
    
  10. Click the Play button in the toolbar (or press F5) to “Execute the query”. The query will return the following string, confirming that PostGIS is properly enabled in the database.

    _images/install-29.png