4. Creating a Spatial Database¶
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
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
Serversitem 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
postgresdatabase is the user database for the default postgres user and is not too interesting to us.
Right-click on the
Databasesitem and select
Fill in the
Create Databaseform as shown below and click OK.
Select the new
nycdatabase and open it up to display the tree of objects. You’ll see the
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:
You have successfully created a PostGIS spatial database!!
4.3. Function List¶
PostGIS_Full_Version: Reports full PostGIS version and build configuration info.