4. Creating a Spatial Database

4.1. PgAdmin

PostgreSQL has a number of administrative front-ends. The primary one is psql a command-line tool for entering SQL queries. Another popular PostgreSQL front-end is the free and open source graphical tool pgAdmin. All queries done in pgAdmin can also be done on the command line with psql.

  1. Find pgAdmin and start it up.

    _images/pgadmin_01.png

  2. If this is the first time you have run pgAdmin, you should have a server entry for PostGIS (localhost:5432) already configured in pgAdmin. Double click the entry, and enter anything you like at the password prompt to connect to the database.

    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.

4.2. Creating a Database

  1. 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.

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

    _images/pgadmin_03.png

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

    Name

    nyc

    Owner

    postgres
    _images/pgadmin_02.png

  4. Select the new nyc database and open it up to display the tree of objects. You’ll see the public schema.

    _images/pgadmin_04.png

  5. Click on the SQL query button indicated below (or go to Tools > Query Tool).

    _images/pgadmin_05.png

  6. Enter the following query into the query text field to load the PostGIS spatial extension:

    CREATE EXTENSION postgis;

  7. Click the Play button in the toolbar (or press F5) to “Execute the query.”

  8. Now confirm that PostGIS is installed by running a PostGIS function:

    SELECT postgis_full_version();

You have successfully created a PostGIS spatial database!!

4.3. Function List

PostGIS_Full_Version: Reports full PostGIS version and build configuration info.