PostgreSQL,
often simply called Postgres for
short, is a popular open source DBMS package. This topic is the
last 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:
Open a Firewall Port for PostgreSQL
Enable Network Access to PostgreSQL
Configure PostGIS in PostgreSQL
Connect to PostgreSQL
This is the last 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. We enabled use of PostgreSQL through networks. We have created a database within PostgreSQL called gisdb and we have enabled the PostGIS extension for that database. Now, we will use that database to show an example of copying and pasting data from Manifold into Postgres.
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.
In the Configure PostGIS in PostgreSQL topic we used Manifold on the same machine as hosts Postgres. For the sake of variety, we now will use Manifold running on a different machine, our desktop machine on which we usually do GIS things with Manifold, and we will connect through our organization's local network to the machine hosting Postgres. The machine we use has an IP address which falls within the sub net range we specified in the Enable Network Access to PostgreSQL topic.
Every machine running Manifold which
will be used to connect to PostgreSQL must
have the PostgreSQL Client .dll installed. If we want to connect
to our PostgreSQL server from a remote machine running Manifold, we must install the PostgreSQL Client
.dll on that remote machine. That is easy to do, as was seen
in the Configure
PostGIS in PostgreSQL topic.
If we have teleported into this topic without reading the preceding topics in this case study, we should make sure to install client software as discussed above.
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 gisdb, and choose Database: postgresql as the type. Click the browse [...] button to launch the Database Login dialog.
Enter the IP address of the server, which in this example is 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.
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. When we see Connection established we press OK.
Back in the Database Login dialog, 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 immediately can see by the presence of PostGIS infrastructure, such as the public.spatial_ref_sys table, that PostGIS has been enabled for the database.
We can load the DBMS with components from Manifold projects by simply copying and pasting them into the DBMS data source, or even more simply, using drag and drop. We can also copy data from the DBMS by simply copying from the DBMS data source and pasting into the Manifold project.
We now will copy and paste some Manifold components into the gisdb database in our PostgreSQL server. Copying and pasting a drawing from a Manifold project into the PostgreSQL database means copying two components, of course, the drawing and the drawing's table.
First, we open another Manifold session (not illustrated) in which we have stored some drawings of Mexico using different projections. We copy and paste those into our working session, as seen above. We have pasted two drawings, each with its table. One drawing shows Mexico using Latitude / Longitude projection, and the other shows Mexico using Pseudo-Mercator projection.
In the illustration above, we have opened the Latitude / Longitude drawing, named MexicoLL.
To copy that drawing and its table into our PostgreSQL database, we ctrl-click on MexicoLL and MexicoLL Table, to highlight them, and then we drag and drop the highlighted items into the Postgres gisdb portion of the project.
Immediately, we see the PostgreSQL equivalent components appear in the Postgres gisdb data source. Manifold has automatically translated the Manifold component names and components into the PostgreSQL equivalents, and has created some new, virtual components such as drawings. To keep the user interface as similar as possible, Manifold is using Manifold-style icons to identify the different components in the Postgres gisdb data source.
To provide a consistent, orderly, user interface, Manifold will automatically generate virtual components, such as folders, some Manifold system tables, and some automatically-generated drawings, that appear to be in the database but which are generated on the fly from metadata and are not a new, real table or other new item added to the database. See the Real and Virtual Components topic for discussion and examples.
We close our MexicoLL tab, and then we double-click on the public.MexicoLL drawing within the Postgres gisdb data source to open that drawing. It opens, still retaining the Style with which it was formatted within Manifold. Copying drawings and their tables into PostgreSQL saves the style those drawings use as well.
We can switch to the Component tab of the Info pane to get information on the drawing we have opened. Manifold has automatically specified the coordinate system within PostgreSQL as required by PostgreSQL.
We can copy another drawing into our PostgreSQL server by ctrl-clicking on MexicoPM and MexicoPM Table, to highlight them, and then we drag and drop the highlighted items into the Postgres gisdb portion of the project.
We close our public.MexicoLL tab, and then we double-click on the public.MexicoPM drawing within the Postgres gisdb data source to open that drawing. It opens, also still retaining the Style with which it was formatted within Manifold.
The Component tab of the Info pane shows information on the public.MexicoPM drawing. Manifold has automatically specified the coordinate system within PostgreSQL as required by PostgreSQL.
However, instead of using the name Geom_x for the spatial index as used by the public.MexicoLL drawing, the public.MexicoPM drawing uses a very long name for the index. That is because PostgreSQL requires the name of an index to be unique throughout an entire database, and not just unique within a particular table. Manifold therefore synthesizes on the fly a unique name for indexes as needed within PostgreSQL.
If we compare the above illustration to the analogous illustrations for the Info pane of the gisdb.MexicoPM in the Connect to MySQL topic, and the dbo.MexicoPM drawing in the Connect to SQL Server topic, we see a difference between how MySQL and SQL Server name indexes and how PostgreSQL names indexes. SQL Server and My SQL require that an index name be unique within a table, but they both allow the same index name to be used in different tables. If we have an index called Geom_x in one table we are free to use that same name for an index, Geom_x in different tables. That is the same as in Manifold, so there is no need to rename an index when copying multiple tables to MySQL or to SQL Server that use the same name for an index. Our drawing as seen above therefore uses the same Geom_x name for the spatial index that most Manifold drawings use by default.
PostgreSQL is different in that PostgreSQL requires index names to be unique throughout an entire database. If one table in the database uses Geom_x as an index name, then no other table in the database can use Geom_x as a name for an index. When Manifold copies multiple tables to PostgreSQL, if an index name is already used by one of those tables, Manifold will, on the fly, synthesize a new index name that does not conflict, automatically adjusting references to that index name in other components.
We can work with components, like drawings and tables, saved into the database as if they were local components stored within our project. So far, we have just opened and viewed the public.MexicoLL drawing and the public.MexicoPM drawing, but we can do more than viewing. We can work with them as if they were resident within the project.
For example, we can Alt-click the province of Durango in the public.MexicoPM drawing and immediately the attributes will appear in the Info pane.
We can click any segment or handle in that province to enable it for editing with the Coordinates tab, just as if it were a local drawing. Or we can click on the Coordinates tab to show the coordinates, and thus enable the object for editing.
If we Ctrl-click the province of Chihuahua, it will immediately be selected and appear in red selection color, just like it would in a local drawing.
Open the drawing's table in the database, public.mexicopm table, and we see that the corresponding record for Chihuahua is also selected and shown in red selection color, just as it would be with a local drawing's table.
We have finished the last topic in the case study on installing PostgreSQL.
See the preceding topic: Configure PostGIS in PostgreSQL
Jump to the beginning of the case study: Install PostgreSQL
Superuser login - It is not a good idea in organizations to always use the superuser login postgres for daily work. A better idea is to learn how to create different logins for users in PostgreSQL and to login with a user account, not the superuser login.
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