Since Teradata first introduced native Geospatial capabilities in the database there have been inquiries about how to interoperate with the spatial data via client GIS and visualization tools.  In this article we are going to discuss how to view and edit spatial features and attributes maintained within Teradata in real-time by using the GeoServer web services.

Teradata Geospatial and GeoServer

The Teradata database currently complies with the Open Geospatial Consortium (OGC), Simple Features – SQL – Types and Functions 1.1 (SFS(TF) 1.1) standard.  Since Teradata complies with the OGC standards, customers can integrate with other OGC compliant interfaces, web services, and technologies that adhere to the standard as well.  GeoServer (www.geoserver.org) is an open source software server written in Java that allows users to share and edit geospatial data.  Designed for interoperability, it publishes spatial data from the Teradata database using these open standards.  GeoServer publishes maps and data in standard protocols devised by the OGC.  The most common of these protocols are the Web Map Service (WMS) and Web Feature Service (WFS).

The open source GeoServer project has recently added support for Teradata Geospatial, which will allow for full read and write capabilities including the viewing, editing, and deleting of spatial features in the database.  Now GIS and visualization tools can interoperate with Teradata spatial data in real-time via the GeoServer web services.  The Teradata extension for GeoServer has also been optimized and customized for Teradata including the use of Querybanding and Tessellation support.

GeoServer and Teradata Configuration Documentation

The GeoServer and Teradata documentation provides detailed instructions about how to download, install, and configure GeoServer with Teradata.  It also contains Teradata database considerations, configurations, and set-up instructions, including information about setting up tessellation index tables for optimal performance.  The documentation also covers how to connect popular visualization tools such as Google Earth and uDig to GeoServer to view and edit features in Teradata.

Download GeoServer and Teradata - Your Geospatial Data Served on the Web (.PDF, 5.2MB)

This document has been developed in conjunction with OpenGeo (http://www.opengeo.org), which is one of the primary contributors to the GeoServer project, and provides enterprise support, services, and training.

The sample shapefile data referenced in the document can be found here: http://files.opengeo.org/states.zip

User Manuals and Download Sites

Information about the Teradata extension for GeoServer and connection instructions can be found at:

Information about the Teradata extension and connection instructions for uDig, the open source desktop GIS tool, can be found at:

Interoperating with Other GIS and Visualization Tools

Once the data is served in GeoServer, connecting to it means to connect to WMS and WFS services.  The following document points to online resources for instructions on how to connect various GIS and visualization tools via these OGC services, including esri ArcGIS for Desktop and Explorer, MapInfo Professional, AutoDesk MapGuide Studio and Map Server, uDig, QGIS, Google Earth, and gvSIG.

 

Discussion
ND_Siouxfan 5 comments Joined 05/09
25 Jul 2011

Great stuff Matt! Works like a charm!

overman 2 comments Joined 05/09
26 Jul 2011

It's a positive progress to visualize geospatial data in teradata, it helps to promote teradata PS offers about GIS application. one more question, when ArcGIS will support Teradata directly

mzenus 4 comments Joined 05/09
26 Jul 2011

According to esri, there are no plans to make Teradata an ArcSDE Geodatabase. However, we do believe that esri is working on a direct connection capability via their Query Layers capability (http://blogs.esri.com/Dev/blogs/geodatabase/archive/2010/04/14/Query-Layers-_4000_-ArcGIS-10.aspx) . Query Layers were added to ArcGIS 10. Although not committed to support Teradata yet, we hope it will make it in the next release (arcGIS 10.1 - Q1 2012).

overman 2 comments Joined 05/09
10 Aug 2011

Hi,mzenus,while using Tdgeoimportexport tools to import ESRI shp files into Teradata, The Chinese character encoding cannot support well, all Chinese strings are translated into wrong characters like '???????', any suggestions ?

mzenus 4 comments Joined 05/09
10 Aug 2011

TDGeoImport is not required for GeoServer interoperability. However, I did ask the engineers and they don't think TDGeoImport can handle this encoding. TDGeoImport is based on GDAL open source project, which doesn't seem to fully support Unicode (so not Chinese encoding). We have no updates on when they will either.

They recommended to try the following:
1) create the target database as UNICODE, and
2) instead of using Chinese Encoding, import the Shapefile in Unicode or UTF-8

ND_Siouxfan 5 comments Joined 05/09
11 Aug 2011

RE: Indexes. Does the table need to be created in the SYSPATIAL database?

mzenus 4 comments Joined 05/09
11 Aug 2011

Yes. In order to process a spatial index, you will need to create a tessellation table in the SYSSPATIAL database, the database responsible for holding all spatial functions, and then create an index table for your data sets.

CREATE SET TABLE SYSSPATIAL.tessellation, NO FALLBACK,
NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT
(
F_TABLE_SCHEMA VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
F_TABLE_NAME VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
F_GEOMETRY_COLUMN VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
U_XMIN FLOAT,
U_YMIN FLOAT,
U_XMAX FLOAT,
U_YMAX FLOAT,
G_NX INTEGER,
G_NY INTEGER,
LEVELS INTEGER,
SCALE FLOAT,
SHIFT INTEGER
)
UNIQUE PRIMARY INDEX ( F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN );

You will then create an index table for each layer (table/view) in the database w/ your layer data with the following naming convention:

[TABLE_NAME]_[GEOMETRY_COLUMN]_idx

See "Setting a Spatial Index" (page 10) in the GeoServer-Teradata manual attached to this article. It has the details about how to set this up.

Note the details about case sensitivity of the table names and geometry columns in these index tables.

ND_Siouxfan 5 comments Joined 05/09
26 Aug 2011

Yeah.... the case sensitivity part. I caught that while looking at DQBL and the SQL. Once I caught it this works great!!!

TarunAgarwal 1 comment Joined 10/11
08 Dec 2011

Can you please help me with the loading of MapInfo file to Teradata 13 using TDGeoImport.
TDGeoImport is giving error while trying to upload map from MapInfo Tab format:

C:\Installers\Geospatial\bin>java -Xms256m -Xmx512m -classpath .;"C:\Installers\
Geospatial\JDBC\Teradata 13\TeraJDBC__indep_indep.13.00.00.27\terajdbc4.jar";"C:
\Installers\Geospatial\JDBC\Teradata 13\TeraJDBC__indep_indep.13.00.00.27\tdgssj
ava.jar";"c:\Program Files\Teradata\Tdat\LTDBMS\bin\tdgssconfig.jar";. com.terad
ata.geo.TDGeoImport -l localhost/tduser,tduser -s tduser -f C:\MC\Geospatial -n
WI_Load_11032011
Logon = localhost/tduser,tduser
DatabaseName = tduser
Data Source = C:\MC\Geospatial
LayerName = WI_Load_11032011
connecting to localhost ... connected!
Validating layer: WI_Load_11032011
All layers successfully validated.

-------------------------------------------
Importing layer WI_Load_11032011 to table WI_LOAD_11032011... java.sql.BatchUpda
teException: [Teradata JDBC Driver] [TeraJDBC 13.00.00.27] [Error 1154] [SQLStat
e HY000] A failure occurred while inserting the batch of rows destined for datab
ase table "TDUSER"."TDGEOFLoadTbl_1". Details of the failure can be found in the
exception chain that is accessible with getNextException.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(E
rrorFactory.java:146)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(E
rrorFactory.java:130)
at com.teradata.jdbc.jdbc.fastload.FastLoadManagerPreparedStatement.exec
uteBatch(FastLoadManagerPreparedStatement.java:2173)
at com.teradata.geo.TDGeoImport.LayerToData(TDGeoImport.java:776)
at com.teradata.geo.TDGeoImport.ProcessLayer(TDGeoImport.java:395)
at com.teradata.geo.TDGeoImport.importAllData(TDGeoImport.java:931)
at com.teradata.geo.TDGeoImport.main(TDGeoImport.java:1185)
Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [
TeraJDBC 13.00.00.27] [Error 1148] [SQLState HY000] The next failure(s) in the e
xception chain occurred while ending FastLoad of database table "TDUSER"."TDGEOF
LoadTbl_1"
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(Er
rorFactory.java:92)
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(Er
rorFactory.java:67)
at com.teradata.jdbc.jdbc.fastload.FastLoadManagerPreparedStatement.endF
astLoad(FastLoadManagerPreparedStatement.java:872)
at com.teradata.jdbc.jdbc.fastload.FastLoadManagerPreparedStatement.exec
uteBatch(FastLoadManagerPreparedStatement.java:2156)
... 4 more
java.io.IOException: LayerToData failed
at com.teradata.geo.TDGeoImport.LayerToData(TDGeoImport.java:837)
at com.teradata.geo.TDGeoImport.ProcessLayer(TDGeoImport.java:395)
at com.teradata.geo.TDGeoImport.importAllData(TDGeoImport.java:931)
at com.teradata.geo.TDGeoImport.main(TDGeoImport.java:1185)
Importing data failed.
===========================================
Data importing failed!

Flamboyant4u 1 comment Joined 01/12
16 Jan 2012

Informative.. ThanQ :)

YSS

gerardo 1 comment Joined 10/09
09 Feb 2012

It works!!!!

sg186010 1 comment Joined 06/12
17 Oct 2012

Hi,

Can you post what you did to get the import working. I'm getting the exact same error when trying to load the third layer in an esri shape file.

rbzikha 1 comment Joined 10/13
12 Nov 2013

Thanks for this article is very useful for Geo business intelligence developer :)

24 Apr 2015

Hi,
Question. Using GeoServer, instead of a point can I use a company logo and also provide more information for that point (#contract, $investment, etc.)? Also this applies for a polygon.
What about doing it in Tableau?
What is your recommendation?
If you work in TD please send me a mail to luis.guerrero@teradata.com so I can provide more info about what we want to do.
 
Regards
Luis

You must sign in to leave a comment.