Adding Geospatial Features to Teradata Express 12.0

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

Adding Geospatial Features to Teradata Express 12.0

We’re in the midst of an explosion in the world of location data. GPS tools have greatly expanded our ability to capture location data and mapping tools have broadly expanded its use. We have Web navigation sites like Google Earth and Yahoo Maps. Our cars have navigation devices to keep us from getting lost. Companies use GPS devices to track their shipments and mobile assets. Our digital cameras add location tags to photos. And we even have dog collars that can track Fido’s adventures around the neighborhood!

Teradata has responded by adding geospatial features to its database.  While these will be included as core features in Teradata 13.0, we do have a geospatial package that can be installed with TD 12.0, 6.2 and 6.1.  Combining this with the freely available Teradata Express 12.0 database provides an excellent platform to begin using these new features in your application development today.

This article will guide you through the steps of installing the Teradata’s geospatial components to your Teradata Express 12.0 development environment. It is the first in a series on geospatial. More articles will follow to guide you on your way to becoming a Teradata geospatial rock star!

Installation Steps

Step 1. Download the Geospatial Package

 

Download the Teradata Geospatial Package from http://developer.teradata.com/download/extensibility/teradata-geospatial-release-1-4

Once the download is complete, unzip to a temporary directory.  For this guide I’ve chosen c:\temp\tdgeo and will reference this location during the installation steps.

Step 2. System Prerequisites

Teradata Express 12.0 is distributed for Microsoft Windows at http://www.teradata.com/express. You can also find extensive instructions for installing Teradata Express in this article.

Software dependencies

The installation is performed using Perl scripts, so you’ll need to have Perl installed.

The installation scripts also have dependencies on some of the Teradata client software that is included with TD Express and should already be on your system:

  • BTEQ SQL query tool.
  • Fastload for windows, used to load the spatial reference system definitions.
  • Multiload for windows or Linux, used by TDGeoImport.

And finally, the installation script compiles the geospatial C functions (UDFs) and needs an installed compiler. I have Microsoft’s Visual Studio installed on my development computer.

Security Configuration on Windows Server 2003/2008

This step is not required for Windows XP, but if you are installing on Windows Server (2003 or 2008), the installation script may require a permission configuration change on your Windows server.  The tdatuser account must have read and execute privilege on cmd.exe.

Configure this permission by doing the following:

- With Windows Explorer, right click the c:\windows\system32\cmd.exe file.

- Select the Security tab.

- Select "Add"

- Enter tdatuser in the object name text box and click ok.

- Look in the lower panel and ensure that Read & Execute should have a check mark in the Allow column. If not, check this option and click the OK button.

 

If you have trouble adding the tdatuser, be sure the Location field is set to your local PC, not your domain. Click the Locations button and scroll to the top of the page to select your local computer. This should now allow you to add the tdatuser. If you still have trouble, verify that the tdauser is indeed a user on your system using the Control Panel -> Users tool. (This user should have been installed during your TD Express setup).

Step 3. Run the Script

The main installation script is in the \install\bin\perl in the folder we’ve unzipped the Teradata package. In this example, that is c:\temp\tdgeos\install\bin\perl and the install script is installgeos.pl.

Invoke this from a command window with the syntax: perl installgeos.pl and chose option (1) from the main menu.

 

A few basic inputs will be required; ie database server name or address and administrator username and password. The script will provide default values that should generally work for Teradata Express.

For the first input, the server name for the standard Teradata Express database is localtd.

All the other inputs default to the standard installation values. If you have changed the password for the dbc user, you’ll need to enter the new password.

Step 4. Validating the Geospatial Installation

The geospatial package basically consists of a new data type for storing spatial data and new functions to act on this new data type.

The spatial data type is ST_Geometry. Here is the SQL for verifying that is has installed correctly in your database:

SELECT TypeName 
FROM DBC.UDTINFO
WHERE TypeName = 'ST_Geometry';

*** Query completed. One row found. One column returned.

*** Total elapsed time was 1 second.

TypeName
---------------------------------------------------------------------------
ST_GEOMETRY

Step 5.  Granting Permissions to use Geospatial

After the geospatial UDTs and UDFs are installed, some standard database permissions need to be granted to the database users in order to allow them to use these features.  In these examples, I am granting these permissions to my database user tduser.  Remember to login first as the database administrator (dbc) before executing these SQL statements.

GRANT UDTUSAGE ON SYSUDTLIB TO tduser;
GRANT EXECUTE FUNCTION ON SYSSPATIAL TO tduser;
GRANT SELECT ON SYSSPATIAL TO tduser;
GRANT EXECUTE PROCEDURE ON SYSSPATIAL to tduser;

The installation script also produces a log file that is a good resource for viewing some spatial query examples: ./install/log/validate.out. In this file you’ll find some good examples of creating and querying spatial data.

The distribution package also includes a document Geospatial Orange Book detailing Teradata’s geospatial solution. In this example, that guide is located in C:\temp\tdgeo\install\doc. This is a great reference for understanding our features with many SQL usage examples.

Conclusion

We’ve now successfully added Teradata’s geospatial package to our TD Express environment and the world (pardon the pun) of geospatial data and functions is now waiting for us to begin exploring. In my next article, we’ll learn how to create spatial data and get an introduction to the powerful functions that are now available within Teradata.  This article is available at http://developer.teradata.com/extensibility/articles/fun-with-teradata-and-geometry.

6 REPLIES
FAQ
Enthusiast

Re: Adding Geospatial Features to Teradata Express 12.0

I am facing this error

.COMPILE FILE ='C:/.../geospatial_1_4/install/inst/installsp.spl';
*** Error 5547 Internal Error: Can not find mt.exe.
Statement# 2592, Info =0
*** Total elapsed time was 1 second.

What could be the reason behind this?
FAQ
Enthusiast

Re: Adding Geospatial Features to Teradata Express 12.0

The above problem that I mentioned is due to a known bug in v13; you need to upgrade to 13.1 to fix it!
Enthusiast

Re: Adding Geospatial Features to Teradata Express 12.0

Great Article.Need some help though. I have downloaded TD12 & perl for Win and configured them so that my utitilies and basic queries are working.I have downloaded the GeoSpatial package as mentioned above and trying to configure it but I am getting the following error -->(No Compiler available)

RETURNS TABLE(c1 VARCHAR(300))
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!installpkg_t!C:/test/geospatial_1_4/install/inst/ installpkg.c!F!installpkg'
PARAMETER STYLE SQL;
*** Failure 5600 Error creating UDF/XSP/UDM/UDT: no compiler available.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

If you can please help where can be the possible issue -

Regards
Shobhit
Teradata Employee

Re: Adding Geospatial Features to Teradata Express 12.0

Shobhit,

To install the Teradata 12 Geospatial UDFs, you will need to have a C compiler on your PC. Install the Microsoft Visual Studio and then re-run the installation scripts.

The alternative is to use Teradata 13 (or 13.10). The Geospatial features are now native to Teradata and do not require this separate installation step.

Mike

Enthusiast

Re: Adding Geospatial Features to Teradata Express 12.0

Hi Mike,

Thanks a lot.I installed the Visual Studio C compiler and tried out few of your examples.Its working fine. Thanks again.

Regards
Shobhit

Re: Adding Geospatial Features to Teradata Express 12.0

The link for the download takes me to a page where information is provided about the download, but there is no file or package that can be downloaded. Is there another source for downloading the code needed to add geospatial to TD 12? Thank you.