PlanetGIS User Guide/Connecting to Information

From PlanetWiki

Contents 1 Connecting to Information 1.1 Introduction 1.2 Supported Database Platforms 1.2.1 dBase support to be partially dropped 1.2.2 SQLite, Planet's new built-in SQL engine 1.2.3 Microsoft ADO 1.2.4 MySQL 1.2.5 PostgreSQL 1.2.6 Oracle & MSSQL Server 1.2.7 Firebird SQL 1.2.8 Embedded databases 1.3 Setting up a Database Connection 1.3.1 Relative paths 1.3.2 Client libraries 1.3.3 Creating a new connection 1.4 Tables 1.4.1 Relationships 1.4.2 Referential integrity 1.4.3 Views 1.4.4 SQL 1.5 Fields 1.5.1 Special fields 1.5.2 Display & input formatting 1.5.3 Lookup fields 1.5.4 Calculated fields

Connecting to Information

Introduction

The power of GIS really only presents itself fully when you attach database information to your geographic features. PlanetGIS uses the concept of detail tables to which one can attach further detail tables. A detail table with detail tables becomes the master table in that relationship. PlanetGIS allows you to attach an unlimited number of tables to each feature class, and each table may have any amount of detail tables attached to it, so that you may create a “drill-down” effect to get to the data you're interested in. Detail tables may be related to your geographic features in a one-to-one, one-to-many or many-to-many relationship.

To attach a detail table to a feature class, activate design mode and locate the detail tables node under the feature class. Right-click on the node and select New.

After clicking on New, the tree view will go into edit mode so that you can name the node. This name does not have to be the same as the table's. You can choose any descriptive name that you want to appear on screen. You might want to name the first table attached to a feature class simply, “Info”.

Supported Database Platforms

PlanetGIS is currently (as of version 3.0.7) undergoing a major overhaul of the way in which it connects to databases. If you are a seasoned PlanetGIS user, the following section might be of much interest. During the transition period both the old database “drivers” and the new, less tested ones will be available. It is intended that the older drivers be a fall-back for when a user experiences difficulties with a new driver. The old drivers will be removed in version 3.1 (roughly end-2009).

The legacy database drivers are:

• Dbase
• Microsoft ADO
• MySQL 3.23
• PostgreSQL 7/8
• Oracle
• MS SQL Server

The new drivers (currently in beta-testing) are:

• ADO
• ASA 7/8/9
• Firebird 1.0, 1.5, 2.0, 2.1
• Firebird Embedded 1.5, 2.0, 2.1
• Interbase 5, 6
• MSSQL
• MySQL 4.1, 5.0
• MySQL Embedded 4.1, 5.0
• Oracle 9i, 10g
• PostgreSQL 7, 8
• SQLite 2.8, 3
• Sybase

In-house experience as well as the numbers of users currently using each particular database platform determines which drivers will be stable first and continue to be better tested and quicker debugged. The drivers that will be graduated out of “beta” first due to them being proven stable and all bugs fixed will be as follows, roughly in order:

• SQLite 3
• Microsoft ADO
• MySQL 5.x
• PostgreSQL 8.x
• Oracle 10g
• Firebird 2.1
• MSSQL

Drivers that might never come out of “beta”, simply because we don't use them and know of nobody else using them with Planet:

• Sybase
• Interbase
• ASA

dBase support to be partially dropped

The variety of interpretations and additions to the dBase format have made this one a minefield. PlanetGIS will always be able to read most dBase files and will produce dBase files as part of ESRI Shapefile exporting, but direct editing of dBase files will be discontinued in version 3.1.0. One of the main reasons is that this is the only format on which one cannot create SQL (Structured Query Language) queries. Another major reason is that data kept in a dBase table is most prone to corruption, either by PlanetGIS, disk errors or other software. Any spreadsheet application will open a .DBF file and recreate the entire table in the sub-format of its choosing when you click Save. Some will only save the first 65565 rows. Furthermore, Planet's filtering, locate-by-attribute, etc. functions have never worked properly on dBase tables. In future, dBase tables will be automatically converted to SQLite tables and the original left untouched and untouchable.

SQLite, Planet's new built-in SQL engine

Already being tested intensively in applications where mobile computing and desktop computing meet on the database level, this SQL engine is shipped with every PlanetGIS installation since 3.0.7. SQLite is an amazing database platform in that it adds less than 500kb to the installed footprint, has impressive support for the SQL standard including complex joins and optimizations and is lightning fast. There is also nothing to configure, similar to using an Access .mdb file for your project, but it is open source under a liberal license that allows commercial and proprietary use without paying license fees. SQLite is not intended for use in a client-server environment but can be used in a multi-user environment, although it is not currently encouraged since table-wide locking will slow things down with only a handful of very active users.

Read more about SQLite here: www.sqlite.org

Microsoft ADO

The MS ADO driver provides an alternative to some of the other drivers in that you may have the required ADO providers installed on your computer or distributed with your database software. This is also the only way to connect to Access .mdbs and ODBC drivers.

ADO and Microsoft Access is the database used by the majority of PlanetGIS users, but that doesn't make it the best choice by a long shot...

MySQL

This is the database that runs more web applications than anything else on the planet. Its clean SQL dialect, simple installation and blistering speed has made it the favourite of web programmers. MySQL is a very good choice for applications with hundreds of simultaneous transactions, but that doesn't mean you shouldn't use it for single user applications. MySQL is open source, but not liberally so. Its creators have made a huge effort to turn it into commercial product while keeping a “community edition” under the coveted GPL open source license. Being GPL, it means your software has to be compatible with the GPL license too. That means you cannot package a non-open source product with MySQL. Because of this attitude MySQL has shunned away some of the more purist open source enthusiasts, thereby losing a lot of community support. On the other hand, it has been purchased by the megalithic Sun Microsystems, which for some people means that the software is rock solid and will be well funded and supported for a long time.

The MySQL Community Server may be downloaded free of charge from their website: http://dev.mysql.com/downloads/

PostgreSQL

The favourite database platform of open source purists for over two decades, this database platform used to be far ahead of MySQL in terms of capabilities but somewhat slower and quite difficult to install and administer. These distinctions have narrowed and greyed a lot over time. Some think of PostgreSQL as an “Oracle clone”. This is a very stable, incredibly well tested and very capable database platform supported and developed by a community of hundreds of the best minds in the computing world. Many organizations are gradually moving their applications from MySQL to PostgreSQL due to licensing costs. On the other end of the spectrum you have programmers that are getting more into the spirit of open source and are drawn from MySQL to PostgreSQL and its active community.

PostgreSQL may be downloaded from their website: http://www.postgresql.org

Oracle & MSSQL Server

Many big organizations want Oracle or MSSQL and nobody has ever been fired for investing heavily in these products. The products from these companies are seen to be the most reliable and are trusted by banks and governments. Both companies also provide a free version which limits the total size of the databases than can be created. See http://www.oracle.com and http://www.microsoft.com/sqlserver

Firebird SQL

Lesser known but a very worthy alternative for MySQL or PostgreSQL. Firebird is especially appealing because it has an “embedded” version with a liberal open source licence that allows its use in proprietary applications. Future versions of PlanetGIS might have the Firebird Embedded server as an option during installation.

Firebird's website is http://www.firebirdsql.org/

Embedded databases

An embedded database server works exactly like a database server installed on a different computer than the client, but instead of the client library (a .DLL on Windows) connecting over a network to the server, the “client” DLL becomes the server, transparently to the application. This is especially useful because the same database can be moved between a client-server environment and an embedded (single user) environment without any changes to the software, application configuration or database structure.

Embedded databases are useful if any of the following considerations are important:

• Zero configuration
• Running database applications off DVDs or other portable media
• Mobile applications, where space and computing power is limited.

Firebird SQL and MySQL have embedded versions of their software and SQLite is by design an embedded database. The embedded database drivers are denoted by a postfixed “d” in the driver name.

Setting up a Database Connection

There is a significant change in the way database connections are presented starting with version 3.0.7. Prior to 3.0.7 each database connection was arbitrarily named when it was created. From version 3.0.7 and onward, a database connection is identified by a resource identifier string, similar to a URL for an internet resource. The first part of the resource identifier indicates the driver, followed by the location (either a path or an IP address or a domain name), the database name if applicable and the user name. For ADO, the connection string as used by ADO is appended as a parameter.

When listing database connections, PlanetGIS now shows unique connections of all projects that are open. Once a connection is selected for a table that isn't in the same project of the table, a copy is made and stored in the project automatically. You can now identify exactly what each connection is used for at a glance. The only down side is that these identifier strings can get quite long.

Another new feature is that database connections may be set to be displayed in info mode (the i button at the top-centre). When this is enabled for a database connection, all its tables will be listed, and when clicking on a table, you will be able to browse (and edit) the contents.

You can create database connections wherever you can select them, and view and maintain the complete list under Tools->Database->Connections.

In the example shown here there are a number of connections to Access databases, a connection to a directory of dBase tables, a MySQL, Oracle and SQLite connection. A few of the shown connection strings are now dissected as illustration.

The first part, up to the :// indicates which driver is being used.

 ado://SA-places.mdb?Provider=Microsoft.Jet.OLEDB.4.0;data source=C:\........

 The red part is extracted from the “data source” parameter, if present, 
 without the path information. Since Planet simply appends the ADO connection 
 string, which could be quite long, it was decided to put this in front of the
 connection string to make the resource identifier easier to read. The 
 “data source” parameter is manipulated as described below.

 dbf://.

 A dBase driver simply points to a directory. All the .dbf files within that 
 directory are available as tables of the database connection. The dot means 
 the same directory as the map (project file) and two dots would mean one 
 directory higher up than the map's directory.

 mysql://root@localhost/sa

 root = user name for the database.
 localhost = standard name of the “loopback device”, i.e. a TCP 
 connection to the same computer as where the software is running.
 @ indicates a user name preceding and a server identifier following.
 sa = name of the database (or schema if you prefer), preceded by a slash (/)

 oracle://d0329251@pmm1

 d0329251 = Oracle user name.
 pmm1 = server identifier, either the computer name or TNS connection 
 set up on the client computer. In Oracle, the user name specifies the default 
 “schema” which belongs to the user.

 zdbc:sqlite-3://..\Sandbox\planetgis-dl\water.db

 SQLite only requires a path to the database file. In this case it is a 
 relative path, one up from the map then into the folders as shown

Relative paths

PlanetGIS projects are meant to be portable. To keep your map portable, always keep databases and other linked media on the same drive. Anything that's on the same drive will get a relative path, even if an exact path is specified. For example, if you have both your map files and your database file in C:\Maps\MyProject and you specify C:\Maps\MyProject\MyDatabase.db as your SQLite database file, the path to the database will be reduced to “.\MyDatabase.db”. If you copy the entire directory onto a DVD, it will work on any computer with PlanetGIS installed.

Client libraries

Most databases require a client library in order to connect to the server. On Windows, this is a .DLL file, possibly accompanied by some auxiliary files needed by the DLL. Client libraries are changed over time to accommodate more features of the database server as they are developed, fix bugs, improve security, etc. Sometimes, when client libraries are changed the software that uses the client library needs to be changed as well. The makers of database servers usually try to keep new versions backwards compatible. For example, PlanetGIS versions prior to 3.0.7 uses a MySQL 3.23 client library that can connect to a MySQL Server of any subsequent version (currently the latest is 5.1) but it cannot make use of more securely encrypted passwords introduced in version 4. For embedded servers like SQLite, the client library also includes the software to run the server.

Many of the new drivers available with PlanetGIS includes a version number. This allows you to specify which client library you want to use, regardless of what the version is of the server that you are connecting to. If you are unsure which version to use, or want your map to always use the latest versions as they become available, choose the driver without a version number.

To connect to Oracle you need to install an Oracle client. The Oracle client cannot be included with a PlanetGIS installation because Oracle's software license doesn't allow it and it would add 18MB to the installation file. The Oracle client for 32-bit Windows is here: http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

Dbase support is built into Planet, SQLite is always installed with Planet and the MySQL and PostgreSQL clients are now included as optional components during installation.

ADO and the Jet4 provider needed to connect to Access .MDBs have been included with the Windows operating system since 2000.

Creating a new connection

To create a new database connection, click Add... in the Database connections dialogue box. The dialogue box that follows changes according to which driver you select. Shown here is an example of connecting to an Access database. The Jet 4 provider is for versions of Access prior to 2007, with database files having an .mdb extension. If you have upgraded your database to an .accdb, use “Microsoft Office 12.0 Access Database Engine” instead. Then click Next>> to advance to the next tab and on the ellipsis (…) button to select your database file.

When you click OK, the connection string will be created for you. Note the check box below the connection string. Be sure to have this unchecked if you don't want other users to be able to browse all the database tables.

The connection properties for the PostgreSQL driver is shown on the left and is similar for most of the other drivers.

Host name / IP. This is the name or the IP address of the server. The name must resolve to an IP address using either the DNS server for which your computer is configured for or the hosts file of your operating system.

Port. If your database server is listening on its standard TCP port you can leave this field blank. Only enter the port number here if your server was configured for a non standard port. For example, MySQL's standard port is 3306 and does not need to be entered.

User name / password. This is your user name and password as set up in your database, not your operating system login credentials. Check Prompt for user name and password if you do not want your password to be saved with the map and prefer that it is entered each time you connect to your database.

Database. Select the database (or schema if you prefer) here. It is not required for Oracle, but it is required for MySQL.

Tables

References to database tables are created under the Detail tables section of a feature class or under the Tables section in Design mode. (See Design Mode). Although these are only references and not the actual tables themselves, we will call them “tables” for sake of brevity. A table reference in Planet allows you to specify an existing table, an SQL statement or even customized software plugins to handle your information. There are 3 critical pieces of information required to reference an existing table in your database:

• A database connection,
• The name of the table, and
• An index field that corresponds to the keys of your features.

It is important that your database has an index on the field you select as the “index field” in order to locate records quickly. Whenever you click on a feature on the map and view its attributes (database information) Planet constructs an SQL query that might look something like this:

SELECT * FROM mytable WHERE Id=1

The number, 1, at the end is the key of feature that you clicked on. In the case of multiple selections the SQL statement might look something like this:

SELECT * FROM mytable WHERE Id IN (1,2,3)

In this case you selected three features on the map: “1”, “2” and “3” being their keys. The SQL query is sent to the database server and a number of rows (or records) is returned.

Relationships

In a relational database tables are inter-related to form connections, or “associations” between them. Tables that have nothing to do with each other will typically belong to separate feature classes or different maps. An association between two tables is made by selecting a field from each table for which the content must be the same to establish the relationship. This is often called a master-detail relationship because the second table often contains more information (more detail) related to the master table. From the perspective of the master table, in a specific relationship, the field used to establish the association is called the key and the field of the detail tabled called the foreign key. From the perspective of the detail table the field in the master table is the master key and the field in the detail table, the key, or sometimes the primary key.

In PlanetGIS, the top-level detail table attached to a feature class has the key of the geographic feature as its master key. A detail table often has one or more further master-detail relationships, in which relationships it becomes the master table. These could also be lookup tables. A lookup table is a table typically used to store textual explanations of attributes that are stored in a table as numbers, instead of repeating the text for each row with the same attribute. This saves space and allows you to modify the description, for example when translating it or correcting a spelling error.

When setting up a master-detail relationship it is important to specify the type of relationship. The most simple type is a one-to-one relationship. That means that each row of the first table will have only one corresponding row in the second table (or none at all). A one-to-many relationship will allow several records of the second table to be associated with one record from the first. A lookup table is an example of a many-to-one relationship. Planet handles multiple selections of features on the map differently according to the type of relationship you specified. In one-to-one relationships you will be able to modify an attribute (field value) and the changes will be applied to all records associated with the features you have selected. In a one-to-many relationship it is assumed that each feature has multiple records and such sweeping modification is not allowed.

It is important to mention here that the relationships you create in Planet are completely separate from the relationships you may have created in your database. There is no standard way among database platforms to provide a software application with information about relationships and it is therefore necessary to duplicate this information in the application. That doesn't mean that the relationships you created in your database will not have an effect on your operations on the data in PlanetGIS.

Referential integrity

Referential integrity is the enforcement of certain rules by the database server to ensure that the information contained therein remains reliable. An example of this is cascading deletes. In most cases, in a master-detail relationship, the detail information is no longer required when the master information is deleted. A referential integrity rule containing the cascade delete option will automatically delete such detail records. In PlanetGIS you also have the option to delete records when a feature is deleted. This option is available under the second tab of the table properties dialogue box where you specify the master link.

Another example of referential integrity is to disallow deletions of records that have corresponding records and to disallow the changing of a key value that doesn't have a detail record. A lookup table, for example, might have entries from 1 to 10, each with a description. A table utilizing such lookup table will most likely have a field that can accept numbers larger than 10, but a referential integrity rule will disallow such number.

Apart from the case of deleting features and their associated records, all referential integrity rules must be set up in the database, not in Planet.

Views

A view in a relational database is nothing other than a stored SQL query and is often indistinguishable from a table. Views are useful to combine certain fields from two or more tables in terms of the relationships that were defined between them. In PlanetGIS, views will appear as tables. In most cases the data in a view cannot be modified.

SQL

SQL (Structured Query Language) is a powerful means of requesting information from a database server. SQL allows you to specify criteria for the records that must be retrieved (i.e. a filter), join information from different tables, group results and do many operations on the values of fields like adding the values of two fields or summing the values of fields grouped by a given criteria. Learning to use SQL is extremely beneficial to a GIS practitioner. Some database products like Access offer a “Query builder” that enables you to construct SQL statements without knowing the SQL language. These tools provide a way of looking at the created SQL and that can be pasted into a PlanetGIS table reference.

A simple SELECT statement was shown above. Here we will demonstrate a simple JOIN statement to illustrate the power of SQL. Lets assume two tables in our database. One is called customers and one is called orders. Good database design dictates that each customer has a unique identifier (id) as do each order as well as a one-to-many relationship existing between the two with the inclusion of the customer's id in the order table. If you wanted a quick report to see the orders that have been placed in the past month, you might like to see the customers' names and perhaps addresses too. Here is an example of how that might be achieved:

SELECT customers.Name, order.Item, order.Qty, order.Price FROM customers INNER JOIN orders ON customers.Id=orders.CustomerId WHERE orders.Date BETWEEN '2009-01-01' AND '2009-01-31'

SQL comes in various “dialects”. The above statement will work with MySQL but not Oracle due to different conventions for specifying dates.

To use SQL statements with Planet, select SQL statement in the General tab of the table properties dialogue box. The third tab will change to “SQL”. You need to supply an unlinked SQL statement in the first input box. An unlinked statement does not restrict the result set to records associated to only one or more features but returns the whole table, perhaps still with some filtering criteria if required. This statement is used for table-wide operations like generating thematic queries. To test if the SQL statement is correct, click on the Test tab to view the results.

The linked SQL statement can in most cases be generated automatically by choosing the index field. There are cases where you will have to make changes to this statement to make it work though. The screenshot above is a good example of a case where the SQL had to be generated in Access (Access requires the parenthesis on the second line while it is not required by MySQL) as well as having to manually edit the linked SQL (Planet created the WHERE clause as “WHERE [Id]=:Key” but Id is ambiguous, it needs to be more specific, i.e. A.Id).

Fields

The fields tab provides an opportunity to add only certain fields from a table/query to be displayed in PlanetGIS' interface. An empty field list will always cause all fields to be displayed. Once a field is added to the field list, Planet will expect it to remain present in the source table, which is a potential for future error messages. Also, when adding fields make sure you include the index field or you will receive an error message.

Adding fields here has the advantage that each field can be customized in many ways. Once added the types will show as “unknown”. Click on the Test tab so that the table/query will be opened. Return to the Fields tab to see the field types and sizes.

To customize a field, select it and then click the Properties button.

You may choose to hide a field (it is fetched from the database server and used for some purpose, but you do not wish the user to see it in Planet) or to make it read-only (user cannot change the content).

Special fields

The second tab allows you to assign special functions to individual fields. According to the function, Planet will provided additional functionality. Special fields are shown as hyperlinks and Planet will take the following actions when you click on it:

• Multimedia
  and open a media player and run the file contained in the field. This could be a audio / video clip, etc.
• Document
  Planet will ask Windows to launch the associated application for the file contained in the field. It is the same process as double-clicking on a file in Windows Explorer. Double-clicking on a PDF, for example, will open Adobe Acrobat with that particular PDF document. If the contents of the field starts with “http://”, your default web browser will be opened and start to load the URL.
• Picture
  Planet will open an image file with its internal image viewer. To use an external viewer, specify “Document” instead.
• Map
  The field may contain the key of a feature and Planet will “jump” to that particular feature – if found – when you click the hyperlink. You need to specify which feature class or feature group should be searched for the key.
• Module
  A custom software plugin could be executed for a field with a special purpose of “module”.
• Spreadsheet
   ?
• Autoform
   ?
• Shortcut
   ?
• Position
  If a GPS is attached to your computer and recognized by Planet, the current location will be placed in this field automatically.

Display & input formatting

Label. Enter an alternate display label for the field here. If the field in the database has been shortened, this is useful to make it more clear what the contents of the field are / should be.

Format. Todo.

Width. Todo.

Mask. Todo.

Min. Todo.

Max. Todo.

Lookup fields

You can specify how alternate (usually more descriptive) text will be displayed for a field. There are 3 methods: using a lookup table, a SQL statement or a list.

Table. If the current field is an existing field it becomes the key field in a lookup table relationship. For calculated fields (see below) you can choose any other field in the table for the key field – the one that contains the actual value that is stored in the table. Lookup field is the field creating the association and lookup result field is the field which contents will be displayed instead. You may (optionally) select a field to be used for sorting the fields in a desired order in the dropdown list that will be displayed for the field when it is editable.

SQL statement. You can also specify SQL, for example SELECT Description FROM mylookuptable WHERE Id=:Key AND AnotherMatchField=:SomeIdField AND CriteriaField=”My criteria” ORDER BY SequenceField. This is useful, for example, when you need the match to be done on more than one field, although that might make the field read-only.

List. This method allows you to enter your own lookup list without using a database. Very useful for short lists like Yes/No/Uncertain/Unknown/etc. Key must match the content of the field (or will be entered into the field) and Value is what will be displayed to the user.

Calculated fields

A calculated field is a field that is not physically present in the database table, but calculated automatically from values in other fields. There are two types of calculated fields in PlanetGIS: lookup fields and expressions. To create a calculated field, click New instead of Add next to the field list of a table. If the new field is to be a lookup field it works the same as described above, except that you have to select an existing field as the key field. This field will be modified if the lookup value is changed. If you are using an expression to calculate a field value, the field will be read-only.

Todo: describe expressions