Current location:

Microsoft SQL Server Spatial Dataset

Location: Overlay Types dialog > Databases > Microsoft SQL Server Spatial Dataset

Note: The Microsoft SQL Server Spatial Dataset requires SQL Server 2008 or later because it relies on the spatial datatype.

The Microsoft SQL Server Connection dialog will be displayed:

Server name

Select a server name from the drop-down list, or type the location of the server where the database you want to access is located. Selecting the database on the server is a separate action. Click Refresh to refresh the list. If the server instance uses a port other than the default specify the port after the server address, in the form: Server\SQL2016, 1434

Authentication

Windows Authentication

Select if the provider is to request a secure (or trusted) connection to a Microsoft SQL Server running in Windows NT. When selected, SQL Server uses integrated login security to establish connection using this data source, regardless of the current login security mode at the server. Any login ID or password supplied is ignored. The SQL Server system administrator must have associated your Microsoft Windows network ID with a SQL Server login ID.

SQL Server Authentication

Select to use a supplied user name and password to authenticate your logon information to the data source.

Login

Type the user ID to use for authentication when you log on to the data source.

Password

Type the password to use for authentication when you log on to the data source.

Database

Type the name of the database that you want to access or select it from the drop-down box.

Save connection details in Options database (with encrypted password)

Check this tickbox if SIS should store these connection details in the Options 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 Options 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.

If the Microsoft SQL Server Connection has been made previously, and the connection details saved, the Microsoft SQL Server Connection dialog will be displayed in the form shown below:

In this case click on one of the Saved connections or Currently used connections (if any are in use) to complete the connection details.

Click Next.

The Database connection type dialog will be displayed:

Simple

Opens one or more spatial database tables, with default configuration options.

Advanced

Opens a single spatial database table, with advanced configuration options.

Create

Creates a new, empty spatial database table.

Select the required database connection type and click Next.

Simple

Opens the following dialog to show one or more spatial database tables, with default configuration options.

Check the tickbox for the spatial data source to load.

Select the Coordinate Reference System.

Click Finish.

Advanced

Opens the following dialog to show one or more spatial database tables, with advanced configuration options.

Highlight the Microsoft SQL Server Spatial Data Source to load into SIS.

Select the Coordinate Reference System.

Click Next.

The Microsoft SQL Server Spatial Connection dialog will be displayed to enable the connection to be configured:

Connection Type

Dynamic

This option maintains an active connection to the Microsoft SQL Server Spatial Database, with all redraws returning to the server.

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

Local cache

Makes a local copy of the entire table. The dataset will be read-only.

If this option is chosen the following warning will be displayed when you click Finish:

This is to inform you that the entire table will be loaded into local, file-based, temporary storage. The connection to the database will be remembered and the local storage will be recreated when the SWD is opened.

Check the Don't ask again tickbox if you do not want to see this warning again during this session.

One-off import

Copies the entire table into an Internal Overlay.

If this option is chosen the following warning will be displayed when you click Finish:

This is to inform you that the entire table will be loaded into an Internal Overlay. The connection to the database will be not be remembered.

Check the Don't ask again tickbox if you do not want to see this warning again during this session.

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

Only load data overlapping the current map view extents

This option is available for Local cache and One-off import connection types only.

Optional Feature Coding

If you intend to use a feature table with your overlay, you must ensure that the Microsoft SQL Server table contains the appropriate feature code attribute as well as the geometry type containing the spatial data.

If your data was loaded using the Cadcorp OS MasterMap Manager, the feature code is exported for you. If you created your dataset using the export utility in SIS, you must ensure that 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 Microsoft SQL Server 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 Microsoft SQL Server table in a column of a number type.

Select the appropriate feature table from the Feature Table drop-down list.

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:

Choose the required theme from the drop-down.

If you do not want to modify the dataset's Coordinate Reference System, scale, etc. click Finish.

If you do want to make changes click the More Properties button.

The ASQLServerCursorDTS 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 Microsoft SQL Server Spatial Dataset dialog:

This dialog enables columns from the Microsoft SQL Server 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 Microsoft SQL Server Spatial Datasetost dialog and click OK in the ASQLServerCursorDTS dialog to return to the Microsoft SQL Server Spatial Connection dialog and click Finish.

Create

The Create Microsoft SQL Server Spatial Table dialog will be displayed:

Schema

Select the schema from the drop-down list.

Table Name

The name of the SQL Server spatial layer. You can enter any SQL server compliant table name.

Geometry Column Name

The name for the column in the SQL Server 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.

Advanced configuration

Default Schema Columns

Use the drop-down to select the required default schema column:

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:

Choose the required theme from the drop-down.

Click Next.

The Cursor Dataset Details dialog will be displayed:

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 changes you made will not be applied to the dataset.

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.

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

Only allow editing inside a Transaction

Check this tickbox to ensure no edits can be made outside of the current transaction.

Top of page

Send comments on this topic.

Click to return to www.cadcorp.com