Introduction to the OpenGeo Suite

Creating a PostGIS Database

Now with some spatial database concepts under our belts, we’ll use the GUI client pgAdmin to connect to the PostGIS server and create a spatially-enabled database.


PostgreSQL has a number of administrative front-ends. psql is a command-line tool for managing PostgreSQL databases and entering SQL queries. The desktop utility pgAdmin is a tool that provides similar functionality with a graphical front-end. In this workshop, we’re going to use pgAdmin, but all of the queries that can be done in pgAdmin can also be done on the command line with psql.

Start PostGIS and connect to pgAdmin

  1. Make sure that OpenGeo Suite is running, specifically the PostGIS service.

  2. Launch pgAdmin from the Start Menu (OpenGeo Suite ‣ pgAdmin).

  3. You should have a server entry for PostGIS (localhost:5432) already configured in pgAdmin. Double-click the entry, and when prompted for a password, leave it blank. This will connect to the local PostGIS instance.


    Connecting to PostGIS


For this workshop, the PostGIS database has been installed with unrestricted access for local users (users connecting from the same machine on which the database is running). That means that it will accept any password you provide. If you need to connect from a remote computer, the password for the postgres user has been set to postgres. Obviously, a production instance would be set up with more security in mind.


If you have a previous installation of pgAdmin on your computer, you may not have an entry for the local PostGIS instance, so you will need to create a new connection. Go to File ‣ Add Server and register a new server at localhost and port 5432. You will then be able to connect to the PostGIS instance that is bundled with OpenGeo Suite.

Create a database

Next we will create a database that will be used to hold spatial data.

  1. Click/open the Databases tree and have a look at the available databases.

  2. Right-click the Databases item and select New Database.


    Creating a new database

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

    Properties tab:





    Definition tab:




    New database parameters (Properties tab)


    New database parameters (Definition tab)

  4. Click the new SuiteWorkshop database and open it up to display the tree of objects. You’ll see in the Schemas a public schema, but no tables or functions.

The database has been created, but it has not been spatially enabled yet. That is the next step.

  1. Click the SuiteWorkshop database to select it.

  2. Click the SQL query button in the toolbar.


    SQL query button

  3. The SQL query window will open. In the SQL Editor tab at the top of the window, type the following:

  4. Execute the query by pressing F5, clicking the “Play” button, or by navigating to Query ‣ Execute.


    Spatially enabling a PostgreSQL database

  5. Now examine the database tree again. You’ll see a PostGIS-specific table called spatial_ref_sys. In the Views node, you’ll see four different views, one of which, geometry_columns, we’ll be working with as part of this workshop.


    New database tables in pgAdmin

Continue Reading

Previous: Spatial Databases

Next: PostGIS metatables and views

This Page

About Boundless

Boundless provides commercial open source software for internet mapping and geospatial application development. We are dedicated to the growth and support of open source software.


This work is licensed under a Creative Non Commercial-Commons Attribution-Share Alike 3.0 United States License. Feel free to use this material, but we ask that you please retain the Boundless branding, logos and style.

Creative Commons License