Current location:

What is a Spatial Database?

Introduction

A Spatial Database is a database that is optimized to store and query data related to objects in space, including Point, LineString and Polygon items.

Advances in relational database management technology now gives users the option of storing their GIS data in a spatial database rather than using file based storage. SIS is compatible with the following industry-standard spatial databases:

Oracle (using Locator or Spatial option)
SQL Server 2008 (Spatial)
PostgreSQL (with PostGIS)

The following table shows the benefits of using a database as opposed to file based data storage for spatial data:

	File based Storage	Database
File size	usually limited to 2Gb	Unlimited
Client Memory Usage	Memory intensive	Memory efficient
Multi-user Access	Only crude control, if any	Multi-user access is supported
Managing data	Difficult to manage, data may become duplicated	Central storage of data, easy to locate and backup
Security	File system security	Built in security access to data is provided
Spatial Analysis and Attribute Searches	Carried out on the client. Therefore limited to client capability.	Carried out on the Server. Servers are more powerful, therefore analysis is faster.

Spatial data is stored in database tables, each spatial feature corresponds to a row in the table. The spatial feature itself is stored in a special geometry type that is available in all three databases. The attribution data associated with each spatial feature is stored in the same table row, each attribute corresponds to a column in the table.

The following figure shows the relationship between a spatial feature displayed in SIS and how it is stored as a row in the database table.

Dataset Formats

Memory Datasets

SIS loads most file based data as memory datasets. This means that the entire file is read into memory before the data can be displayed on screen. Once loaded in memory, performance and navigation will be very fast. Load times are dependent on the size of the file and memory available in the machine. File sizes exceeding 50MB will inhibit performance; however this is subject to the amount of memory available for SIS to use. Examples of memory datasets include SIS BDS files, MapInfo .mif files and ESRI .E00 files.

Index Datasets

Certain datasets are supplied as files which geographically conform to a known grid. For example Landline is based on the national grid. SIS can read these types of files as index datasets. Because the files are based on a known grid SIS can automatically open and close these files according to the geographical area visible in the map window. This allows SIS to use memory efficiently. Examples of index datasets are OSGB products supplied in a tiled format such as OS Strategi and 1:50,000 raster.

Cursor Datasets

See Loading Data - Cursor Datasets

Cursor datasets generally reside in databases. Data is selected from the database according to the geographical area on display in the SIS Map Window. A spatial index is used to work out the data required by SIS. Examples of cursor datasets are .fdb files, .shp files (with a .prj file) and TAB files.

When creating an overlay using an RDBMS Cursor Dataset users can choose which type of connection to make to the database. Overlays can have one of three modes:

Dynamic Mode
Cached Mode
One Off Import Mode

The mode can only be set at the time the overlay is created. Each mode gives the overlay different capabilities:

Dynamic Mode

This mode reads the data into SIS with the following characteristics:

Data is selected from the database to fill the geographical extents of the SIS Map Window. If the user pans to a new area SIS requests the appropriate data from the database. This mechanism gives the impression that the overlay contains a seamless map base. It also facilitates efficient retrieval of data from the RDBMS cursor dataset by limiting the rows selected to just those that are required to fill the Map window. This mechanism allows spatial data to be retrieved from large RDBMS cursor dataset spatial layers while keeping network traffic to a minimum. Dynamic mode can only be selected if the spatial layer/table has a spatial index associated with it.
Data is read in a manner that utilises memory efficiently; as users pan around the map the memory is constantly flushed, ensuring there is no loss of performance.
When the map is refreshed (e.g. if the map is panned, zoomed, etc) or a feature is selected the data is immediately retrieved from the database.
If the overlay was created by connecting to the database as the owner of the layer or as a user who has the following privileges: select, inset, update, delete the overlay will be created with a status of editable.
Ensures that the latest version of the feature is being displayed when the map is panned or a feature is selected.
Edits made to the data displayed in the map window will be reflected in the RDBMS cursor dataset immediately.
Subject to database privileges the data is editable.
The underlying database table structure in the RDBMS cursor dataset can be adjusted using SIS. Columns (attributes) can be added or removed.
The Database Query can be used to create spatial and attribute filters. The Query Wizard applies a ‘where’ clause to the SQL select statement used to extract the data from the database. The Database Query Wizard can only be used to run attribute queries on attributes that exist as columns in the underlying RDBMS cursor dataset table.

Cached Mode

This mode reads the data into SIS with the following characteristics:

Data is always displayed in read only mode.
The dataset is only refreshed/synchronised when the SWD is opened.
Data is cached on the client, this allows for fast data interrogation.
Client server communication is kept to a minimum.
Unless the spatial filter is applied when creating the layer the entire spatial layer/table will be selected by the client. Users are advised to always apply the spatial filter and ensure the SIS Map Window extents are set correctly for your data (see Setting SIS Map Window View Extents). If the SIS map view extents are set incorrectly there is a possibility that SIS will run out of memory, this is dependent on the client machine memory and the amount of data being selected from the database.

One Off Import Mode

This mode reads the data into SIS with the following characteristics:

Data is copied from the database table into an internal overlay. The overlay generated by a one-off import should be considered as a ‘snap-shot’ of the database.
The data in the overlay is editable but does not edit data held in the database table.
Creates a copy of the data on an internal overlay.
The internal overlay is never refreshed/synchronised with the data in the database.
Optional annotations cannot be set when creating a one off import overlay. This can be done using Add Theme [Home-Map] and specifying a label theme based on the attribute that contains the text information to be displayed.
Unless the spatial filter is applied when creating the layer the entire table will be selected by the client. Users are advised to always apply the spatial filter and ensure the SIS Map Window extents are set correctly for your data (see Setting SIS Map Window View Extents). If the SIS map view extents are set incorrectly there is a possibility that SIS will run out of memory, this is dependant on the client machine memory and the amount of data being selected from the database.

Note: To interrupt the creation of either a Cached Mode or One Off Import Mode data retrieval process press Ctrl+Alt+Break. This is particularly useful when users attempt to create a one off import or a cached mode overlay from a spatial database and forget to specify a Spatial Filter Intersect with current extent in the Spatial Object-Relational WHERE Clause dialog (Oracle dialog shown as an example).

Top of page

Send comments on this topic.

Click to return to www.cadcorp.com