## Fun with Teradata and Geometry

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.

## Fun with Teradata and Geometry

Now that you’ve installed Teradata’s geospatial features with Teradata Express let’s roll up our sleeves and see what we can do with this. (If you haven’t setup your TD Express with Geospatial yet, see my article. I think the best way to begin exploring these features is to start is with some basic geometric shapes. Even though we call this Teradata Geospatial, we can ignore the “geo’ part for now and just play around with simple spatial objects like Points, Lines and Polygons. When we talk about Geospatial, we usually mean that we are using objects mapped to the Earth’s reference space; ie longitude and latitude. But really we are free to use any reference space that we like. Teradata by default will simply see our coordinates as references to an arbitrary grid. In the examples here, I’m using a 10x10 grid. These aren’t inches or meters or miles but just plain units.

Let’s get started by creating a new table to hold some spatial objects.

`CREATE TABLE shapes(id INTEGER NOT NULL PRIMARY KEY,geometry ST_GEOMETRY);`

A very basic table that contains an integer column for the shape’s ID and a column named ‘geometry’ for data using the new spatial ST_Geometry type. This of course is where all the magic will happen.

## Points

Now let’s add some data. Here’s a list of 5 simple Points that we want to map onto our 10x10 grid.

`INSERT INTO shapes VALUES (1,'Point(2 2)');INSERT INTO shapes VALUES (2,'Point(8 7)');INSERT INTO shapes VALUES (3,'Point(1 9)');INSERT INTO shapes VALUES (4,'Point(9 2)');INSERT INTO shapes VALUES (5,'Point(4 4)');`

As you can see, the syntax for adding spatial objects is fairly straight forward. The format used to describe spatial objects is known as WKT or Well Known Text representation. Two things to notice are the single quote around the spatial object name and coordinates and that the X and Y coordinates are separated by a space, not a comma. This will be more noticeable later when we use lines and polygons.

Now that we have these 5 points in our table, let’s play with some of the spatial functions offered by Teradata. Here’s an example showing how to calculate the distance between 2 points. The easiest data points to start with are those with IDs 1 and 4. Since both have Y coordinate values of 2, it’s pretty easy to verify the calculation (the distance between (2,2) and (9,2) better be 7 or I’ll need to go find my high school geometry book!).

`SELECT P1.geometry.ST_Distance(P2.geometry) AS DistanceFROM shapes P1, shapes P2WHERE P1.id = 1 AND P2.id = 4;Results:Distance7.00`

The SQL is pretty easy to understand. Using dot notation, we’re calling the ST_Distance() spatial method on the geometry field. We reference the shapes table twice so that we can limit the calculation to only IDs 1 and 4. And as we predicted, the distance is 7 units. Again, these are arbitrary units, not tied to inches, meters, miles or geospatial coordinates.

Here’s the same query changed slightly to calculate the distance between all points on our grid:

`SELECT P1.id, P2.id, P1.geometry.st_distance(P2.geometry) AS DistanceFROM shapes P1, shapes P2ORDER BY 1,2;Results:id id Distance1 1 0.001 2 7.811 3 7.071 4 7.001 5 2.832 1 7.812 2 0.002 3 7.282 4 5.102 5 5.003 1 7.073 2 7.283 3 0.003 4 10.633 5 5.834 1 7.004 2 5.104 3 10.634 4 0.004 5 5.395 1 2.835 2 5.005 3 5.835 4 5.395 5 0.00`

## Lines

Now that we’re comfortable with Points, let’s add a Line object to our table. Here is the syntax for adding a spatial Linestring object. Again, notice the space between the X and Y coordinates. The reason for this usage is now clearer; we need the comma to separate the pairs of coordinates for these multi-point shapes.

`INSERT INTO shapes(6,'linestring(0 8,5 6,10 5)');`

Now let’s calculate the length of our new line object (ID = 6). Again, this is a simple SQL statement using the ST_Length() method of our ST_Geometry data type, which in this case is the geometry field. This calculation measures all the line segments that make up our line string.

`SELECT geometry.ST_Length() AS Length FROM shapes WHERE id = 6;Results:Length10.48`

Now let’s have some fun with this line and our original points. What is the distance between the original 5 points in our shapes table to this line? This could model some real world example, say where we want to calculate the distance of our stores from a major highway (In this SQL I’m aliasing the shapes table to Point and Line to help clarify the SQL – I hope!):

`SELECT Point.id, Point.geometry.ST_Distance(Line.geometry) AS DistanceFROM shapes Point, shapes LineWHERE Point.id <= 5AND Line.id = 6ORDER BY 2;Results:id Distance3 1.302 1.575 2.234 3.141 4.83`

From these results we can see that while most of the points are near the line, point ID=1 is the outlier and if this was a real-world query, we could take some action based on these results.

## Polygons

Now let’s take a look at Polygons. As we all remember from class, Polygons are shapes with multiple vertices. These shapes start with basic rectangles and squares to octagons and beyond. Teradata Geospatial release 1.0 (the one we’ve installed with TD Express 12.0) can support Polygons with up to 2,500 vertices. Teradata 13.0 improves on this and will support up to a million vertices.

Creating a simple Polygon is just as easy as creating the Linestring, though please note the double set of parenthesis - this is to enable more advanced Polygons that can contain internal polygons; imagine representing a donut – the internal polygon would represent the hole.

`INSERT INTO shapes VALUES(7, 'Polygon((0 1, 0 5, 5 5, 5 1, 0 1))' );`

## Measurement Methods

Teradata Geospatial includes some very useful measurement methods, for example:

• ST_Length
• ST_Distance
• ST_Area
• ST_Permeter

Let’s try a few of these on our new Polygon (ID = 7)

`SELECT id,geometry.ST_Area() AS Area FROM shapesWHERE id = 7;Results:id Area7 20.00`

20 is correct – this Polygon is a 5x4 rectangle.

`SELECT id,geometry.ST_Perimeter() as Perimeter FROM shapesWHERE id = 7;Results:id Perimeter7 18.00`

Ah, 18 is also correct. Our 5x4 square has a perimeter of 18.

## Topology Methods

We also have a collection of other methods to analyze relationships between spatial objects.

These are known as our Topology methods and return TRUE of FALSE values represented by 0 or 1. This list includes such methods as

• ST_Contains
• ST_Intersects
• ST_Overlaps
• ST_Touches

For example, let’s find out if any of our original 5 Points are contained within this new Polygon:

`SELECT Point.id, Point.geometryFROM shapes Point, shapes PolyWHERE Poly.id = 7AND Point.id <= 5AND Poly.geometry.ST_Contains(Point.geometry) = 1;Results:id geometry5 POINT (4 4)1 POINT (2 2)`

I’ve also included the Point geometry column in the output to show its location and also to demonstrate how the ST_Geometry objects are returned from SQL queries using their WKT format. From these results we can see that Points (4,4) and (2,2) are indeed contained in our rectangle.

Now let’s add a second Polygon to the grid:

`INSERT INTO shapes VALUES(8, 'Polygon((2 3,2 9,9 9,9 6,6 3,2 3))' );`

Again, we can calculate some basic measurements on this shape:

`SELECT id,geometry.st_area() AS Area,geometry.st_perimeter() AS PerimeterFROM shapesWHERE id = 8;Results:id Area Perimeter8 37.50 24.24`

And again, let’s find any other shapes that are contained within the Polygon (ID = 8):

`SELECT S1.id, S1.geometry from shapes S1, shapes S2where S2.id = 8and S2.geometry.ST_Contains(S1.geometry) = 1and S1.id != S2.id;Results:id geometry5 POINT (4 4)2 POINT (8 7)`

Here’s a query to test whether any objects intersect this Polygon. In addition to any Points that are within the Polygon, we should expect to also see the first Polygon as well as our Linestring in these results:

`SELECT S1.id, S1.geometryfrom shapes S1, shapes S2where S2.id = 8and S2.geometry.ST_Intersects(S1.geometry) = 1and S1.id != S2.id;Results:id geometry7 POLYGON ((0 1,0 5,5 5,5 1,0 1))5 POINT (4 4)6 LINESTRING (0 8,5 6,10 5)2 POINT (8 7)`

Sure enough, the results still show Points 5 and 2 as being in the new Polygon, but also now the first Polygon and Linestring are returned as well. Those are not “contained” in this Polygon, but they do intersect it. And again, notice the “=1” comparison to the ST_Intersects() method to filter on the results, as in True, these objects intersect.

## Spatial Methods

Now let’s look at another group of spatial methods, the ones that return new spatial objects. These methods include

• ST_Buffer
• ST_Boundary
• ST_Transform

Here’s an example using ST_Intersection(), which will return a new spatial object representing the intersection of our 2 Polygons:

`SELECT S1.geometry.st_intersection(S2.geometry) AS IntersectionFROM shapes S1, shapes S2WHERE S1.id = 7 AND S2.id = 8;Results:IntersectionPOLYGON ((2 5,5 5,5 3,2 3,2 5))`

Pretty cool, huh? And we can use our spatial methods on this new object too. For example, let’s look for Points that contained in both Polygons. This would answer the question “are there any Points in this intersection object?”

`SELECT Points.id, Points.geometryFROM shapes Points, shapes Poly1,shapes Poly2WHERE Poly1.id = 7 and Poly2.id = 8AND Points.id <=5AND Poly1.geometry.st_intersection(Poly2.geometry).ST_Contains(Points.geometry) =1;Results:id geometry5 POINT (4 4)`

And we see that we do have one point contained in this intersection.

There’s almost a limitless amount of fun that we can have with Teradata and these geometric shapes. Let’s close this out with 2 more examples using the Linestring. First, let’s see which objects are in its path using the ST_Intersects method. In a real world example, this could be a query to find which towns this road passes through.

`SELECT S1.id, S1.geometryFROM shapes S1, shapes LineWHERE Line.id = 6 AND S1.id != Line.idAND Line.geometry.st_intersects(S1.geometry) = 1;Results:id geometry8 POLYGON ((2 3,2 9,9 9,9 6,6 3,2 3))`

OK, that was easy. Our line passes through Polygon ID = 8. But now let’s take a look at the ST_Buffer() method. ST_Buffer() is a spatial method that returns a new spatial object. It will create a new Polygon that is X units larger than the original spatial object in all directions. This could be a circular buffer around a single Point or a new larger Polygon around an existing Polygon or a new Polygon around a Linestring.

We’ll use the Linestring (ID=6) for our example. A real world example could use this to define a flood zone area around a river; or a buffer zone to mark the area impacted by the path of a hurricane.

`SELECT geometry.ST_Buffer(3) AS Buffer FROM shapesWHERE id = 6;Results:geometry.ST_BUFFER(3)POLYGON ((5.858436 8.887725,10.588348 7.941742,10.588348 7.941742,10.741501 7.906919,10.892621 7.864128,11.041295 7.813486,          11.187114 7.755133,11.32968 7.689229,11.468601 7.615953,11.603497 7.535508,11.733997 7.448112,11.859745 7.354007,          11.980396 7.253449,12.095618 7.146715,12.205096 7.034097,12.308531 6.915903,12.405638 6.792458,12.496151 6.664101,          12.579822 6.531182,12.656423 6.394066,12.725742 6.253129,12.78759 6.108757,12.841798 5.961346,12.888216 5.811301,          12.926718 5.659031,12.957198 5.504956,12.979573 5.349496,12.99378 5.193078,12.999782 5.036131,12.997562 4.879085,          12.987126 4.72237,12.968502 4.566417,12.941742 4.411652,12.906919 4.258499,12.864128 4.107379,12.813486 3.958705,          12.755133 3.812886,12.689229 3.67032,12.615953 3.531399,12.535508 3.396503,12.448112 3.266003,12.354007 3.140255,          12.253449 3.019604,12.146715 2.904382,12.034097 2.794904,11.915903 2.691469,11.792458 2.594362,11.664101 2.503849,          11.531182 2.420178,11.394066 2.343577,11.253129 2.274258,11.108757 2.21241,10.961346 2.158202,10.811301 2.111784,          10.659031 2.073282,10.504956 2.042802,10.349496 2.020427,10.193078 2.00622,10.036131 2.000218,9.879085 2.002438,          9.72237 2.012874,9.566417 2.031498,9.411652 2.058258,9.411652 2.058258,4.411652 3.058258,4.233304 3.099625,          4.057811 3.151793,3.885828 3.21457,-1.114172 5.21457,-1.114172 5.21457,-1.258423 5.276699,-1.399225 5.346291,          -1.536192 5.423158,-1.668948 5.507088,-1.79713 5.59785,-1.920386 5.695197,-2.038378 5.798861,-2.150783 5.908558,          -2.257293 6.023987,-2.357616 6.144833,-2.451477 6.270763,-2.538619 6.401434,-2.618802 6.536485,-2.691808 6.675549,          -2.757435 6.818242,-2.815505 6.964175,-2.865857 7.112946,-2.908354 7.264149,-2.94288 7.417369,-2.969339 7.572186,          -2.98766 7.728176,-2.997792 7.88491,-2.999707 8.04196,-2.9934 8.198895,-2.978888 8.355285,-2.956211 8.510701,          -2.925432 8.664717,-2.886634 8.816912,-2.839924 8.966867,-2.78543 9.114172,-2.723301 9.258423,-2.653709 9.399225,          -2.576842 9.536192,-2.492912 9.668948,-2.40215 9.79713,-2.304803 9.920386,-2.201139 10.038378,-2.091442 10.150783,          -1.976013 10.257293,-1.855167 10.357616,-1.729237 10.451477,-1.598566 10.538619,-1.463515 10.618802,-1.324451 10.691808,          -1.181758 10.757435,-1.035825 10.815505,-0.887054 10.865857,-0.735851 10.908354,-0.582631 10.94288,-0.427814 10.969339,          -0.271824 10.98766,-0.11509 10.997792,0.04196 10.999707,0.198895 10.9934,0.355285 10.978888,0.510701 10.956211,           0.664717 10.925432,0.816912 10.886634,0.966867 10.839924,1.114172 10.78543,1.114172 10.78543,5.858436 8.887725))`

That’s a pretty detailed Polygon that we calculated. Once again, pretty cool!

And finally, let’s get real fancy. Let’s take this new buffer Polygon and see how many of our objects lie in its path:

`SELECT S1.id, S1.geometryFROM shapes S1, shapes LineWHERE Line.id = 6 AND S1.id != Line.idAND Line.geometry.st_buffer(3).st_intersects(S1.geometry) = 1ORDER BY 1;Results:id geometry2 POINT (8 7)3 POINT (1 9)5 POINT (4 4)7 POLYGON ((1 1,1 5,5 5,5 1,1 1))8 POLYGON ((2 3,2 9,9 9,9 6,6 3,2 3))`

Almost all of them - let’s hope this isn’t a Category 5 hurricane!

## Conclusion

I hope you’ve enjoyed this quick pass through Teradata’s Spatial features. In my next article we’ll take a dive into the geo side of Geospatial and use some real world examples. In the meanwhile, continue exploring these spatial features. Here’s a more complete list of the available methods in each category for you to explore:

### Measurement Methods

• ST_Area
• ST_Distance
• ST_SphericalDistance
• ST_SpheroidalDistance
• ST_Perimeter
• ST_Length

### Spatial Methods

• ST_Buffer
• ST_Intersection
• ST_Boundary
• ST_Difference
• ST_Envelope
• ST_ExteriorRing
• ST_GeometryN
• ST_InteriorRingN
• ST_Transform

### Topology Methods

• ST_Intersects
• ST_Overlaps
• ST_Relate
• ST_Touches
• ST_Within
• ST_Contains
• ST_Disjoint
• ST_Crosses
• ST_Equals

### Attribute Methods

• ST_AsBinary
• ST_AsText
• ST_CoordDim
• ST_Dimension
• ST_GeometryType
• ST_IsEmpty
• ST_IsSimple
• ST_IsClosed
• ST_NumPoints
• ST_SRID
Tags (6)
6 REPLIES
Enthusiast

## Re: Fun with Teradata and Geometry

I am trying to load a shapefile in ST_GEOMETRY but getting a Java error. I even tried to fix the classpath but still not able to run the import. Here is my error message. Any help will be appreciated.

geo/TDGeoImport (Unsupported major.minor version 49.0)
at java.security.AccessController.doPrivileged(Native Method)
Enthusiast

## Re: Fun with Teradata and Geometry

OK! one of my office colleagues fixed the issue but both of us are running into the same issue. As below, any feedback will be helpful. I understand that I did not provide the correct database, file, etc. But I just wanted to run and see if the import apps works.

C:\TdGeoImportExport\bin>java -Xms256m -Xmx512m -classpath .;"c:\TdGeoImportExpo
geo.TDGeoImport -l geo_system/geo_user,geo_pw -s mydb
java.lang.UnsatisfiedLinkError: C:\TdGeoImportExport\bin\geojni.dll: This applic
ation has failed to start because the application configuration is incorrect. Re
installing the application may fix this problem
===========================================
Data importing failed!
Enthusiast

## Re: Fun with Teradata and Geometry

Worked out fine. It was issue with the classpath.
Enthusiast

Good one
Enthusiast

## Re: Fun with Teradata and Geometry

I recently got access to use the geospatial functions in Teradata 13.0, but now i'm getting errors & I'm wondering if you could help -- or point me in the right direction. I searched the web & didn't find anything, so i'm posting it here so that if anyone else encounters the problem - hopefully hey can just google & have the answer handy... (Assuming that you know the answer)

I'm trying to execute this:

CREATE VOLATILE TABLE mygeo (
id INTEGER,
latlon ST_Geometry
)
NO PRIMARY INDEX;

The error I get is this:

7552: Unable to create UDT Instance SYSUDTLIB.ST_GEOMETRY: the library for SYSUDTLIB rev 9 not found - Check message log for possible 7820 errors in library synchronization.

Where is the message log? And is this a permissions issue? or is it something that needs to be installed. The orange book indicates that everything that is needed should be installed, so I really need help figuring out what needs to happen to make it work.

Thanks!