Creating a spatial database

This section describes the process of creating a new spatially enabled PostGIS database.

Note

This section uses the command line utility createdb and optionally the graphical utility pgAdmin. createdb is included with the Boundless Server PostgreSQL package. pgAdmin is provided as part of Boundless Desktop.

  1. Expand the Databases item in the Object browser to reveal the available databases.

  2. Right-click Databases and select New Database.

    ../../_images/createdb_newdb.png

    Creating a new database in pgAdmin

  3. Complete the New database form with the following information:

    • Name—<user-defined database name>
    • Owner—postgres
    ../../_images/createdb_newdbsettings.png

    New database settings

  4. Click OK.

  5. Either click Execute arbitrary SQL queries on the pgAdmin toolbar or click Tools ‣ Query tool to open the Query dialog box.

  6. Enter the following query into the SQL editor input box and click the Execute query button, or press F5, to run the query.

    CREATE EXTENSION postgis;
    
    ../../_images/createdb_postgisext.png

    Creating a new PostGIS database.

  7. Verify the database was created correctly by running the management function postgis_full_version() in the SQL editor. It should return version and build configuration information.

    SELECT postgis_full_version();
    
    ../../_images/createdb_postgisversion.png

    Verifying a new PostGIS database

    If the command runs successfully the PostGIS database is setup correctly and ready to use.

  8. Double-click the new database item in the Object browser to display the contents. Inside the public schema, you will see one PostGIS-specific metadata table, spatial_ref_sys (for further information, see the section on OpenGIS metadata).

    ../../_images/postgis_metatables.png

    Spatial metadata tables

    Warning

    If you don’t see this table, your database was not created correctly.

Creating a spatial database from the command line

You can also create a PostGIS database from the command line with the createdb and psql commands.

createdb -U postgres <DATABASENAME>
psql -U postgres -d <DATABASENAME> -c 'CREATE EXTENSION postgis'