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.
Teradata Employee

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 Distance
FROM shapes P1, shapes P2
WHERE P1.id = 1 AND P2.id = 4;

Results:

Distance
7.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 Distance
FROM shapes P1, shapes P2
ORDER BY 1,2;

Results:

id id Distance
1 1 0.00
1 2 7.81
1 3 7.07
1 4 7.00
1 5 2.83
2 1 7.81
2 2 0.00
2 3 7.28
2 4 5.10
2 5 5.00
3 1 7.07
3 2 7.28
3 3 0.00
3 4 10.63
3 5 5.83
4 1 7.00
4 2 5.10
4 3 10.63
4 4 0.00
4 5 5.39
5 1 2.83
5 2 5.00
5 3 5.83
5 4 5.39
5 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:

Length
10.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 Distance
FROM shapes Point, shapes Line
WHERE Point.id <= 5
AND Line.id = 6
ORDER BY 2;

Results:

id Distance
3 1.30
2 1.57
5 2.23
4 3.14
1 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 shapes
WHERE id = 7;

Results:

id Area
7 20.00

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

SELECT id,geometry.ST_Perimeter() as Perimeter 
FROM shapes
WHERE id = 7;

Results:

id Perimeter
7 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.geometry
FROM shapes Point, shapes Poly
WHERE Poly.id = 7
AND Point.id <= 5
AND Poly.geometry.ST_Contains(Point.geometry) = 1;

Results:

id geometry
5 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 Perimeter
FROM shapes
WHERE id = 8;

Results:

id Area Perimeter
8 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 S2
where S2.id = 8
and S2.geometry.ST_Contains(S1.geometry) = 1
and S1.id != S2.id;

Results:

id geometry
5 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.geometry
from shapes S1, shapes S2
where S2.id = 8
and S2.geometry.ST_Intersects(S1.geometry) = 1
and S1.id != S2.id;

Results:

id geometry
7 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 Intersection
FROM shapes S1, shapes S2
WHERE S1.id = 7 AND S2.id = 8;

Results:

Intersection
POLYGON ((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.geometry
FROM shapes Points, shapes Poly1,shapes Poly2
WHERE Poly1.id = 7 and Poly2.id = 8
AND Points.id <=5
AND Poly1.geometry.st_intersection(Poly2.geometry).ST_Contains(Points.geometry) =1;

Results:

id geometry
5 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.geometry
FROM shapes S1, shapes Line
WHERE Line.id = 6 AND S1.id != Line.id
AND Line.geometry.st_intersects(S1.geometry) = 1;

Results:

id geometry
8 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 shapes
WHERE 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.geometry
FROM shapes S1, shapes Line
WHERE Line.id = 6 AND S1.id != Line.id
AND Line.geometry.st_buffer(3).st_intersects(S1.geometry) = 1
ORDER BY 1;

Results:

id geometry
2 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
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.

Exception in thread "main" java.lang.UnsupportedClassVersionError: com/terad
geo/TDGeoImport (Unsupported major.minor version 49.0)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
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
rt\bin\terajdbc4.jar";"c:\TdGeoImportExport\bin\tdgssconfig.jar";. com.teradata.
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
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at com.teradata.geo.TDGeoImport.main(TDGeoImport.java:869)
===========================================
Data importing failed!
Enthusiast

Re: Fun with Teradata and Geometry

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

Re: Fun with Teradata and Geometry

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!

Teradata Employee

Re: Fun with Teradata and Geometry

Hi Mike,

Do you know if it is possible to get the area of a polygon that is actually on the earth a real geospatial polygon.

I ask this because for a client I have the Shape File that contains a surface on the earth, its a state of Mexico, is it possible to calculate that area,  because I am using the funcion ST_Area and the result is '0.03' which it is obviously wrong.

Thanks for any help!