Datenbank

Einführung zur Datenbankverbindung

In OCAD information which is stored in a database can be added to an object (e.g. position of the object, name of the place, URL-Link, length of the object etc.). A database is structured as follows:

Hauptstruktur einer Datenbank

Tabelle

A database usually consists of several tables. There are different forms of databases: In a flat file database like dBase, each table is a file and all tables in a folder form the database. In other databases like Microsoft Access or in spreadsheet programs like Microsoft Excel all tables of the database are in the same file.

Satz

A table consists of records. A record is a row in the table and contains the information about an OCAD object.

Feld

A record consists of fields. Each field contains a single information of an OCAD object, which is described by the corresponding record. Normally this information is a number, but can also be text. For example the x-coordinate of the objects' position. Each record has a key field, which is used to identify the record. This is mostly a number.

Beispiel

The following table contains three records. Each record describes an area object in OCAD which describes real estate and consists of six fields: ID, SIZE, OWNER and XCOOR, XCOOR, TYPE. The ID is the key field, which is used by OCAD to identify the record. The SIZE describes the magnitude of the area. In the OWNER field, there is a number which links to a Sekundärtabellen. The fourth and the fifth field contain the coordinate and in the last field, the type of the area is indicated.

ID	SIZE	OWNER	XCOOR	YCOOR	TYPE
1	724	29	754870	233386	Private Building Area
2	702	12	754900	233442	School
3	422	13	754815	233505	Private Building Area

In OCAD a record is displayed as follows when the corresponding object is selected:

Einige Begriffe

Verbindung

To connect to a table OCAD uses a dataset. The dataset contains the link to the database, the name of the table, the name of the key field and information about other special fields. You can have several datasets for the same OCAD map.

Datenbankverbindungen verwalten

Neue Datenbankverbindung erstellen

You have to create a dataset, which can be done by following these steps:

1. Choose the Manage Database Connections command in the Database menu.
2. The Manage Database Connections dialog opens.

   

3. Click the New button.

   

4. The New Dataset dialog appears. Choose the Create new database file option and select a Database type or choose the Use existing datasource option. Note that the Access Database Engine has to be installed if one of the Mircosoft Access or Mircosoft Excel database type options is chosen. See at Karteninformation in the Karte menu if the Access Database Engine is installed.
5. If a new database file is created, the Save Database File dialog appears. If an existing datasource is used, the location of the datasource has to be specified by clicking the Browse button or connecting via ODBC in the Manage Database Connections dialog.
6. The dataset is created. Your OCAD map is now connected to the database.

When a database connection is newly created, OCAD displays a dialog after closing the Manage Database Connections dialog. You can check two options in this dialog:

• Beim Löschen von Objekten auch Datenbankeintrag löschen
• Datenbankeintrag beim Schneiden von Objekten erstellen

dBase

When OCAD is connected with a dBase table there are additional functions available. In dBase each table is a file. It is possible to edit field settings within OCAD. If a dBase table is loaded, the Edit fields button is enabled in the General tab of the Manage Database Connections dialog. Click it to open the dBase Table dialog.

This dialog box lists the fields of the dBase table. Each field is displayed in a line. There are several functions available:

• Name:: Enter here the name for the field. The name must start with a letter and may contain up to 10 letters and numbers. Letters are converted to capital letters.
• Type:: Choose either Character (C), Number (N) or Float (F) as a field type.
• Length:: Enter here the number of characters for the field.
• Decimals:: This filed is only active if the data type is Float. Enter the number of decimals.
• Move Up: Click this icon to move the selected field one line upwards.
• Move Down: Click this icon to move the selected field one line downwards.
• Insert: Click this button to add a field. After adding the new field, the dBase table is restructured. Existing information is preserved.
• Delete: Click this button to delete the selected field.
• Character encoding: A character encoding type can be chosen in the corresponding dropdown list.

If you do not have installed the Borland Database Engine (BDE), only filenames with less than 8 characters are allowed (Example: 'test5678.dbf'). Click the Karteninformation command in the Karte menu to see, if the Borland Database Engine is installed or not. It can be downloaded from the internet for free.

ODBC

You can access to databases via ODBC (Open Manage Database Connection). This is an interface to connect to all kind of databases.

Click the ODBC button in the Manage Database Connections dialog to create a new ODBC data source or to modify an existing data source. The ODBC Data Source Administrator is started. This is a Microsoft program and contains its own online help. Here are just some hints: Normally you create a new User DNS.

For a connection to an Excel file, you select the Excel driver and the Excel (*.xls) file.

For a connection to an Access database, you select the Access driver and the Access (*.mdb) file.

For a connection to a flat file database like dBase you do not select the dBase file. Instead you select the folder where the dBase file is.

Allgemeine Einstellungen für selektierte Verbindung

The first of the three tabs in the Manage Database Connections dialog is about general settings of the currently selected dataset. In the first part the source of the database is given. It can be either a Database file or an ODBC data source. In the lower part of this tab, the Table which contains the desired information can be chosen. Define a Key field so that OCAD can identify the record. This field is mostly named ID.

Erstellen und bearbeiten von Sekundärtabellen

Secondary tables are tables which are linked to a field in the primary table. This is especially useful, when additional information is added. For example, imagine a map with all real estates of a village. Then, each owner would get a number, which is stored in the primary table. The secondary table would be linked to this number and would contain all names, addresses and contact information of the owners. If an owner changed his contact information, you would update the changes in the secondary table, which would have an effect on all his real estates.

In OCAD, secondary tables can be managed in the Secondary Tables tab of the Manage Database Connections dialog. Click the Add button to add a new one. The Secondary Table dialog appears. First, you have to define the Reference field in the primary table, which is the field, the secondary table is linked to. Then, choose the secondary table which must be in the same dataset. Finally, define a Key field for the secondary table and click the OK button.

Click the Edit button to change the settings of the secondary table.

Click the Remove button to remove the selected secondary table.

Fields which are linked to a secondary table are indicated with an asterisk (see below).

Click the asterisk to display the secondary table:

Spezialfelder definieren

Open this tab to define special fields. Special fields are automatically updated in the database when a modification to the object in the map is made. However, it does not work in the other direction. If you change such a field in the table, the object is not updated.

OCAD provides the following special fields:

• Symbol field: The symbol number of the object is automatically copied to the database field which you have chosen in the dropdown list.
• Text field: For text and line text objects, the text of the objects is automatically copied to the database field which you have chosen in the dropdown list. For multiline text, only the first line is copied.
• Size field: The size of the object is automatically copied to the database field which you have chosen in the dropdown list. For line objects the length and for area objects the area is taken. Adjust the units in the corresponding fields as well as the number of decimals.
• Easting: For point objects the horizontal coordinate is copied to the chosen database field. For line, area and text objects it is the horizontal coordinate of the start point.
• Northing: For point objects the vertical coordinate is copied to the chosen database field. For line, area and text objects it is the vertical coordinate of the start point.
• Angle: For point and text objects the angle is copied to the chosen database field.

Datenbank-Box

Objekt verknüpfen

When the map was connected to a database, the Database Box appears below the Symbolbox.

The Database Box is shown right below the Symbolbox by default. Only one row of the Symbolbox is visible. To move the Database Box down, simply click and drag the grey bar between symbol and database box down.

To link an object:

1. Select the object which you want to link to a record.
2. Click the Link button in the Database Box.
3. The Link Object dialog appears.

   

4. Select the dataset which contains the desired record.
5. Enter a key. This number is used for the key field. Unless you make any changes, OCAD takes always the next free integer.
6. Check the Create new record option. If the object is to be linked to a record which already exists, uncheck this option and enter the key of the record.
7. Click the OK button.
8. The Datensatz is shown in the Database Box now.

To remove a link:

1. Select the object which the link is to be removed from.
2. Click the Link button in the Database Box.
3. The Link Object dialog appears.
4. Click the Remove button.
5. The link is removed from the object but the record is not deleted from the table.

Learn how to link multiple objects to records in the Datenbankeinträge erstellen und aktualisieren article.

Datensätze in OCAD

This is how a record looks in the Database Box:

The Key field is indicated with a K behind the field name. A S means, that this is a Spezialfeld. A link to a Sekundärtabellen is indicated with an asterisk. If no sign appears in this column, it is just a normal field.

It is possible to open an URL directly from the Database Box. Press the Ctrl key and click the field. OCAD opens the URL in the web browser. This works for local files (for example a picture), too:

OCAD opens the file in the default program.

Objekt suchen

Find an object with help of the key by clicking the Find button in the Database Box. The Find Object dialog appears.

Select a dataset and enter the key. Click the OK button. OCAD will display the record in the Dialog Box and will move the view to the corresponding object. Furthermore, the object will be selected.

SQL-Abfrage

Click the SQL Query button to select database objects by a certain criteria. The Select Database Object dialog appears.

In the SELECT FROM part of the dialog, choose a dataset.

In the WHERE part you can give a condition:

Field: Choose a field of the selected dataset. When you double-click a field name it is added to the SQL statement box.

Operator: Select an operator. When you double-click an operator it is added to the SQL statement box.

Value: Select a Value. When you double-click a value it is added to the SQL statement box.

The SQL statement should always contain the components FIELD - OPERATOR - VALUE (example: Length > 430). An SQL statement can be cleared, saved or loaded by clicking the corresponding button to the right of the SQL statement box.

Click the Select button to start the database query. The found objects are selected and the corresponding records are displayed in a table.

Datenbankeinträge erstellen und aktualisieren

With this function, new records can be created or updated for all objects with the selected symbol:

1. Choose the Create and Update Records command in the Database menu.
2. The Create and Update Records dialog appears.

   

3. Select the Dataset the records are to be created in and click the OK button.
4. New records are created and linked to all objects with the selected symbol(s). The next free integers are used for the key fields. If they are already linked to records, the records are updated. Spezialfelder are updated automatically.

As an example, assume that you want to create an OCAD Internet Map with a street find function. All street names must be linked to the database. OCAD provides a simple way to create these links.

1. Make sure you have enabled the Spezialfelder for text.
2. Select all symbols which are used for street names.
3. Choose the Create and Update Records command from the Database menu.
4. Select the dataset and click OK.

Now all street names are linked to a record which contains the street name itself as a field.

Spezialfelder aktualisieren

Spezialfelder are only updated automatically when the linked object is edited. When objects are linked to a database and the database is edited with another program, the Spezialfelder are not updated, until you use the Update Special Fields function in the Database menu. The same applies for fields which were edited manually in OCAD.

The Update Special Fields dialog opens. Select a dataset or choose the All datasets option. Then, check all special fields you want to update and click the Update button.

Objekt erstellen

With this option, objects can be created with location and text data from the database.

1. Select the symbol the new objects shall get. This must be a point or a text symbol.
2. Choose the Create Objects from Table command in the Database menu.
3. The Create Objects from Table dialog appears.

   

4. Select the dataset which contains the information the object is to be created with.
5. Enter a condition. This condition must be an SQL statement: FIELDNAME OPERATOR VALUE (Examples: SIZE > 500, City='Baar'). If this field is empty, all records in the table get an object on the map.
6. Select the field for the Easting and Northing which determines the position of the new object.
7. Choose between m and km as a unit of measure.
8. If a text symbol was selected in the beginning, you have to select a text field. The content of the text field is used as the text of the OCAD object.
9. You can give a horizontal and vertical offset. This is useful for example when you want to import city names. First create a point object for each city, then create a text object with the city name with an offset, so that the name does not overlap with the point object.
10. Finally, click the OK button.

Symbole anhand Datenbankeinträgen zuordnen

After importing for example a Shape file the objects have no symbol assigned and appear as Unsymbolisierte Objekte. With this command you can use the information in the database table to assign OCAD symbols to the objects.

Choose the Assign Symbols by Records command in the Database menu. The Assign Symbols by Records dialog appears.

In this dialog box you can create a list of conditions. You can save the list to a condition file (*.cnt) for later use. You can load an existing condition file to modify or execute it. You have the following possibilities in the Assign Symbols by Records dialog:

• Dataset: Select here the dataset which should be used to assign symbols. Check All to execute the condition for all datasets.
• Load: Click this button to load an existing condition file (*.cnt).
• Save: Click this button to save the changes to a condition file (*.cnt).
• Save as: Click this button to save the changes to a different condition file (*.cnt).
• Symbol: Select here a symbol. For those objects the condition is true, the symbol number will be assigned.
• Condition: Enter the condition here. This must be a SQL statement: FIELDNAME OPERATOR VALUE (Example: TYPE = 'BUILDING').
• Move up: Click this button to move up the selected condition.
• Move down: Click this button to move down the selected condition.
• Add: Click this button to add a condition to the list.
• Delete: Click this button to delete the selected condition.
• Execute: Click this button to execute the assignment.

Texte anhand Datenbankeinträgen zuordnen

With this function it is possible to add a text which is written in a field of a record to an OCAD object.

1. Choose the Add Texts by Records command in the Database menu.
2. The Add Texts by Records dialog appears.
3. Choose a Dataset or check the All option to take all datasets into consideration.
4. Choose the field which contains the Text to be added.
5. Assign a text or line text symbol. If no symbol is assigned, the text appears as Unsymbolisierte Objekte.
6. You can either replace the existing objects or add new objects.
7. Enter an Object offset if you want to have the text slightly displaced from the existing object.
8. Click the OK button.

Objektrichtungen anhand Datenbankeinträgen definieren

With this function the object direction can be defined by an angle (in degrees) from a field of the database.

Choose the Define Object Directions by Records command from the Database menu. A dialog appears. Choose a Dataset in the dropdown list or check the All option to take all datasets into consideration. The define the Angle field. Click the OK button when finished.

The following things are rotated according to the angle field:

• Text objects
• Point objects
• The pattern of area objects

OCAD does not rotate line or line text objects!

==Objekte anhand Datenbankeinträgen verschmelzen

==

With this function, objects with the same value on a specified database field are merged. They also must have the same symbol.

Choose the Merge Objects by Records command in the Database menu. A dialog appears. Choose a Dataset or check the All option to take all datasets into consideration. Then choose the field with the value to be used for merging the objects. Click the OK button when finished.

The merged objects have to be linked again to the database.

Example:

You have different river segments on a map. Each river segment have the same river name. With the Merge Objects by Records function, they can easily be merged to one object.

Objekte mit Verknüpfung selektieren

Objekte mit Verknüpfung und entsprechendem Datenbankeintrag selektieren

Choose this function in the Database menu to select all objects with a link to an existing record.

Objekte mit Verknüpfung und fehlendem Datenbankeintrag selektieren

Choose this function in the Database menu to select all objects which are linked to a record but the record was not found.

Objekte mit Verknüpfung zu demselben Datensatz selektieren

Choose this function in the Database menu. By choosing this function, multiple objects which link to the same record are selected.

Beim Löschen von Objekten auch Datenbankeintrag löschen

If this option is checked in the Database menu, the corresponding record is deleted when you delete a linked object in OCAD.

Datenbankeintrag beim Schneiden von Objekten erstellen

If this option is checked in the Database menu, a second database record is created when a linked object is cut.

---

Previous Chapter: GPS

Next Chapter: XML Script

Back to the Main Page.