Oracle Spatial

This type of database overlay is an editable dataset that stores items in an Oracle Spatial or Locator database.

Cadcorp SIS Desktop requires Oracle Locator as a minimum to be installed in the Oracle database.

Note: Some functionality within SIS desktop requires Oracle Spatial to be installed (with additional licensing). Please check with your Oracle supplier to ensure all software is licensed correctly.

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

SIS Desktop automatically selects data from the database that coincides with the map extents of the map window.

Select Add Overlay > Databases > Oracle Spatial.

Click Next to enter your connection details for the Oracle database or select a previously saved connection.

Option	Description
Server	Oracle Client:- use the oracle Net Service Name as configured in your local tnsnames.ora file Oracle Instant Client :- Cadcorp SIS installation files contain an Oracle Instant client that can be optionally installed. Use the Oracle EZCONNECT format to specify the server value: Host:port/database servicename
Username	Oracle database user name.
Password	Password for the Oracle account.
Save connection details in UI settings database (with encrypted password)	Check this tickbox if SIS Desktop should store these connection details in the UI settings database. The password will be encrypted for security.
Saved connection name	Enter a meaningful name for the connection. Previously used names cannot be used.

If the Oracle connection has been made previously and the connection details saved, the Oracle Connection dialog will be displayed in the form shown below. Click one of the Saved connections to complete the connection details.

If there are any connections currently in use these will be shown as follows:

Click Next and select either a Simple or Advanced Database connection type. Alternatively you can Create your own connection type.

Simple

The Simple option opens one or more spatial database tables, with default configuration options.Oracle Spatial/Locator Layers dialog will be displayed to allow you to select the required layers to load into SIS.

Select Simple to see a list of one or more Oracle database tables with default configuration options.
Tick the required boxes for the Oracle Spatial/Locator Layers you want to open in SIS Desktop as overlays.
To load all listed layers, check the tickbox for Layer Name in the header row.
Adjust the ‘Coordinate Reference System’ if necessary. For most users the default value can be retained.
Click Next to open the Oracle Spatial/Locator Connection dialog.
Click Finish after enabling the connection.

Connection Type

1. Dynamic (default): 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. This connection type maintains an open/active connection to the database throughout the SIS session.

A redraw in the map window causes SIS Desktop to 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.

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

2. Local cache: This connection type creates an overlay that 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. Data in the overlay remains in the client memory for the duration of the session.

The overlay is created as a read only dataset. If you save the SWD when it is opened again. SIS Desktop will read the data from the database and place the new data into memory.

Attempting to open tables with a large amount of data (100,000 features or more) may affect client performance depending on the amount of available RAM. You may see the following warning:

Click Yes to continue creating a local cache.

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; this means 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.

Note: Cadcorp SIS 8.0 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 cacheand One-off import connection types only.

5. ID Column: This parameter is for future development.

Optional Feature Coding

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

Overlay Configuration

Name: Specify a name for the overlay. If this is omitted, a default name for the overlay will be created using the database connection information and table.

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

To make changes click More Properties. (e.g. modify the dataset's Coordinate Reference System, scale, etc.)

Otherwise click Finish. The AOracleSpatialCursorDts 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 Oracle Spatial dialog:

Create

Geometry Dimension specify ‘3D’ if the table will be used to store height information

Option	Description
Table Name	The name of the Oracle spatial layer. You can enter any Oracle server compliant table name.
Geometry Column Name	The default name for the column in the Oracle 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
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 to none to define your own schema.
Geometry Dimension	Specify ‘3D’ if the table will be used to store height information.
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:

Click Next.

Note: After the table in created in the PostgreSQL database an overlay (based on the same table) is added to the current session ready for you to add data.

Transactions

Transactions are a database concept. A transaction allows users to make a series of edits to data without making permanent changes in the database. During a transaction the current user is the only user that sees the changes being made.

Other users see the data as it was prior to the changes being made. The user can then choose to make the changes permanent by committing the changes or undoing the changes by rolling back the data.

Click on Begin to start a transaction.
Edit the data as necessary.
After the edits are completed either ‘Commit’ or ‘Rollback’ the changes on ‘Cursor Dataset Details’ dialog:

Rollback

This will release the transaction in the current session; changes made to the data will not be committed to the database. The data will return to the state 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 data.

Only allow editing inside a Transaction

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

You can also access the three Transaction commands; Begin, Commit and Rollback from the local menu.