Difference between revisions of "Database"

From OCAD 12 Wiki - English
Jump to: navigation, search
(Link Object dialog box)
(Set Object Direction from Database Records File:Space.PNGThis function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography)
 
(162 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 +
[[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]
 +
 
==Introduction to Database Connection==
 
==Introduction to Database Connection==
: ''(Database connection is a feature of the Professional edition only)''
+
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:
  
In OCAD you can link objects to information in the database about this object.
+
===General Structure of a Database===
A database normally consists of several '''tables'''. In flat file databases like dBase, each table is a file. All tables in a folder form a database. In other databases like '''Access''' or in spreadsheet programs like '''Excel''' all tables of the database are in the same file.
+
====Table====
A table consists of '''records'''. A record contains the information about one OCAD object.
+
A database usually consists of several tables. There are different forms of databases: In a flat file database like '''[http://en.wikipedia.org/wiki/DBase dBase]''', each table is a file and all tables in a folder form the database. In other databases like '''[http://en.wikipedia.org/wiki/Microsoft_Access Microsoft Access]''' or in spreadsheet programs like '''[http://en.wikipedia.org/wiki/Microsoft_Excel Microsoft Excel]''' all tables of the database are in the same file.
A record consists of '''fields''' which contain the information. A special field is the key field. It normally contains a number to identify the record. To link an OCAD object to a record the number in the key field is stored in the object.
+
  
 +
====Record====
 +
A table consists of records. A record is a row in the table and contains the information about an OCAD object.
  
'''Dataset'''
+
====Field====
 +
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.
  
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.
+
====Example====
You can have several datasets for the same OCAD map.
+
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''', '''YCOOR''', '''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 '''[[Database#Create and Edit Secondary Tables|Secondary Table]]'''. The fourth and the fifth field contain the coordinate and in the last field, the type of the area is indicated.
To create a dataset choose '''Manage Database connection''' from the '''Database''' menu.
+
You can access on databases via '''ODBC''' ('''O'''pen '''M'''anage '''D'''atabase '''C'''onnection). This is an interface to connect to all kind of databases.
+
  
 +
{| class="wikitable"
 +
|-
 +
! 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
 +
|}
  
'''Manage Database Connections'''
+
In OCAD a record is displayed as follows when the corresponding object is selected:
  
: Choose this command to create and edit datasets. A dataset makes the connection to a database. It defines the table to use in the database and inside the table the key field. The key is used to connect an OCAD object to a record in the table. Each object must have a unique key.
+
[[File:Database1.PNG]]
: You can use several datasets for one map. On the left side of the dialog box, there is a list of the datasets.
+
  
: The dialog box has 3 pages:
+
====Dataset====
* '''General:''' General settings for the selected dataset.
+
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.
* '''Secondary tables:''' Create/edit secondary tables.
+
You can have several datasets for the same OCAD map.
* '''Special fields:''' Define special fields.
+
  
==General Settings for the Selected Dataset==
+
==Manage Database Connections [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
ToDo
+
===Create a New Database Connection===
 
+
You have to create a dataset, which can be done by following these steps:
==Create and Edit Secondary Tables==
+
# Choose the '''Manage Database Connections''' command in the '''Database''' menu.
ToDo
+
# The '''Manage Database Connections''' dialog opens.
 +
#: [[File:Database2.PNG]]
 +
# Click the '''New''' button.
 +
#: [[File:DatabaseNewDataset.PNG]]
 +
# 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 '''[[Technical_Data#Computer_.2F_System_Requirements|Access Database Engine]]''' has to be installed if one of the '''Mircosoft Access''' or '''Mircosoft Excel''' database type options is chosen. See at '''[[Map#Map_Information|Map Information]]''' in the '''[[Map]]''' menu if the Access Database Engine is installed.
 +
# 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.
 +
# The dataset is created. Your OCAD map is now connected to the database.
  
==Define Special Fields==
+
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:
ToDo
+
* '''[[Database#Delete_Database_Record_when_Deleting_Object|Delete Database Record when Deleting Object]]'''
 +
* '''[[Database#Create_Database_Record_when_Cutting_Object|Create Database Record when Cutting Object]]'''
  
==dBase==
+
===General Settings for the Selected Dataset===
 +
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'''. Click the '''Edit Fields...''' button to edit the fields of the selected dataset (only available for dBase format).<br>
 +
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'''.
  
Choose this command to create a dBase table and to add or remove fields in a dBase table. In dBase each table is a file. The dBase Table dialog box appears.
+
[[File:Database3.PNG]]
 +
===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.
 +
:[[File:hint.jpg|hint]] OCAD can only link dBase files in the 32 bit version. In 64 bit version a warning appears when opening the ocd file. The warning can be switched on/off in the Preferences in the submenu '''Warnings'''.
 +
:[[File:hint.jpg|hint]] You can convert these databases to Microsoft Access in the [[Database#Convert|'''Manage Database Connections''']] dialog with convert or open this ocd file in OCAD in 32 bit version.  
  
'''Fields:'''
+
[[File:dBaseTableDialog.PNG]]
  
: This box lists the fields of the dBase table. Each field is displayed in a line. Select a field if you want to remove it.
+
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.
+
* '''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 character (C), number (N) or float (F).
+
* '''Type:'''  Choose either '''Character (C)''', '''Number (N)''' or '''Float (F)''' as a field type.
* '''Length::'''  Enter here the number of characters for the field.
+
* '''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 decimal places.
+
* '''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 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.
 
* '''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.
 
* '''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.
 
* '''Delete:''' Click this button to delete the selected field.
* '''New table:''' Click this button to create a new dBase table. The new table will contain 6 predefined fields: ID (intended for the key field), SYMBOL (symbol field), TEXT (text field) and URL (intended for Internet address). You can add additional fields.
+
* '''Character encoding:''' A character encoding type can be chosen in the corresponding dropdown list.
* '''Open table:''' Click this button to open an existing dBase table.
+
  
  
==ODBC==
+
[[File:Hint.jpg|hint]] 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 '''[[Map#Map_Information|Map Information]]''' command in the '''[[Map]]''' menu to see, if the '''Borland Database Engine''' is installed or not. It can be downloaded from the internet for free.
  
Choose this command in '''Manage Database Connections''' 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 just some hints.
+
===Remove===
 +
With this function, you can remove the selected dataset.
 +
 
 +
===Rename===
 +
This function allows you to change the selected dataset name.
 +
#Choose your dataset, which you want to rename.
 +
#Click on the "Rename..." Button.
 +
#:[[File:Rename.PNG]]
 +
#Enter the new dataset name.
 +
#Click on the "OK" Button.
 +
 
 +
===Convert===
 +
It allows you to convert your datasets to Microsoft access either each as single file or all datasets in one file. This works only if your datasets are in dBase (*.dbf).
 +
 
 +
# Get to '''Manage Database Connections''' in Database.
 +
# Click on the '''"Convert"''' Button and the Convert dBase to Microsoft Access dialog opens.
 +
#: [[File:Convert.PNG]]
 +
# Pick your datasets, which shall get converted and if each dataset shall be in a new file or all datasets in a single file.
 +
# Click on "OK" to end the process.
 +
 
 +
===Relink===
 +
This function allows you to change the directory of your datasets.<br>
 +
[[File:Relink.PNG]]
 +
 
 +
===View and edit table===
 +
This function shows you the elements of the dataset and allows you to edit them.<br>
 +
[[File:Viewandedittable.PNG]]
 +
 
 +
===ODBC===
 +
You can access to databases via '''[http://en.wikipedia.org/wiki/ODBC ODBC]''' ('''O'''pen '''M'''anage '''D'''atabase '''C'''onnection). 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.
 
Normally you create a new User DNS.
: For a connection to an '''Exce'''l file, you select the Excel driver and the Excel (*.xls) file.
+
: 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 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.
 
: 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.
  
 +
===Create and Edit Secondary Tables===
 +
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.
  
==Creating a New Database Table==
+
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.
  
For certain functions you need a database table (for instance for the Find and the Hotspot functions in Internet maps). If the Database does not yet exist, you can easily create a database table for an existing OCAD map.
+
Click the '''Edit''' button to change the settings of the secondary table.
  
'''Create a dBase file'''
+
Click the '''Remove''' button to remove the selected secondary table.
# Choose '''Manage Database connections''' from the '''Database''' menu.
+
# Click the '''New''' button.
+
# Enter in the field '''Name''' the name of the dataset.
+
# In the field '''Data source''' choose Create new dBase file and click '''OK'''. A file dialog box appears.
+
# Leave the file name as proposed or change it to your needs and click OK.
+
: Now you have created a dBase table with 6 fields ID (intended as key field), SYMBOL (symbol field), TEXT (text field), URL (link field), X (for horizontal coordinate) and Y (for vertical coordinate). For many cases, such as an Internet map this is enough. If you want to add additional fields choose dBase from the Database menu.
+
  
''' New Dataset'''
+
Fields which are linked to a secondary table are indicated with an asterisk (see below).
  
'''Data source'''
+
[[File:Database4.PNG]]
: -Create new dBase file: Select this option if you want to create a new empty dBase table for the dataset. A new dBase file will be created. To add fields or to modify the existing fields choose dBase from the Database menu.
+
: -Use existing data source: Select this option if the database or table already exists.
+
: [[File:Hint.jpg|hint]] Hint for dBase files: If you do not have installed the "Borland Database Engine" (BDE) then only filename with less than 8 characters are allowed. For example 'test5678.dbf'. You can if the BDE is installed in the Map Information dialog. The free BDE can be downloaded from the www and installed.
+
  
 +
Click the asterisk to display the secondary table:
  
'''Linking objects to the table'''
+
[[File:SecondaryTable.PNG]]
: Let's assume that you want to create an Internet map with street find function. All street names must have a record in the table and must be linked to this table. OCAD provides a quick way to create these links.
+
# '''Select All Symbols''' used for street names.
+
# Choose '''Create Links''' from the '''Database''' menu.
+
# Select the dataset, if the map has several datasets and click '''OK'''.
+
: Now all street names are linked and the map is ready to create an Internet map with street find function.
+
  
 +
===Define Special Fields===
 +
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.
  
==Link Object==
+
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. It is possible to let new symbols get assigned, when the field value is changed.
 +
* '''Assign new symbol when changing field value''': When changing the symbol number in the database field then OCAD change the symbol of the linked object.
 +
** Example:
 +
** Object is assigned to symbol number 900.002.
 +
[[File:AssignNewSymbolWhenChangingFieldValue1.png]]
 +
** When changing the symbol number to 900.003 in the database box then OCAD changes the symbol.
 +
[[File:AssignNewSymbolWhenChangingFieldValue2.png]]
  
This dialog box appears when clicking the Link button in the database box on the right side of the screen.
+
* '''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.
* '''Dataset:''' Select here the dataset to be used for the link.
+
* '''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.  
* '''Key:''' Enter here a value for the key field in the database table. The key points to a record in the table.
+
* '''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.
* '''Create new record:''' Activate this box if a new record should be created in the database table for this object.
+
* '''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.
* '''Remove:''' Click this button to remove an existing link.
+
* '''Angle''': For point and text objects the angle is copied to the chosen database field.
 +
* '''Date''': The date of the object is automatically copied to the database field which you have chosen in the dropdown list. It's value get's adjusted, whenever you change the object.
  
 +
==Database Box [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
 +
===Link Object===
 +
When the map was connected to a database, the '''Database Box''' appears below the '''[[Symbol Box]]'''.
  
'''Linking an Object to a Database'''
+
[[File:Database5.PNG]]
  
To link an object to a database you must have created a '''dataset'''.
+
[[File:Hint.jpg]] The '''Database Box''' is shown right below the '''[[Symbol Box]]''' by default. Only one row of the '''[[Symbol Box]]''' is visible. To move the '''Database Box''' down, simply click and drag the grey bar between symbol and database box down.
  
'''Proceed as follows:'''
+
To link an object:
# Select the object
+
# Select the object which you want to link to a record.
# Click the Link button in the database box on the right side of the screen. The Link Object dialog box appears.
+
# Click the '''Link''' button in the '''Database Box'''.
 +
# The '''Link Object''' dialog appears.
 +
#: [[File:Database6.PNG]]
 +
# Select the dataset which contains the desired record.
 +
# Enter a key. This number is used for the key field. Unless you make any changes, OCAD takes always the next free integer.
 +
# 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.
 +
# Click the '''OK''' button.
 +
# The '''[[Database#Records in OCAD|Record]]''' is shown in the '''Database Box''' now.
  
 +
To remove a link:
 +
# Select the object which the link is to be removed from.
 +
# Click the '''Link''' button in the '''Database Box'''.
 +
# The '''Link Object''' dialog appears.
 +
# Click the '''Remove''' button.
 +
# The link is removed from the object but the record is not deleted from the table.
  
==Find Object==
+
Learn how to link multiple objects to records in the '''[[Database#Create and Update Records|Create and Update Records]]''' article.
  
This dialog box appears when clicking the '''Find''' button in the database box on the right side of the screen.
+
===Records in OCAD===
This function lets you search for an object with a specified key value. If the function is successful, the object is marked in the map.
+
This is how a record looks in the '''Database Box''':
* '''Dataset:''' Select here the dataset to be used.
+
* '''Key:''' Enter here the value of the key to be found.
+
  
 +
[[File:Database7.PNG]]
  
==Select Database Object==
+
The '''Key field''' is indicated with a '''K''' behind the field name. A '''S''' means, that this is a '''[[Database#Define_Special_Fields|Special Field]]'''. A link to a '''[[Database#Create and Edit Secondary Tables|Secondary Table]]''' is indicated with an asterisk. If no sign appears in this column, it is just a normal field.
  
This dialog box appears when clicking the '''SQL Query''' button in the database box on the right side of the screen.
+
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:
  
This function lets you search for object(s) with linked database records. Selected objects are marked in the map and linked database records are shown in a table.
+
[[File:Database8.PNG]]
  
 +
OCAD opens the file in the default program.
  
'''SELECT FROM'''
+
===Find Object===
: '''Dataset:''' Select the dataset to be used.
+
  
 +
Find an object with help of the key by clicking the '''Find''' button in the '''Database Box'''. The '''Find Object''' dialog appears.
  
'''WHERE'''
+
[[File:FindObjectDialog.PNG]]
: '''Field:''' Choose a field of the selected dataset. When you double-click on a field name it is added to the '''SQL statement''' box.
+
: '''Operator:''' Select an operator. When you double-click on an operator it is added to the '''SQL statement''' box.
+
: '''Value:''' Select a Value. When you double-click on a value it is added to the '''SQL statement''' box.
+
  
'''SQL Statement''': The SQL statement should always contain the components FIELD - OPERATOR - VALUE (ex. Length > 430).
+
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.
  
Click '''Select''' button to start the database query.
+
===SQL Query===
  
 +
Click the '''SQL Query''' button to select database objects by a certain criteria. The '''Select Database Object''' dialog appears.
  
'''Assign Symbols, Texts and Angles'''
+
[[File:SelectDatabaseObject.PNG]]
  
Choose this command after importing a Shape file. After importing the objects have no symbol assigned and appear gray (as [[Unsymbolized Objects|unsymbolized objects]]). With this command you can use the information in the dBase table to assign OCAD symbols to the objects.
+
In the '''SELECT FROM''' part of the dialog, choose a dataset.
  
Click '''Symbols, Texts or Angles''' in '''Database''' menu. The appropriate dialog box appears
+
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.
  
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.
+
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.
  
* '''Dataset: ''' Select here the dataset which should be used to assign symbols. Check All to execute the condition for all datasets.
+
Click the '''Select''' button to start the database query. The found objects are selected and the corresponding records are displayed in a table.
* '''DLoad: ''' Click this button to load an existing condition file (*.cnt).
+
* '''DSave: ''' Click this button to save the changes to a condition file (*.cnt).
+
* '''DSave as: ''' Click this button to save the changes to a different condition file (*.cnt).
+
* '''DSymbol: ''' Select here a symbol. For those objects the condition is fulfilled, the symbol number will be assigned.
+
* '''DCondition: ''' Enter the condition here.
+
* '''DMove up: ''' Click this button to move up the selected condition.
+
* '''DMove down: ''' Click this button to move down the selected condition.
+
* '''DAdd: ''' Click this button to add a condition to the list.
+
* '''DDelete: ''' Click this button to delete the selected condition.
+
* '''DExecute: ''' Click this button to execute the assignment.
+
* '''DAngle field: ''' Choose the field in the dataset which contains the angle values.
+
* '''DAngle values: '''  Hint that angle values have to be entered in DD (not DMS).
+
  
==Manage Database Connections==
+
==Create and Update Database Records [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
ToDo
+
With this function, new records can be created or updated for all objects with the selected symbol:
 +
# Choose the '''Create and Update Database Records''' command in the '''Database''' menu.
 +
# The '''Create and Update Database Records''' dialog appears.
 +
#: [[File:CreateandUpdateRecords.PNG]]
 +
# Select the '''Dataset''' the records are to be created in and click the '''OK''' button.
 +
# 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. '''[[Database#Define Special Fields|Special Fields]]''' are updated automatically.
  
==Create and Update Records==
+
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.
ToDo
+
# Make sure you have enabled the '''[[Database#Define Special Fields|Special Fields]]''' for text.
 +
# Select all symbols which are used for street names.
 +
# Choose the '''Create and Update Records''' command from the '''Database''' menu.
 +
# Select the dataset and click '''OK'''.
 +
Now all street names are linked to a record which contains the street name itself as a field.
  
==Update Special Fields==
+
==Update Special Fields from Database Records [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
ToDo
+
'''[[Database#Define Special Fields|Special Fields]]''' 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 '''[[Database#Define Special Fields|Special Fields]]''' are not updated, until you use the '''Update Special Fields from Database Records''' function in the '''Database''' menu. The same applies for fields which were edited manually in OCAD.
  
==Create Objects from Table==
+
# Choose in the '''database''' pannel '''Update Special Fields from Database Records''' and the dialog opens.
 +
#: [[File:Update.PNG]]
 +
# Select a dataset or choose the '''All datasets''' option.
 +
# Then, check all special fields you want to update and click the '''Update''' button.
  
Choose this command to create OCAD objects from database records.
+
==Create Objects from Database Records [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
The coordinates of objects must be stored in the database.
+
[[File:CreateObjectsFromTableExample.PNG]]
  
* '''Dataset: '''Select here the dataset.
+
With this option, objects can be created with location and text data from the database.
* '''Condition: '''Enter the condition for the objects here.
+
# Select the symbol the new objects shall get. This must be a point or a text symbol.
 +
# Choose the '''Create Objects from Database Records''' command in the '''Database''' menu.
 +
# The '''Create Objects from Database Records''' dialog appears.
 +
#: [[File:CreateObjectsFromTable.PNG]]
 +
# Select the dataset which contains the information the object is to be created with.
 +
# Select the field for the '''Easting''' and '''Northing''' which determines the position of the new object.<br> If the dataset is a line text object with two points (P1,P2), P1 has the coordinates of the Easting and Northing fields. The length of the line text is added to the P2 easting coordinate.
 +
# Choose between '''m''' and '''km''' as a unit of measure.
 +
# 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.
 +
# 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.
 +
# 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.
 +
# Finally, click the '''OK''' button.
  
 +
==Assign Symbols from Database Records [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
  
'''Database fields'''
+
After importing for example a Shape file the objects have no symbol assigned and appear as '''[[Unsymbolized Objects]]'''. With this command you can use the information in the database table to assign OCAD symbols to the objects.
  
* '''Horizontal coordinate:''' Select the database field for the horizontal coordinate.
+
Choose the '''Assign Symbols from Database Records''' command in the '''Database''' menu. The '''Assign Symbols from Database Records''' dialog appears.
* '''Vertical coordinate:''' Select the database field for the vertical coordinate.
+
* '''Unit of measure:''' Select the unit of measure for the database fields with the horizontal and vertical coordinate.
+
* '''Text field:''' This field is only activated if an text symbol is selected. Choose the database field for the label.
+
  
 +
[[File:AssignSymbolsByRecords.PNG]]
  
'''Offset'''
+
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:
  
* '''Horizontal offset:''' Enter here the value for the horizontal offset between the coordinate and the reference point of the object. A positive value shift the object to right, a negative value to left.  
+
* '''Dataset: ''' Select here the dataset which should be used to assign symbols. Check '''All''' to execute the condition for all datasets.
* '''Vertical offset:''' Enter here the value for the vertical offset between the coordinate and the reference point of the object. A positive value shift the object upward, a negative value downward.
+
* '''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.
  
 +
[[File:Hint.jpg]] '''Assign Symbols from Database Records''' might make slow progress for big datasets. There is an alternative for shape files by chosing the option '''Use layer information from field''' in the [[Import_Files#Import_Shape_Files|'''Import Shape File''']] dialog and [[Map#Convert_Imported_Layers_to_Symbols|'''Convert Imported Layers to Symbols...''']] afterwards.
  
==Assign Symbols by Records==
+
[[File:Hint.jpg]] If there is an apostrophe in the value then you have to add an addition apostrophe.
 +
For example:  '' 'RIVERNAME LIKE l' 'Avançon' ''
  
Choose this submenu to assign symbols to the objects according to the database.
+
==Add Texts from Database Records [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
 +
[[File:AddTextsbyRecordsExample.PNG|500px]]
  
 +
With this function it is possible to add a text which is written in a field of a record to an OCAD object.
  
==Add Text by Records==
+
# Choose the '''Add Texts from Database Records''' command in the '''Database''' menu.
 +
# The '''Add Texts from Database Records''' dialog appears.
 +
#: [[File:Add.PNG]]
 +
# Choose a '''Dataset''' or check the '''All''' option to take all datasets into consideration.
 +
# Choose the field which contains the '''Text''' to be added. <br> It's possible to assign a parameter condition for the text.
 +
# Assign a text or line text symbol. If no symbol is assigned, the text appears as '''[[Unsymbolized Objects]]'''.
 +
# You can either replace the existing objects or add new objects.
 +
# Enter an '''Object offset''' if you want to have the text slightly displaced from the existing object.
 +
# Click the '''OK''' button.
  
Choose this submenu to assign text to text objects from the database.
+
==Set Object Direction from Database Records [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
 +
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 from Database 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'''.
  
==Define Object Directions by Records==
+
'''Mathematical function:''' Optionally you can define a mathematical function. To convert Radians to Degrees enter ''*180/3.14159''.
ToDo
+
  
==Merge Objects by Records==
+
Click the '''OK''' button when finished.
ToDo
+
  
==Select Linked Objects with Record==
+
The following things are rotated according to the angle field:
ToDo
+
* Text objects
 +
* Point objects
 +
* The pattern of area objects
  
==Select Linked Objects without Record==
+
OCAD does not rotate line or line text objects!
ToDo
+
  
==Select Objects Linked to the same Record==
+
==Merge Objects from Database Records [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
ToDo
+
With this function, objects with the same value on a specified database field are merged. They also must have the same symbol.
  
==Delete Record when Deleting Object==
+
Choose the '''Merge Objects from Database 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.
ToDo
+
  
==Create Record when Cutting Object==
+
The merged objects have to be linked again to the database.
ToDo
+
  
 +
'''Example:'''<br />
 +
[[File:MergeObjectsByRecord.PNG]]
  
Previous Chapter: [[GPS]]
+
You have different river segments on a map. Each river segment have the same river name. With the '''Merge Objects from Database Records''' function, they can easily be merged to one object.
  
Next Chapter: [[XML Script]]
+
==Select Linked Objects [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
 +
===Select Objects with Database Record Links===
 +
Choose this function in the '''Database''' menu to select all objects with a link to an existing record.
  
 +
===Select Objects with Broken Database Record Links===
 +
Choose this function in the '''Database''' menu to select all objects which are linked to a record but the record was not found.
 +
=== Select Objects without Database Record Link from Selected Symbols ===
 +
Choose this function in the '''Database''' menu to select all objects which are not linked to a record. OCAD checks only if a record link exists. OCAD does not check if the record link is broken or not. To check if the record links are broken choose '''Select Objects with Broken Database Record Links'''.
 +
 +
===Select Objects Linked to the same Database Records===
 +
Choose this function in the '''Database''' menu. By choosing this function, multiple objects which link to the same record are selected.
 +
 +
==Delete Database Records without Linked Object [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
 +
 +
Use this function to delete unused database records for example after using the [[Map#Export_Part_of_Map|Part of Map]] function.
 +
 +
Choose '''Delete Records without Linked Object''' in the '''Database''' menu. The '''Delete Records without Linked Object''' dialog appears.
 +
 +
[[File:DeleteRecordsWithoutLinkedObject.png|Delete Records without Linked Object]]
 +
 +
Select the dataset and click the '''Find''' button. OCAD checks for
 +
* records in the selected dataset
 +
* links to OCAD objects found. OCAD does not check if the objects also exists.
 +
* records in the selected dataset without a link to an OCAD object
 +
 +
The ids of the records without a link to an OCAD object are shown in the ''Records to delete'' field. Please note that only the first 100 ids are shown. For the complete list of ids please use the ''Copy report to Clipboard'' function.
 +
 +
Click the '''Copy report to Clipboard''' icon to copy a list with the record ids to the Windows Clipboard. You can paste this list into an text document.
 +
 +
Example of this report:
 +
<PRE>
 +
*** Records found in dataset: (35982)
 +
198
 +
199
 +
200
 +
...
 +
 +
*** Linked objects found: (818)
 +
199
 +
18421
 +
202
 +
...
 +
 +
*** Records without linked objects found: (35165)
 +
49535
 +
49536
 +
49537
 +
...
 +
</PRE>
 +
 +
Click the '''Delete''' button to delete the records according the list from the ''Records to delete'' field. The number of the deleted records are shown in the left status bar during the deleting process. Press the ESC key to abort this process.
 +
 +
[[File:Hint.jpg]] Please note that is not possible to undo this process. So please backup your database before starting the deleting process.
 +
 +
==Options [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
 +
===Delete Database Record when Deleting Object ===
 +
If this option is checked in the '''Database''' menu, the corresponding record is deleted when you delete a linked object in OCAD.
 +
 +
===Create Database Record when Cutting Object ===
 +
If this option is checked in the '''Database''' menu, a second database record is created when a linked object is cut.
 +
 +
==Database Information [[File:Space.PNG]][[File:Pro40px.PNG|This function is available in OCAD 12 Professional.|link=https://www.ocad.com/en/products/ocad-for-cartography]]==
 +
Allows you to see all the information about each dataset.<br>
 +
 +
OCAD checks for invalid database links and shows these in the '''Report''' field when opening the dialog.
 +
 +
[[File:DatabaseInformation.PNG]]
 +
 +
'''All linked objects from all datasets:''' Shows all objects with have link to a database records. OCAD does not check if this record really exists (broken database record links). 
 +
 +
'''Datasets:''' Shows all datasets. Click on a dataset to load the data for this dataset.
 +
 +
'''All keys in database:''' Shows all keys from the choosen dataset. This list shows also keys from database records which are not linked with OCAD objects.
 +
 +
'''All linked objects:''' Shows all objects which are linked with the choosen dataset. OCAD does not check if this record really exists (broken database record links).
 +
 +
'''Report:''' Shows a report about the choosen dataset.
 +
 +
==Database Compatibility==
 +
OCAD checks the compatibility of the dataset. OCAD 12 exists in to different as 32 bit and 64 bit versions.
 +
===Mircosoft Excel/Access===
 +
OCAD 64 bit version cannot connect to Microsoft Excel/Access if the 32 bit version of Microsoft Access Database Engine is installed. The same with 64 bit Microsoft Access Database Engine and OCAD 32 bit version.
 +
<br>In this case use the same OCAD version as installed Microsoft Access Database Engine.
 +
<br>[[File:hint.jpg|hint]] You can switch on/off this warning in the Preferences in the submenu '''Warnings'''.
 +
 +
----
 
Back to [[Main Page]]
 
Back to [[Main Page]]
 +
 +
Previous Chapter: [[GPS]]
 +
 +
Next Chapter: [[Thematic Map]]

Latest revision as of 14:19, 18 October 2016

This function is available in OCAD 12 Professional.

Introduction to Database Connection

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:

General Structure of a Database

Table

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.

Record

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

Field

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.

Example

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, YCOOR, 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 Secondary Table. 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:

Database1.PNG

Dataset

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.

Manage Database Connections Space.PNGThis function is available in OCAD 12 Professional.

Create a New Database Connection

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.
    Database2.PNG
  3. Click the New button.
    DatabaseNewDataset.PNG
  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 Map Information in the Map 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:

General Settings for the Selected Dataset

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. Click the Edit Fields... button to edit the fields of the selected dataset (only available for dBase format).
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.

Database3.PNG

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.

hint OCAD can only link dBase files in the 32 bit version. In 64 bit version a warning appears when opening the ocd file. The warning can be switched on/off in the Preferences in the submenu Warnings.
hint You can convert these databases to Microsoft Access in the Manage Database Connections dialog with convert or open this ocd file in OCAD in 32 bit version.

DBaseTableDialog.PNG

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.


hint 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 Map Information command in the Map menu to see, if the Borland Database Engine is installed or not. It can be downloaded from the internet for free.

Remove

With this function, you can remove the selected dataset.

Rename

This function allows you to change the selected dataset name.

  1. Choose your dataset, which you want to rename.
  2. Click on the "Rename..." Button.
    Rename.PNG
  3. Enter the new dataset name.
  4. Click on the "OK" Button.

Convert

It allows you to convert your datasets to Microsoft access either each as single file or all datasets in one file. This works only if your datasets are in dBase (*.dbf).

  1. Get to Manage Database Connections in Database.
  2. Click on the "Convert" Button and the Convert dBase to Microsoft Access dialog opens.
    Convert.PNG
  3. Pick your datasets, which shall get converted and if each dataset shall be in a new file or all datasets in a single file.
  4. Click on "OK" to end the process.

Relink

This function allows you to change the directory of your datasets.
Relink.PNG

View and edit table

This function shows you the elements of the dataset and allows you to edit them.
Viewandedittable.PNG

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.

Create and Edit Secondary Tables

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).

Database4.PNG

Click the asterisk to display the secondary table:

SecondaryTable.PNG

Define Special Fields

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. It is possible to let new symbols get assigned, when the field value is changed.
  • Assign new symbol when changing field value: When changing the symbol number in the database field then OCAD change the symbol of the linked object.
    • Example:
    • Object is assigned to symbol number 900.002.

AssignNewSymbolWhenChangingFieldValue1.png

    • When changing the symbol number to 900.003 in the database box then OCAD changes the symbol.

AssignNewSymbolWhenChangingFieldValue2.png

  • 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.
  • Date: The date of the object is automatically copied to the database field which you have chosen in the dropdown list. It's value get's adjusted, whenever you change the object.

Database Box Space.PNGThis function is available in OCAD 12 Professional.

Link Object

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

Database5.PNG

Hint.jpg The Database Box is shown right below the Symbol Box by default. Only one row of the Symbol Box 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.
    Database6.PNG
  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 Record 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 Create and Update Records article.

Records in OCAD

This is how a record looks in the Database Box:

Database7.PNG

The Key field is indicated with a K behind the field name. A S means, that this is a Special Field. A link to a Secondary Table 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:

Database8.PNG

OCAD opens the file in the default program.

Find Object

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

FindObjectDialog.PNG

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 Query

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

SelectDatabaseObject.PNG

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.

Create and Update Database Records Space.PNGThis function is available in OCAD 12 Professional.

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

  1. Choose the Create and Update Database Records command in the Database menu.
  2. The Create and Update Database Records dialog appears.
    CreateandUpdateRecords.PNG
  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. Special Fields 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 Special Fields 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.

Update Special Fields from Database Records Space.PNGThis function is available in OCAD 12 Professional.

Special Fields 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 Special Fields are not updated, until you use the Update Special Fields from Database Records function in the Database menu. The same applies for fields which were edited manually in OCAD.

  1. Choose in the database pannel Update Special Fields from Database Records and the dialog opens.
    Update.PNG
  2. Select a dataset or choose the All datasets option.
  3. Then, check all special fields you want to update and click the Update button.

Create Objects from Database Records Space.PNGThis function is available in OCAD 12 Professional.

CreateObjectsFromTableExample.PNG

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 Database Records command in the Database menu.
  3. The Create Objects from Database Records dialog appears.
    CreateObjectsFromTable.PNG
  4. Select the dataset which contains the information the object is to be created with.
  5. Select the field for the Easting and Northing which determines the position of the new object.
    If the dataset is a line text object with two points (P1,P2), P1 has the coordinates of the Easting and Northing fields. The length of the line text is added to the P2 easting coordinate.
  6. Choose between m and km as a unit of measure.
  7. 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.
  8. 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.
  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.

Assign Symbols from Database Records Space.PNGThis function is available in OCAD 12 Professional.

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

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

AssignSymbolsByRecords.PNG

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.

Hint.jpg Assign Symbols from Database Records might make slow progress for big datasets. There is an alternative for shape files by chosing the option Use layer information from field in the Import Shape File dialog and Convert Imported Layers to Symbols... afterwards.

Hint.jpg If there is an apostrophe in the value then you have to add an addition apostrophe. For example: 'RIVERNAME LIKE l' 'Avançon'

Add Texts from Database Records Space.PNGThis function is available in OCAD 12 Professional.

AddTextsbyRecordsExample.PNG

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 from Database Records command in the Database menu.
  2. The Add Texts from Database Records dialog appears.
    Add.PNG
  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.
    It's possible to assign a parameter condition for the text.
  5. Assign a text or line text symbol. If no symbol is assigned, the text appears as Unsymbolized Objects.
  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.

Set Object Direction from Database Records Space.PNGThis function is available in OCAD 12 Professional.

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 from Database 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.

Mathematical function: Optionally you can define a mathematical function. To convert Radians to Degrees enter *180/3.14159.

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!

Merge Objects from Database Records Space.PNGThis function is available in OCAD 12 Professional.

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 from Database 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:
MergeObjectsByRecord.PNG

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

Select Linked Objects Space.PNGThis function is available in OCAD 12 Professional.

Select Objects with Database Record Links

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

Select Objects with Broken Database Record Links

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

Select Objects without Database Record Link from Selected Symbols

Choose this function in the Database menu to select all objects which are not linked to a record. OCAD checks only if a record link exists. OCAD does not check if the record link is broken or not. To check if the record links are broken choose Select Objects with Broken Database Record Links.

Select Objects Linked to the same Database Records

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

Delete Database Records without Linked Object Space.PNGThis function is available in OCAD 12 Professional.

Use this function to delete unused database records for example after using the Part of Map function.

Choose Delete Records without Linked Object in the Database menu. The Delete Records without Linked Object dialog appears.

Delete Records without Linked Object

Select the dataset and click the Find button. OCAD checks for

  • records in the selected dataset
  • links to OCAD objects found. OCAD does not check if the objects also exists.
  • records in the selected dataset without a link to an OCAD object

The ids of the records without a link to an OCAD object are shown in the Records to delete field. Please note that only the first 100 ids are shown. For the complete list of ids please use the Copy report to Clipboard function.

Click the Copy report to Clipboard icon to copy a list with the record ids to the Windows Clipboard. You can paste this list into an text document.

Example of this report:

*** Records found in dataset: (35982)
198
199
200 
...

*** Linked objects found: (818)
199
18421
202
...

*** Records without linked objects found: (35165)
49535
49536
49537
...

Click the Delete button to delete the records according the list from the Records to delete field. The number of the deleted records are shown in the left status bar during the deleting process. Press the ESC key to abort this process.

Hint.jpg Please note that is not possible to undo this process. So please backup your database before starting the deleting process.

Options Space.PNGThis function is available in OCAD 12 Professional.

Delete Database Record when Deleting Object

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

Create Database Record when Cutting Object

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

Database Information Space.PNGThis function is available in OCAD 12 Professional.

Allows you to see all the information about each dataset.

OCAD checks for invalid database links and shows these in the Report field when opening the dialog.

DatabaseInformation.PNG

All linked objects from all datasets: Shows all objects with have link to a database records. OCAD does not check if this record really exists (broken database record links).

Datasets: Shows all datasets. Click on a dataset to load the data for this dataset.

All keys in database: Shows all keys from the choosen dataset. This list shows also keys from database records which are not linked with OCAD objects.

All linked objects: Shows all objects which are linked with the choosen dataset. OCAD does not check if this record really exists (broken database record links).

Report: Shows a report about the choosen dataset.

Database Compatibility

OCAD checks the compatibility of the dataset. OCAD 12 exists in to different as 32 bit and 64 bit versions.

Mircosoft Excel/Access

OCAD 64 bit version cannot connect to Microsoft Excel/Access if the 32 bit version of Microsoft Access Database Engine is installed. The same with 64 bit Microsoft Access Database Engine and OCAD 32 bit version.
In this case use the same OCAD version as installed Microsoft Access Database Engine.
hint You can switch on/off this warning in the Preferences in the submenu Warnings.


Back to Main Page

Previous Chapter: GPS

Next Chapter: Thematic Map