Exporting to PostGIS

SIS is able to export data to a number of databases. This topic deals with exporting data to a PostGIS database.

Loading data into PostGIS

SIS Desktop 9.1 provides the ability to export (load) data into PostGIS (running on PostgreSQL) database using the Geometry type to store the spatial data.

Spatial data contained in any Cadcorp SIS Desktop 9.1 overlay can be exported to PostGIS. SIS Desktop 9.1 will export all the geometry features as well as any attributes contained in the overlay schema. The export utility can also create spatial indexes.

Cadcorp recommend that data to be exported to PostGIS is first copied to an internal overlay so that the data and the schema columns can be adjusted to conform to the PostGIS model.

Implicit Transactions

When an overlay (dynamic or cached) is created in Cadcorp SIS Desktop 9.1 the PostgreSQL database opens an implicit transaction against the base table. This transaction prevents certain type of administration taking place on the database until the user has closed the SWD. For example it may not be possible to change the structure of the base table using the PGAdmin tool until the user closes the SWD.

Exporting to PostGIS

When data is exported from Cadcorp SIS Desktop 9.1 to PostGIS SIS Desktop 9.1 uses the following conventions when creating database tables and supporting objects.

Note: PostGIS is an implementation of OGC SQL92, therefore the names will clash. SIS Desktop 9.1 should only be used with an installation of PostgreSQL that does not have PostGIS enabled.

Table Definitions

Spatial data is exported to PostGIS in the following steps:

  1. A table is created in the users chosen database. All optional parameters are default values (your database administrator will know the default values for your system). For most users the default options are irrelevant.
  2. Columns definitions default to the following PostGIS column types:
    Data in Cadcorp SIS Desktop 9.1 Corresponding column definition in PostGIS
    Text text
    Float Double precision
    Integer integer
    Geometry GEOMETRY
    Date Time stamp without zone

    3. Note: When exporting text data to Oracle, PostGIS or SQL Server 2008 spatial databases the following expression should be added to the schema of the overlay to be exported. The expression should be renamed text_height. 

_ Point_ Height * 0.000352777778 * [dataset Scale]

where _ Point_ Height is a system property

dataset Scale is the dataset scale for the overlay being exported.

Naming Convention

PostGIS tables and column names must conform to the PostgreSQL naming convention. This includes:

  • names should be less than 31 characters
  • names should start with an alphabetic character
  • should not contain PostGIS reserved words such as: "select" or contain spaces or certain characters
  • avoid using names that require double quotes.

Note: Cadcorp SIS Desktop 9.1 does not support tables created using non-default convention for table and columns name. For example SIS Desktop 9.1 will not open tables where the column name has been forced to start with a number or contains a space in it.

Changing the Name of a Table

Users should not change the name of a PostGIS spatial table. Changing the name of a table containing spatial data causes the following issue:

  • SWD containing PostGIS overlays will not be able to retrieve data from the table.

Simple Features Compliant

At the time of writing PostGIS was not OGC simple features compliant. However, the developers of PostGIS are aiming to make the database simple features compliant.

Existing Table Overwritten

If Export to Server [File-Export] is used to export a layer and the user specifies a table name of an existing table in PostGIS (whether spatial or not) the existing table in PostGIS will be replaced.

Exporting PostGIS Overlays

If users have a requirement to export an PostGIS spatial overlay back to PostGIS it is recommended that the data be read into Cadcorp SIS Desktop 9.1 as a cached overlay before exporting it to PostGIS.

Feature Tables

When exporting data to PostGIS ensure that the data has been appropriately feature coded before exporting to PostGIS. Failure to do so will prevent SIS Desktop 9.1 from being able to style (colour) the data to match the original data. Cadcorp SIS Desktop 9.1 will display all the data in a single default colour.