PostGIS Manager

Intro

PostGIS Manager is a plugin for Quantum GIS that aims to be a simple yet powerful tool for management of PostgreSQL databases with emphasis on support of spatially enabled tables in PostGIS. Target audience for this plugin are users that want to do some administration tasks on the tables in user-friendly graphical interface without having to type SQL commands. This plugin is not meant to be a complex tool supporting virtually all PostgreSQL/PostGIS functionality. Instead, it should help users with typical tasks and speed up the work.

We hope that PostGIS Manager will help to lower the burden when it comes to usage of geodatabases which are for many considered as an interesting but too complex concept to use for real work.

The user should have some basic knowledge about relational databases and their usage. For the simplest use for only viewing database structure and contents of tables, it's necessary to understand what the databases and tables. Before doing any modifications, it's highly recommended to understand the views, schemas, indexes, constraints, SQL syntax, triggers and rules. Otherwise one can do some serious harm to the data stored in the database.

Main window consists basically of two parts:

• database view - this is a widget that lists all tables and views in the database
• central view - displays metadata, tabular preview or map preview

Short intro to PostGIS

If you're familiar with PostGIS, feel free to skip this section.

PostGIS is an extension for PostgreSQL database that adds support for spatial data. Much of the objects we recognize in the world have some location: a restaurant can be defined by a point, a road can be described as a line with several segments and border of a forest can be represented as a polygon. This means that we can extend our tables (containing restaurants, roads, forests or anything else) with another column which will specify object's location. PostGIS implements adds a new geometry type that stores the spatial information. Once such table contains such column, be call it spatial table or geotable.

There are many reasons why one would want to add spatial information to objects. It's possible to select data by specifying a location (all restaurants along some road). TODO

When working with a large set of data (think millions of rows), it's necessary to access the data effectively. Relational databases use indexes on columns to achieve better performance of queries. Traditional indexes however can't be used for the spatial data because they're two (or even more) dimensional. PostGIS comes with a spatial index (R-tree, as opposed to B-tree used for traditional indexes).

TODO: 1 table = 1 layer

Database view

This widget displays database content in a hierarchy. Top-level items are always schemas (i.e. namespaces), their children are both tables and views.

You can find following icons that differentiate the items in the view:

Schema
Table
View

If the database has PostGIS support, the plugin will detect which geometry type is used for the table (points, lines or polygons) and set appropriate icon (instead of the original table/view icon):

Point layer
Line layer
Polygon layer
Unknown layer type

Unknown layer type means that the geometry column is either not listed in geometry_columns or some special type is used.

Tables that contain more geometry columns are listed multiple times, each time for one geometry column. This is however not very common.

Schemas can be expanded and collapsed, by default all schemas are expanded if the database contains less than 100 tables or views.

Note: by default, there is a public schema in every database and is used implicitly. This means that tables in public schema can be accessed just by their name and not necessarily with schema.table syntax.

Central view

The central part consists of three tabs for inspection of the table metadata and their contents:

• Metadata tab displays basic information about the table: row count, privileges, owner, detailed list of fields (columns), constraints (primary and foreign keys, unique columns, checks), list of indexes, triggers and rules. For spatial tables it adds a section about geometry type, dimension, spatial reference and extent. TODO
• Table tab shows contents of the table in typical tabular form. Geometry columns are excluded from the view. The table is read-only.
• Preview tab is available only for spatial tables and displays a simple map from the table contents.

Database connections

TODO

Database info

When the user selects menu Database -> Show info, the metadata tab is filled with information about the database connection: host computer name, current user (role), user's privileges and server version. If the PostGIS support is installed for the database, it will also show information about version of PostGIS and its support libraries.

Management of schemas

TODO

Management of tables

TODO

Data import

TODO

Data export

TODO

Advanced features

Geometry processing

In many case you may want to do some analyses with your spatial tables. Often it's useful to know the area of every polygon or length of every linestring in a spatial table. Just imagine situation where you would like to see polygons symbolized depending on their size. Or as a part of cleaning process you want to delete some of the smallest polygons. In these cases it's handy to have the area or length value precalculated in a separate attribute. Of course it's possible to let PostgreSQL calculate the values on the fly (e.g. using a view with such calculated column), but that leads to performance penalties on retrieval of data.

Geometry processing dialog greatly simplifies the task of calculation of features' area or length. You just select the table and appropriate geometry column (usually there's just one per table) and the attribute which will receive the calculated values.

If data in your table are being added or modified, the calculated values become soon invalid. But don't worry. There's a check box "Add a trigger for calculation on insert and update". If checked, the dialog not only calculates the values (one-time action) but it also creates a trigger function that does the calculation and assigns it to the table. On each modification of the table trigger function updates the area/length attribute to make sure the value is correct.

Note: both area and length are calculated in the units of layer's projection. If the layer has meters as map units, the resulting length is in meters and area in square meters. When using lat/lon coordinates, the map unit is one degree and the resulting length (in degrees) and area (in square degrees) doesn't have much use (IMHO).

Table versioning

This is currently an experimental feature.

Having table with versioning means that you can go back to any moment in the history of the table and see its state. This is achieved by only appending changes to the table instead of doing modifications in place. Every record has additional start and end time attribute. In case the record is still actual, its end time is null. The write operations on the table are modified as follows:

• INSERT - start time is set to current time, end time is set to null
• UPDATE - original record gets its end time set to current time, new record is inserted to the table containing the new version of the record
• DELETE - end time of the record is set to the current time.

To get actual view of the table, you must set a condition that end time is null, otherwise you would fetch contents of the table in the whole history. It's common to use a convenience ("current") view that has this constraint included in the definition.

It's recommended to read TimeTravelling in PostgreSQL [PDF] presentation as the table versioning feature is heavily based on it. Another good source of information (for those understanding German) is Historisierung von Tabellen in PostGIS [PDF] which discusses the topic more in context of GIS application and gives examples of the implementation.

TODO