PostgreSQL,
often simply called Postgres for
short, is a popular, free, open source DBMS package. PostGIS
is an extension to PostgreSQL that adds spatial data capability. Manifold
documentation uses both PostgreSQL
and Postgres interchangeably,
with no distinction between them. PostgreSQL is a high quality,
genuinely enterprise-class DBMS that is both well known and well respected.
There are no limitations on PostgreSQL such as a limited number of users, limited database size, or limited number of cores, which may apply to the free, "express" versions of commercial DBMS packages
This topic is the first step in a five topic sequence showing a complete installation and then utilization of PostgreSQL from the very beginning, including the PostGIS extension required for GIS applications, using a simplified approach intended to facilitate experimentation by individuals. A full enterprise installation should be undertaken by experts, to insure all required security facilities are correctly configured. The examples these topics show, especially in the last topic in the sequence, will be useful for working with an existing PostgreSQL installation our organization may have.
This topic is the first step in a five topic sequence showing a complete installation of PostgreSQL from the very beginning, including the PostGIS extension required for GIS use.
There are numerous extensions available for PostgreSQL that add capabilities. A key extension used in GIS is the PostGIS extension for PostgreSQL, which adds geospatial data types and geospatial capabilities like those in Oracle or SQL Server spatial capabilities. When used in GIS, PostgreSQL should always be configured with PostGIS.
There is no requirement to install
PostgreSQL or any other DBMS for using Manifold. Manifold includes
as built-in capabilities everything you need to do Manifold. In
general, storing GIS data within Manifold .map project
files will be faster, and will deliver higher performance with greater
ease of use and more capabilities than storing GIS data within PostgreSQL
or some other DBMS.
So why install or use PostgreSQL? Manifold users often use PostgreSQL or any other DBMS for three reasons:
Access to data - Organizations often use enterprise-class DBMS packages to store data. Being able to reach into organizational data provides tremendous access to data that can be combined with geospatial data in Manifold or in other systems, or to simply get access to a seemingly infinite range of data of all kinds in tables. Topics showing how to connect to a given database teach us how to access that data so we can use it.
Capabilities not in Manifold - While Manifold generally provides far broader geospatial capabilities than any DBMS, big DBMS packages such as PostgreSQL have very many capabilities evolved over many years, including capabilities not found in Manifold. By running server-side queries we can take advantage of those capabilities.
Alternative data storage - Whatever storage infrastructure has been invented, there is a database that uses it. Connecting to different DBMS packages allows Manifold users to store their data in whatever data technology they like, such as cloud storage, massively distributed storage, and memory resident storage.
Interchange with other applications - There are many other applications which can interact with data stored in a PostgreSQL database. Using PostgreSQL as a common data store allows easy interchange of data between those applications.
Multiuser, simultaneous use of GIS data - Manifold .map files at the present time do not allow simultaneous access by multiple Manifold sessions. If a .map file is open in one Manifold session it cannot be opened in another. If we require simultaneous access to data by multiple Manifold sessions, such as might be run by different people on different machines, we can store the data in a DBMS, such as PostgreSQL, Oracle, MySQL, SQL Server, DB2, or other DBMS.
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
This is the first topic in the above sequence of steps.
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. We have downloaded the Interactive Installer by EnterpriseDB from the postgresql.org site at https://www.postgresql.org/download/windows/ - the file we have downloaded and have now launched is called postgresql-11.2-1-windows-x64.exe.
Click Next.
We accept the default installation directory. Click Next.
We have plenty of free disk space on this computer so it is easier to install everything than to worry we missed something. Click Next.
If we had a file location on an especially fast disk drive or on a disk with exceptionally large amounts of space, we might choose to use that. In this example, we simply accept the default data directory location. Click Next.
Choose a password for the postgres superuser login. Write down the password where it will not be lost. Click Next.
5432 is the default port on which PostgreSQL listens. Accepting the default is easier for beginners, since examples and teaching materials often assume the default is being used. Click Next.
Since this documentation is written in English, we accept the default locale. Click Next.
It is a good idea to Copy the above settings and to Paste them into a text file somewhere for future reference. Click Next.
Click Next.
The installation process runs for a while and then we get the above screen. We will need to use Stack Builder to install at least the PostGIS spatial extension to PostgreSQL, so we check the box. Click Next.
When we Click Finish, Stack Builder automatically launches.
We have only one PostgreSQL installation on this machine, the one we installed, so that is what appears. Click Next.
We expand the Spatial Extensions line and check the PostGIS bundle choice. We do not actually need everything in the bundle, but given that our machine has plenty of free space it is easier to install everything than to think about how to install only what we need. Click Next.
We do not like the mindless Windows style of using a User\username folder for downloads, so we enter a generic folder location. Click Next.
Depending on how fast our Internet link is, it might take a while to download the PostGIS bundle. When downloads are done we see the screen above. Click Next.
Stack Builder automatically launches the PostGIS installation.
We do not actually agree with some of the political cant in the license (most licenses these days are open source, so it is incorrect to claim that the licenses for most software are designed to take away your rights), but given the wonderful work done by the Free Software Foundation we will not quibble over details. Click I Agree.
We do not want to create a sample spatial database (easy to create such things with Manifold), so we leave that box unchecked. Click Next.
We accept the default installation folder. Click Next.
If we have not previously installed GDAL on this machine, we would choose Yes. However, we have already installed GDAL on this machine, so we will not register the GDAL_DATA environment variable as that may conflict with what we have already installed. Given that we plan to do all of our format conversions in Manifold, not GDAL, this is not a critical decision in any event. Click No.
Click Yes. We choose this in case some day we want to do raster work inside PostgreSQL.
Click Yes. We choose this in case some day we want to do raster work inside PostgreSQL.
Click Close.
We have accomplished the raw installations, but we have yet to alter the PostgreSQL configuration to allow use of PostgreSQL through the network. That comes next in two steps, first, opening a firewall port and then, second, enabling network access within PostgreSQL.
Continue this case study on installing PostgreSQL with the Open a Firewall Port for PostgreSQL topic.
Limits on Simultaneous Users? - PostgreSQL does not limit the number of concurrent connections, but the Windows system on which it is installed may limit the number of concurrent connections. For example, if we install PostgreSQL on a Windows 10 machine we will be limited by Windows, and not by PostgreSQL, to a maximum of 20 connections. If we install on a Windows Server machine we can have more connections. We could also install PostgreSQL on a Linux server for unlimited connections. Manifold is happy to connect to a PostgreSQL DBMS that is running on a Linux server.
GIS Funded the First Relational DBMS - Amazing, but true! In the 1970s IBM researchers published papers on their System R project, a work in progress to create the first relational database. When Michael Stonebreaker and Eugene Wong, two scientists at the University of California in Berkeley, heard about the project they began creating their own relational database, utilizing seed money they had already raised for the creation of a geographic database system, a GIS, for Berkeley's economic group. The geographic database system was to be called INGRES, for INteractive Graphics REtrieval System, a name that Stonebreaker and Wong re-purposed (along with the funds) for their new relational database.
Depending on how we reckon who was first, it appears that Ingres was the first working, production, relational DBMS, in 1974, narrowly beating out System R, which continued in development and was first sold by IBM to a customer in 1977, to Pratt & Whitney. However, System R was the first to use SQL, which became the standard language for DBMS access and manipulation, while Ingres was hobbled by using QUEL, eventually losing share to Oracle and other databases that used SQL.
The PostgreSQL database was originally called POSTGRES for Post Ingres, and was developed at Berkeley as a follow on to Ingres. POSTGRES continued to use a version of QUEL called (of course) POSTQUEL, only in 1994 finally replacing POSTQUEL with SQL. The product was renamed to PostgreSQL in 1996 to emphasize its support for SQL.
Despite it being obvious as far back as the 1970s that GIS should be built on a database that is expressly designed to handle geographic data types, for many years DBMS and GIS took separate paths in technology, business, and programming cultures. For 40 years, DBMS awareness was tacked onto GIS and spatial "extensions" for GIS were tacked onto DBMS. Only recently, with the emergence of Radian in 2017 and then later with Manifold Release 9, have a new generation of Manifold GIS packages emerged that are purposely designed as databases and created from the ground up to handle geographic data, both vector and raster, expressly as required for all GIS functionality, from data storage, to analysis and processing, to visualization and interactive graphic user interface interaction.
File - Create - New Data Source
Open a Firewall Port for PostgreSQL
Enable Network Access to PostgreSQL
Configure PostGIS in PostgreSQL
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines