What can I do with Teradata’s Geospatial Support?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

What can I do with Teradata’s Geospatial Support?

Maybe your company is thinking of opening a new branch office if there are enough customers who live within the target area. Or maybe you want to let your customers know which of your stores they are closest to. Maybe you want to investigate the routes your delivery trucks take.

If it’s time for you to start understanding your organization’s location data in a different way, there’s a lot you can do with Teradata’s geospatial support.

How to Represent Your Data as Geospatial Information

Geospatial information identifies the geographic location of features and boundaries on the planet. Release 13.0 supports geospatial data types that you can use to represent customers or stores as points, delivery truck routes as lines, and demographic regions as polygons. (Earlier releases provide geospatial support as an extension, available from the Teradata Download Center.)

If you currently represent your location data using addresses (streets, cities, states, zip codes), you’ll have to geocode your data, converting it into latitude and longitude coordinates for example. (Several publicly available tools are available to do this.)

To store your geocoded data, use a table that has an ST_GEOMETRY type column. The ST_GEOMETRY type can represent a variety of shapes including points, lines, and polygons. Here’s an example of a simple table for storing customer location data:

CREATE TABLE sample_customers(
custKey INTEGER,
custName VARCHAR(40),
custShape ST_GEOMETRY)
PRIMARY INDEX (custKey);

Teradata Database implements the ST_GEOMETRY type as a proprietary internal user-defined type (UDT).

To load your geocoded data into an ST_GEOMETRY type column, you can use an INSERT statement that includes a character literal representing the data in a well-known text format. Each shape that ST_GEOMETRY can represent has its own well-known text format. For example, the following statements use the well-known text format for points to insert point values into the sample_customers table:

INSERT INTO sample_customers
VALUES(100001, 'Ouellet', 'POINT(-118.395849 33.916485)');

INSERT INTO sample_customers
VALUES(100002, 'Hax', 'POINT(-118.395741 33.918111)');

INSERT INTO sample_customers
VALUES(100003, 'Lopez', 'POINT(-118.395749 33.918613)');

If you already have geographic data sets, for example TIGER/Line® files from the U.S. Census Bureau, you can use a data loading tool called TDGeoImport that Teradata provides. TDGeoImport efficiently converts the data into a format that is compatible with the Teradata geospatial UDTs.

Testing Spatial Relationships

Implementing the ST_GEOMETRY type as an internal UDT allows Teradata to define methods that perform geometric calculations and test for spatial relationships between two geospatial values. For example, there is a method to test if a value spatially intersects another value (Do these two streets intersect?) and another method to test if a value is spatially within another value (Is this customer within this demographic region?).

Consider the sample_customers table from the preceding section and another similar table called sample_branches where the branchShape column stores point values that represent the locations of your branch offices.

CREATE TABLE sample_branches(
branchID INTEGER,
branchName VARCHAR(40),
branchShape ST_GEOMETRY)
PRIMARY INDEX (branchID);

To find the nearest branch office to each customer, you can use the ST_SphericalDistance method, which calculates the spherical distance between two spherical points on the planet.

SELECT custName || ' is closest to ' || branchName
FROM (
SELECT custName,
MIN(custShape.ST_SphericalDistance(branchShape))
FROM sample_customers, sample_branches
GROUP BY custName) AS nearest (minName, minDistance),
sample_customers, sample_branches
WHERE custShape.ST_SphericalDistance(branchShape) =
minDistance
AND custName = minName;

In the query, the derived table groups the shortest distance to a store by the customer name. When the outer WHERE condition is satisfied, you know the name of the store that is closest to the customer.

Viewing Geospatial Data

If your geospatial data consists of points and lines where the coordinates are longitude and latitude, you can use the Google Maps visualization tool, a free offering from Google, to display your location data. The Google Maps API lets you embed Google Maps in your web pages with Javascript.

If you have polygons or non-latitude, non-longitude data, you can use MapServer, an open source web mapping development environment developed at the University of Minnesota. MapServer interfaces with Teradata via a plug in dll called MSTDPlugin.dll, available from the Teradata Download Center.

Teradata Extras

The Teradata Database geospatial implementation closely follows ISO/IEC 13249-3, Information technology — Database languages — SQL Multimedia and Application Packages — Part 3: Spatial, referred to as SQL/MM Spatial.

In addition to the SQL/MM Spatial shapes that ST_GEOMETRY can represent is a shape called GeoSequence, an extension of a line that can contain tracking information, such as time stamps, in addition to geospatial information. If you are analyzing a delivery truck route, for example, you could use a GeoSequence to represent stops along the route and time stamps for each stop.

Teradata also supports another proprietary internal UDF called MBR that provides a way to obtain the minimum bounding rectangle (MBR) of a geometry for tessellation purposes. Tessellation, another Teradata extra, is a technique where the object universe is described as a grid with a specific grid size. The tiles within the grid are referred to as cells. Grids can be multilevel, with each level a finer granularity.

To use tessellation, you convert geospatial objects that you want to compare into MBRs and place the MBRs within the grid. Instead of comparing every object, a potentially costly operation, you can narrow the comparison to objects that lie within the same grid cells.

Additional Information on Geospatial Support

The following documentation provides more information about geospatial support:

  • SQL Geospatial Types, B035-1181-098A, available from http://www.info.teradata.com
  • Teradata Spatial Release 13.0 User’s Guide, Teradata Orange Book 541-0007514, available when you register through Teradata @ Your Service from the Support Services link at http://www.teradata.com
Tags (2)
3 REPLIES
N/A

Re: What can I do with Teradata’s Geospatial Support?

Thanks alot for this great article. I was wondering though if there was more examples on spatial relationships?
Enthusiast

Re: What can I do with Teradata’s Geospatial Support?

Is the MapServer integration still supported through the MSTDPlugin.dll? I'd like to evaluate the integration but I'm unable to locate the file on the download center any more.
Enthusiast

Re: What can I do with Teradata’s Geospatial Support?

Can anyone please point me to any documentation on integrating Teradata with MapServer?

I see the mentioned dll MSTDPlugin.dll, however when do I get this from and use it?

Thanks!