PostGIS

Before accessing spatial data from a PostGIS database, ensure the SIS Map Window is focused in the correct location and at a reasonable scale for the data being accessed (i.e 1:1250).

SIS Dekstop will automatically select data from the database that coincides with the map extents of the map window.

Note: PostGIS is an extension to the PostgreSQL database instance.

Select Add Overlay > Databases > PostGIS and click Next.

Enter your connection details for PostGIS or select a previously saved connection.

Option Description

Host

The PostgreSQL database server name or IP address.

Port Number

Port number that PostgreSQL service is running on

Database

Specify the database to connect to.

Username

PostgreSQL database user name.

Password

Password for the specified PostgreSQL user name.

Save connection details in UI settings database (with encrypted password)

Check this tickbox if SIS should store these connection details in the UI settings database. The password will be stored in encrypted form for security

Saved connection name

This text box will become active if the Save connection details in UI settings database (with encrypted password) tickbox is checked.

Enter a meaningful name for this connection. This text box cannot be left blank and the same name cannot be used twice.

In this case click one of the Saved connections to complete the connection details. If there are any connections currently in use these will be displayed.

Click Next.

You will see a list of available Database connection types:

Simple

  • Select Simple to see a list of one or more spatial database tables with default configuration options.
  • Select the PostGIS tables you want to open in SIS Desktop as overlays.
  • Select the Coordinate Reference System. For most users the default value can be retained.
  • Click Finish to open the select table and display the data in an Overlay.

Advanced

  • Select Advanced to open a single database table, with options to configure connection type made to the database & an optional feature table
  • Select the table to load into SIS Desktop.
  • Select the Coordinate Reference System. For most users the default value can be retained.
  • Click Next.
  • The PostGIS Connection dialog will be displayed to enable the connection to be configured:

Connection Type

1. Dynamic

This is the default connection type and is only available if the table has a spatial index present. This option allows editing if the correct permissions are present on the database.

The Dynamic connection type maintains an open/active connection to the database throughout the SIS Desktop session. A redraw in the map window will request fresh data from the database based on the current view extents. This becomes very important in a multi-user environment as edits made to the data by other users are displayed after the map has been refreshed.

The dataset si editable, subject to PostGIS user privileges.

Note: Dynamic connection requires spatial index on the table and column.

2. Local cache

This connection type creates an overlay that is a local copy of the entire table or the area that overlaps the current view extent if appropriate check box is ticked.

The connection to the database does not remain open; as a result refreshes in the map window do not return data from the database. Data in the overlay remains in the client memory for the duration of the session.

The overlay is created as a read-only dataset. A saved SWD, when opened again, will read the data from the database and place the new data into memory.

Be cautious when attempting to open tables containing a large amount of data (100,000 features or more may affect client performance depending on amount of RAM available).

You will be presented with the following check to make a local copy of the entire table. Click Yes to load the entire table into local file-based temporary storage.

3. One-off import

This connection type creates an overlay that is a replicated copy of the entire table or the area that overlaps the current view extent if appropriate check box is ticked.

The connection to the database does not remain open; as a result refreshes in the map window do not return data from the database.

Data in the overlay remains as a copy in an internal overlay as a part of the SWD file. The overlay is created as an editable dataset. A saved SWD, when opened again, will NOT read the data from the database.

Note: SIS does not remember the Connection Type settings between uses of a database connection and will always default to Dynamic.

4. Only load data overlapping the current map view extents: Available for Local cache and One-off import connection types only.

Optional Feature Coding

Select the optional feature table and feature code column for your overlay

If you intend to use a feature table with your overlay, ensure the PostGIS table contains the appropriate feature code attribute as well as the SDO_GEOMETRY type containing the spatial data.

If your data was loaded using the Cadcorp OS MasterMap Manager, the feature code is automatically exported for you. If you created your dataset using the export utility in SIS, ensure the feature code column is listed in the overlay schema before exporting the overlay.

Alternatively if there is no feature code attribute you can generate one manually using one of the PostGIS database interfaces such as SQL Plus.

To apply a feature table select the feature code attribute from the Feature Code Column drop-down list. The feature code attribute must be in the PostGIS table in a column of a number type.

Overlay Configuration

Name: Specify a name for the overlay. If this is omitted, a default name for the overlay is assigned (using the database connection information and table.)

Theme: Specify a theme to associate with the data. (optional)

To make changes click More Properties. If you do not want to modify the dataset's Coordinate Reference System, scale, etc. click Finish. The APostGisCursorDTS dialog will be displayed:

Coordinates: Displays the coordinate reference system that the dataset items are defined in.

Scale: The default viewing scale for this dataset. This will affect how Text items convert their point heights into world sizes. Click the Properties... button to display the resizeable PostGIS dialog:

This dialog enables columns from the PostGIS spatial table to be mapped to SIS properties which in the case of Angle column (_angleColumn$) allows symbols to be rotated by the angle specified for that row in the table.

For example if your symbol rotation value is stored in COLUMN_X, enter COLUMN_X.

In the case of the URI column (_uriColumn$) the Open Link command can be used to link to a given feature by entering the name of the PostGIS table column that contains the data. The column name must "exactly" match the case of the column in the database. For example, if you have a series of photographs stored in COLUMN_X, enter COLUMN_X.

Click Close in the PostGIS dialog and click OK in the APostGisCursorDts dialog to return to the PostGIS Connection dialog and click Finish.

Create

This option creates an new empty dataset (i.e. a new table in PostGIS)

Specify the necessary information to create a new table in PostGIS:

Option Description

Schema

Select the schema from the drop-down list.

Table Name

The name of the PostGIS spatial layer. You can enter any PostGIS compliant table name.

Geometry Column Name

The name for the column in the PostGIS table is GEOMETRY and is type geometry. The name of the column can be changed.

Feature ID column

The name of the Feature ID column- this will become the primary key for the table in the database.

Coordinate Reference System

The Coordinate Reference System associated with the spatial layer defaults to the Coordinate Reference System for the overlay.

Default Schema Columns

Use the drop-down to select the required default schema column. Set this to none and define your own schema.

Overlay Configuration

Name: This is the full name of the selected overlay which can be edited.

Theme: This drop-down shows any Themes associated with the overlay:

When you choose a theme from the drop-down and click Next, the Cursor Dataset Details dialog opens.

Configure the dataset as necessary:

Option Description

Column

Attribute information can be stored in a column. Add as many columns required. Specify the appropriate datatype for your new column.

Index

Optionally add an index to the column. Indexes should be added to columns that will be used for filtering data.

Feature Table and Feature Code Column

Apply a feature table to the overlay (Optional)

Dimension

Specify ‘XYZ if the table will be used to store height information (Optional)

Version

Specify the OGC Simple Features version to be used by the overlay

Transactions

Specify the use of database transaction. (Optional)

Transactions

Click on Begin to start a transaction. Click Finish. Begin editing the data.

After the edits are finished redisplay the Cursor Dataset Details dialog and you will two active options:

Rollback

This will release the transaction and allow other users to obtain editable access to the data. The data will return to the sate it was in prior to the changes being made.

Note: Regenerating an overlay whilst in the middle of a transaction causes the transaction to rollback. In addition all undo information for the overlay will be cleared.

Commit

This will commit the changes permanently to the database and release the transaction. All users will now be able to view the modified data.

Only allow editing inside a Transaction

Check this box to ensure edits to the resulting overlay can only be made within a transaction.

Alternatively the three Transaction commands; Begin, Commit and Rollback may also be accessed locally as shown below: