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. pgAdmin also includes a geometry viewer you can use to spatial view PostGIS queries.

Find pgAdmin and start it up.
If this is the first time you have run pgAdmin, you probably don’t have any servers configured. Right click the Servers item in the Browser panel.

We’ll name our server PostGIS. In the Connection tab, enter the Host name/address. If you’re working with a local PostgreSQL install, you’ll be able to use localhost. If you’re using a cloud service, you should be able to retrieve the host name from your account.

Leave Port set at 5432, and both Maintenance database and Username as postgres. The Password should be what you specified with a local install or with your cloud service.

4.2. Creating a Database¶

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.
Right-click on the Databases item and select New Database.
Fill in the Create Database form as shown below and click OK.

Name

nyc

Owner

postgres
Select the new nyc database and open it up to display the tree of objects. You’ll see the public schema.
Click on the SQL query button indicated below (or go to Tools > Query Tool).
Enter the following query into the query text field to load the PostGIS spatial extension:
```
CREATE EXTENSION postgis;
```
Click the Play button in the toolbar (or press F5) to “Execute the query.”
Now confirm that PostGIS is installed by running a PostGIS function:
```
SELECT postgis_full_version();
```