Brian Brown, MS, MS
TatukGIS Consulting: GIS Consulting, Application Development, Training, and Support. Specializing in TatukGIS solutions. A U.S.-based TatukGIS partner since 2003. Working with ESRI products since 1995. Dubuque, IA & Galena, IL. www.tatukgisconsulting.com.
ArcSDE-like Functionality Out-of-the-Box: The TatukGIS Enterprise GIS Database
April 5th, 2012 by Brian Brown, MS, MS
Most GIS-savvy professionals have heard of ESRI’s ArcSDE. ArcSDE is a black-box piece of software that allows spatial features to be stored in an enterprise database. ArcSDE is sold separately from other ESRI software. Typically one or more individuals within an organization must be trained in the use of ArcSDE so that they can administer the ArcSDE database for the organization.
The Enterprise GIS Database
Traditionally, GIS software vendors have put a large cost premium on the ability to store map features in an enterprise database. (An enterprise database is a database accessible by all levels of the organization and is typically located on a server.) This premium has led to the common belief that what’s going on behind the scenes of an enterprise GIS database is overly complex. The truth is that it’s not complex at all, it’s rather simple.
A database can be used to store any type of information that is usable on a computer. The most common kinds of data that people are familiar with are text, numbers, and dates.
But databases can also store more complex kinds of data like pictures, videos, and map features. These more complex data types are typically stored in a special binary field type (sometimes called a BLOB or Binary Large OBject) within the database. As long as we know the complex format of the data we are placing into a binary field, we can read and write the data to and from the database just as easily as we can write text and numbers.
As stated previously, traditionally GIS vendors have charged their customers a lot of money to store their map features in an enterprise database. TatukGIS gives their customers the ability to utilize layers stored in an enterprise GIS database with the purchase of either the TatukGIS Editor, the TatukGIS Developer Kernel, or the TatukGIS Internet Server. And all three of these products are quite reasonably priced.
The SQL Layer
The TatukGIS term for a map layer that is stored in an enterprise GIS database is ‘SQL Layer’. A SQL Layer is technically comprised of the following items:
1. A simple text file (with the .ttkls extension) that lists the database connection string, the layer name, the data storage type, and the database dialect. The contents of an example .ttkls file are shown below:
2. A valid connection to the database listed in the .ttkls file.
3. Once the connection is made (with the proper credentials) by the TatukGIS software, the following three tables will be created automatically in the database: ttkGisLayerSQL (lists all the SQL Layers in the database), LayerName_GEO (stores the geometry for each map feature in the SQL Layer), and LayerName_FEA (stores all attributes about each map feature in the SQL Layer).
The .ttkls text file and the database connection and information can be handled by an organization’s database administrator or IT specialist. No individual with specialized expertise in enterprise GIS database management is required. The rest of the details are handled by the TatukGIS software.
Compatible Enterprise Databases
TatukGIS software can create SQL Layers in any of the following enterprise databases:
Microsoft Access/MSJET (though not technically an ‘enterprise’ database, SQL Layers stored in an Access database provide better performance than shapefiles on the desktop when the file size exceeds 200 mb)
Microsoft SQL Server
Compatible Enterprise GIS Data Formats
TatukGIS can utilize SQL Layers within a given enterprise database in a variety of data formats, several of which are listed below:
TatukGIS Native format (optimized for speed!)
Creating SQL Layers using the TatukGIS Editor
Once you have a good understanding of how map features are stored in an enterprise GIS database and how the SQL Layer works, it is fairly easy to create a SQL Layer using the TatukGIS Editor.
Creating an Access database SQL Layer
You can create a SQL Layer in an Access database with a single command using the Editor. As stated above, for shapefiles on the desktop that are larger than 200 mb, performance can be greatly enhanced just by converting the shapefile to a MS Access SQL Layer. Fortunately the Editor can do this without MS Access. Any vector layer can be exported to an Access SQL Layer in the Editor.
Use the Layer > Export command to export a shapefile to a SQL Layer as shown in the image below.
Then just select the GIS data format within the Access database to create the SQL Layer as shown in the image below.
Creating a Firebird database SQL Layer
For best SQL Layer performance in a free enterprise GIS database, TatukGIS recommends Firebird (http://www.firebirdsql.org). (For best SQL Layer performance in a non-free enterprise GIS database, TatukGIS recommends MS SQL Server.)
Firebird is the free and fast, open-source enterprise database (formerly known as Interbase by Inprise Corp.). In addition, it is easy to install and use, especially with the FlameRobin Database Admin tool (http://www.flamerobin.org/)
To create a SQL Layer in a Firebird database first install Firebird, register a server within Firebird, create a new database on the server and test the connection. Then install a Firebird ODBC driver (such as the one on this page http://www.firebirdsql.org/index.php?op=files&id=odbc). Then optionally create a DSN using the Windows ODBC Data Source Administrator (Start > Control Panel > Administrative Tools > Data Sources(ODBC)). Finally, create the .ttkls text file. The contents of an example .ttkls text file used with a Firebird database are shown below.
The method used to create a Firebird database SQL Layer is the same as the method used to create an Access database SQL Layer. Use the Layer > Export command in the Editor. This time, however, select the .ttkls file as the export target, as shown below. If the database connection parameters are correct, then the Editor will create a new SQL Layer in the database.
To create another SQL Layer in the same database, just create another .ttkls text file with the same parameters as the previous one, but with a different layer name as shown below.
Then use the Layer > Export command in the Editor as illustrated above, but instead refer to the new .ttkls text file.
A quick look inside our Firebird database using the FlameRobin Database Admin tool shows the tables created in the database that represent the two new SQL Layers (see image below).
Creating SQL Layers in an enterprise GIS database involves a little more overhead than creating a shapefile on the desktop. Fortunately an organization’s existing database administrator or IT specialist will take care of this overhead.
Once SQL Layers are created, manipulating them is exactly the same as manipulating a shapefile on the desktop using the TatukGIS Editor.
TatukGIS has simplified the enterprise GIS database and put it within reach of almost any organization.