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.

Note

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.

    ../_images/pgadmin_connect.png

    Connecting to PostGIS

Note

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.

Note

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.

    ../_images/pgadmin_newdb.png

    Creating a new database

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

    Properties tab:

    Name

    SuiteWorkshop

    Owner

    postgres

    Definition tab:

    Encoding

    UTF8

    ../_images/pgadmin_newdbvalues1.png

    New database parameters (Properties tab)

    ../_images/pgadmin_newdbvalues2.png

    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.

    ../_images/pgadmin_querybutton.png

    SQL query button

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

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

    ../_images/pgadmin_createextension.png

    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.

    ../_images/pgadmin_dbobjects.png

    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.

License

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