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 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 SIS overlay can be exported to PostGIS. SIS 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 SIS 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 SIS to PostGIS SIS 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 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 SIS	Corresponding column definition in PostGIS
   Text	text
   Float	Double precision
   Integer	integer
   Geometry	GEOMETRY
   Date	Time stamp without zone

   
   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:

Note:SIS does not support tables created using non-default convention for table and columns name. For example SIS 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 SIS 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 from being able to style (colour) the data to match the original data. SIS will display all the data in a single default colour.  

Send comments on this topic.

Click to return to www.cadcorp.com

© Copyright 2000-2017 Computer Aided Development Corporation Limited (Cadcorp).