Configure PostGIS in PostgreSQL

PostgreSQL, often simply called Postgres for short, is a popular open source DBMS package.  This topic is the fourth step in a five topic sequence showing a complete installation of PostgreSQL from the very beginning, including the PostGIS extension required for GIS use.    Please see the Install PostgreSQL topic for basic info on PostgreSQL.

 

Disclaimer: These topics were accurate when written, using the versions of installation software indicated.  Third party packages can and do change, so this topic may be out-of-date.  It is provided as an example of how such installations can be approached.

 

Installing PostgreSQL and readying it for use in typical installations involves five steps:

 

Install PostgreSQL

 

Open a Firewall Port for PostgreSQL

 

Enable Network Access to PostgreSQL

 

Configure PostGIS in PostgreSQL

 

Connect to PostgreSQL

 

This is the fourth topic in the above sequence of steps.

 

So far, we have installed PostgreSQL on our target machine and we have done a raw installation of PostGIS, but we have not yet enabled use of PostGIS within PostgreSQL.   We must enable PostGIS use within PostgreSQL so our databases will be spatially-enabled and thus useful for GIS work.

 

Because we have already enabled use of PostgreSQL through a network, we could do this topic either on the same machine that hosts PostgreSQL (if we have a Manifold license on that machine) or we could launch Manifold on some other computer and connect to the PostgreSQL machine through a network.

 

This topic is illustrated assuming we have Manifold installed on the machine on which we have just installed PostgreSQL.  That allows us to launch Manifold on that machine and to connect to PostgreSQL using localhost, that is, not connect through a network.    We are using a 64-bit Windows 10 system to which we have connected via Remote Desktop (RDP).  This entire topic was conducted on that machine via RDP.   

Install PostgreSQL Client .dll

Every machine running Manifold which will be used to connect to PostgreSQL must have the PostgreSQL Client .dll installed.   The easiest way to do that at the present time is to visit the Product Downloads page on the Manifold website and, from the DLLs for Popular Open Source DBMS Packages section, to download the extension-dlls zip package for 64-bit and/or 32-bit operation, unzipping the appropriate package into the bin64 and/or bin folder of the Manifold installation folder.

 

It is possible that future Manifold builds may include native PostgreSQL client software as a built-in part of Manifold, eliminating the need to manually install the PostgreSQL client .dll.

Create a Database within PostgreSQL

We often use the word database casually, as a synonym for Database Management System (DBMS), or, in the case of file databases, in the sense of connecting to a particular database file, such as a particular GPKG database file or a particular Microsoft Access database .mdb file.  

 

In this topic we use the special PostgreSQL meaning of database, to mean a collection of tables, queries and other infrastructure grouped together under a name within a particular PostgreSQL DBMS installation.   When connecting to a PostgreSQL server, PostgreSQL allows us to specify the particular database within that server to which we want to connect.   If we do not specify a particular database, we will simply connect to the overall master database by default.  

 

We could, in theory, connect to and use the default master database without ever creating specific databases.  But that would be disorderly, somewhat like keeping hundreds of files in a Windows system in the root of the C: drive without ever organizing those hundreds of files into sensible folders.   It is much more orderly and efficient to create one or more databases within which we group tables and other components.  For example, within our PostgreSQL server we might create a database called gisdb within which we will keep all of our GIS data.

 

We also create a database so we can enable PostGIS functionality for that database.  So far, we have installed PostGIS but we have not yet enabled it.  PostGIS must be enabled for each database within which we would like to have the geospatial functionality that PostGIS provides.

 

There are a variety of ways, for example, using PostgreSQL administration tools, to create a database within a PostgreSQL server and to enable PostGIS for that database.    We can also use Manifold to issue the PostgreSQL commands which will create a database and then which will enable PostGIS for that database.  That is what we will do in this topic.

 

In this topic, we launch Manifold on the same system on which the PostgreSQL server has been installed.   The procedure below would be identical if we wanted to use Manifold from a remote machine, but instead of using localhost in the Create New Data Source dialog we would we would specify the IP address of the PostgreSQL server machine, as illustrated in the Connect to PostgreSQL topic.

 

Launch Manifold.

 

 

Choose File - Create - New Data Source.  

 

In the dropdown menu we then choose More... to launch the New Data Source dialog.

 

 

Enter a descriptive name, Postgres, and choose Database: postgresql as the type.  Click the browse [...] button to launch the Database Login dialog.

 

 

Enter localhost as the name of the server.  If we are connecting from a remote machine, we would enter the IP address of the server, for example, 192.168.2.44.  

 

Choose Use login and password and then enter postgres as the login.  Enter the password we provided for the postgres superuser login in the Install PostgreSQL topic.

 

It is a good idea to check for errors by pressing the Test button.  If that does not report a connection, we should fix any mistakes.   If we are sure there are no typos or other errors, we press OK.

Security

The best option for secure database connections is to use integrated security, where the Windows login through which a connection is made is mapped into user accounts within the DBMS, to which all the power and sophistication of roles and privileges can apply.  When using integrated security, no passwords or other credentials appear within connection strings.  Most DBMS packages support the use of integrated security.  To keep the presentation as simple as possible, except for the SQL Server example, integrated security is not used in DBMS topics.

 

Other options leverage the extensive security facilities already built into Window and databases.   If we do not use integrated security, but instead create a data source that uses login and password credentials we should take advantage of Windows security to protect those credentials.   It should go without saying that if we have access to a database in a particular role, we must know the password for the database user login that we will use.   We therefore should not fear using that password in plain text in the connection string if we have protected our credentials using the usual Windows security features.

 

For example, if we do not want other users on our computer to open the project and to use our access privileges to reach into the database, then we should Windows security features to ensure other users on the machine do not have permission to use the .map file.   If they have permissions to launch the .map file, it does not matter whether the password is stored in the connection string as plain text or in encrypted form: they still get the same level of access to the database when they connect through the data source using our credentials.  

 

Another way to enforce security is to save the connection to the database in a Windows DSN file as a user DSN.  User DSNs are specific to a particular user.   We then create a data source in Manifold by using File: dsn as the type in the New Data Source dialog, so that no connection strings are stored in the .map file.  Using a DSN has the advantage that we need only specify desired access to that one file to protect credentials, instead of to every .map project file we create.

 

 

Back in the New Data Source dialog we review the connection string just built for obvious errors, find none, and press Create Data Source.

 

A new data source called Postgres appears in our Project pane.  That data source is a connection to our PostgreSQL server.  We did not name a database so this data source is a connection to the default master database.   We can now use that connection to issue commands within that data source, which will take effect with the full power and privileges of our postgres login.

 

A risk element for security is the plain text use of passwords in connection strings.  We should not leave this project unattended where somebody else can right-click on the data source to see the Properties of it, which would reveal in plain text the password for postgres in the JSON string that gives the connection string.    We should only create such data sources for as long as we need to use the mighty powers of the superuser.  When our work is done, we should delete the data source from the project.  When other people might have access to our computer and our files, we should not save the project with the data source in it, since someone else might open that project to find the password string inside.

 

We can avoid such risks by using integrated security, where the Windows login through which a connection is made is mapped into user accounts within the DBMS, to which all the power and sophistication of roles and privileges can apply.  To keep the presentation as simple as possible, except for the SQL Server example, integrated security is not used in these case studies.

 

 

In the Project pane we right-click on the Postgres data source and choose New Command Window.

 

 

That launches a Command Window that operates in the context of the Postgres data source, that is, within the PostgreSQL server.  Whatever SQL and other commands we enter into that command window will be executed within the PostgreSQL server using the native SQL of PostgreSQL.

 

We enter the command

 

CREATE DATABASE gisdb;

 

And then we press the ! button to run the command.  

 

 

That creates a new database within the PostgreSQL server.  There is no visible change, since new databases do not appear as some sort of "database" component within PostgreSQL.

 

If we like, we can create other databases.  For example, if we want to create a database for use by students we could run another command:

 

CREATE DATABASE studentdb;

 

For this example, we will settle for creating only one database, the gisdb database we have just created.

Enable PostGIS for the gisdb Database

Although we have installed PostGIS, we must enable the PostGIS spatial extension for each database within our PostgreSQL database server.    We will use Manifold to connect to the gisdb database and to issue the command that will enable PostGIS for the gisdb database.

 

We will now create a second data source that is a connection to the new gisdb database within our PostgreSQL server.

 

Choose File - Create - New Data Source.  

 

In the dropdown menu we then choose More... to launch the New Data Source dialog.

 

 

Enter a descriptive name, Postgres gisdb, and choose Database: postgresql as the type.  Click the browse [...] button to launch the Database Login dialog.

 

 

Enter localhost as the name of the server.  If we are connecting from a remote machine, we would enter the IP address of the server, for example, 192.168.2.44.  

 

Choose Use login and password and then enter postgres as the login.  Enter the password we provided for the postgres superuser login in the Install PostgreSQL topic.

 

We enter gisdb as the database to which we will connect with this data source.

 

Press OK.

 

 

Back in the New Data Source dialog we review the connection string just built for obvious errors, find none, and press Create Data Source.

 

 

A new data source called Postgres gisdb appears in our Project pane.  That data source is a connection to the database called gisdb within our PostgreSQL server.  We now have two different data source connections to the same PostgreSQL server in our project.  One connects by default to the master, overall database for the server, while the other connects only to the gisdb database within the server.

 

We now will issue a command in the context of the gisdb database, to enable PostGIS features for that database.

 

 

In the Project pane we right-click on the Postgres gisdb data source and choose New Command Window.

 

 

That launches a Command Window that operates in the context of the Postgres gisdb data source, that is, within the gisdb database in the PostgreSQL server.  

 

In the illustration above, we have closed the tab for the previous command window that we earlier opened for the Postgres data source.   We now just show the command window for the Postgres gisdb data source.

 

We enter the command

 

CREATE EXTENSION postgis;

 

And then we press the ! button to run the command.  

 

 

That enables PostGIS functionality for the gisdb database.  There is no visible change to the listings within either of the PostgreSQL data sources.  To see the new geometry infrastructure added within the Postgres gisdb data source we can right-click on that data source and choose Refresh.

 

We have now created a new database, called gisdb, within the PostgreSQL server and we have enabled PostGIS functionality for that database.  We can now close the project.   In the next topic, we look at an example using the gisdb database we have created.

Next Steps

Continue this case study on installing PostgreSQL with the Connect to PostgreSQL topic.

 

See the preceding topic: Enable Network Access to PostgreSQL

 

Jump to the beginning of the case study: Install PostgreSQL

Notes

Deleting a database - If we make a typographical error when creating a database or if we simply want to delete a database we used for experimentation, we can open a command window on the Postgres data source and enter a command such as:

 

DROP DATABASE gisdb;

 

That will instantly delete the database and all it contains.  PostgreSQL is designed for experts, so when logging in with the superuser postgres login and issuing commands like that there are no confirmation dialogs: give a command to drop a database and PostgreSQL immediately and irreversibly deletes that database.  Handle with care, and never, ever give the superuser password to careless people.

 

See Also

Schema

 

DBMS Data Sources - Notes

 

Project Pane

 

File - Create - New Data Source

 

Command Window

 

Real and Virtual Components

 

Install MySQL

 

Install SQL Server

 

Install PostgreSQL

 

Open a Firewall Port for PostgreSQL

 

Enable Network Access to PostgreSQL

 

Connect to PostgreSQL

 

Install Oracle

 

Big List of Formats and Data Sources

 

Example: Switching between Manifold and Native Query Engines