Section 3: Creating a Spatial Database¶
The Dashboard and PgAdmin¶
The “Dashboard” is the central application to access all portions of the OpenGeo Suite.
When you first start the dashboard, it provides a reminder about the default password for accessing GeoServer.
The PostGIS database has been installed with unrestricted access for local users (users connecting from the same machine as 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.
For this workshop, we will be using the entries under the “PostGIS” section of the Dashboard almost exclusively.
First, we need to start up PostGIS. Click the green Start button at the top right corner of the Dashboard.
The first time the Suite starts, it initializes a data area and sets up template databases. This can take a couple minutes. Once the Suite has started, you can click the Manage option under the PostGIS component to start the pgAdmin utility.
If this is the first time you have run pgAdmin, you should have a server entry for PostGIS (localhost:54321) already configured in pgAdmin. Double click the entry, and enter anything you like at the password prompt to connect to the database.
If you have a previous installation of PgAdmin on your computer, you will not have an entry for (localhost:54321). You will need to create a new connection. Go to File > Add Server, and register a new server at localhost and port 54321 (note the non-standard port number) in order to connect to the PostGIS bundled with the OpenGeo Suite.
Creating a Database¶
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.
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.
Right-click on the Databases item and select New Database.
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 PostGIS (localhost:54321) item and select Disconnect. Double-click the same item to reconnect and try again.
Fill in the New Database form as shown below and click OK.
The template and encoding are specified on the “Definition” panel.
Select the new nyc database and open it up to display the tree of objects. You’ll see the public schema, and under that a couple of PostGIS-specific metadata views – geometry_columns and spatial_ref_sys.
Click on the SQL query button indicated below (or go to Tools > Query Tool).
Enter the following query into the query text field:
This is our first SQL query. postgis_full_version() is management function that returns version and build configuration.
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.
You have successfully created a PostGIS spatial database!!
Table Of Contents
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License. Feel free to use this material, but we ask that you please retain the OpenGeo branding, logos and style.