TatukGIS Consulting

Brian Brown, MS, MS
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:

[TatukGIS Layer]

ADO=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Layers.mdb”

Dialect=MSJET

Layer=COUNTIES

Storage=Native

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

Interbase

Firebird

MySQL

DB2

Sybase

Oracle

Progress

Informix

Advantage

Sap DB

PostgreSQL

Flash Filer

NexusDB

BlackFish SQL

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

ArcSDE

Oracle Spatial

OpenGIS WKB

OpenGIS WKT

OpenGIS Katmai

Katmai

PostGIS

GeoMedia

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.

[TatukGIS Layer]

Storage=Native

Dialect=INTERBASE

ADO=”DSN=Firebird;UID=ADMIN;PWD=PASSWORD;DBNAME=ENTERPRISE_GIS.FDB”

Layer=COUNTIES

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.

[TatukGIS Layer]

Storage=Native

Dialect=INTERBASE

ADO=”DSN=Firebird;UID=ADMIN;PWD=PASSWORD;DBNAME=ENTERPRISE_GIS.FDB”

Layer=SOUTH_AMERICA

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

Conclusion

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.

 

∞ TatukGIS Consulting

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Topcon
softmouse 3D
CADalog.com - Countless CAD add-ons, plug-ins and more.
HPSA Designations and GIS Manager



Click here for Internet Business Systems © 2014 Internet Business Systems, Inc.
595 Millich Dr., Suite 210, Campbell, CA 95008
+1 (408) 850-9202 — Contact Us, or visit our other sites:
TechJobsCafe - Technical Jobs and ResumesEDACafe - Electronic Design AutomationGISCafe - Geographical Information Services	MCADCafe - Mechanical Design and EngineeringShareCG  - Share Computer Graphic (CG) Animation, 3D Art and 3D Models
  Privacy Policy