15.10 - Methods, Functions, and Stored Procedures - Teradata Database

Teradata Database SQL Geospatial Types

Teradata Database
Programming Reference

SQL/MM Spatial defines constructor methods and instance methods for the geospatial types. The standard also defines user-defined functions (UDFs) to support geospatial types.

To construct an instance of ST_Geometry that represents any of the supported subtypes, you can use one of several ST_Geometry constructor methods that Teradata Database implements. You can also use one of the UDFs that is defined in the SYSSPATIAL database. (For the best performance, use constructor methods instead of UDFs to construct instances of ST_Geometry.)

To perform operations on geospatial types, you can use the geospatial UDFs or the instance methods defined for ST_Geometry. (For the best performance, if an instance method is available that performs the same functionality as a UDF, use the instance method.)

The geospatial methods may be used to form geospatial predicates. For more information see “Geospatial Predicates and the Optimizer” on page 99.

Some methods are specific to the subtype that the ST_Geometry type represents. For example, some methods only operate on ST_Geometry types that represent ST_Point values.

Other methods test the spatial relationship between geospatial types. Consider the following tables, where sample_cities represents a list of cities and sample_streets represents a list of streets.

   CREATE TABLE sample_cities(
      skey INTEGER,
      cityName VARCHAR(40),
      cityShape ST_GEOMETRY);
   CREATE TABLE sample_streets(
      skey INTEGER,
      streetName VARCHAR(40),
      streetShape ST_GEOMETRY);

The following requests insert polygon values into the sample_cities table and linestring values into the sample_streets table:

   INSERT INTO sample_cities 
   VALUES(0, 'Oceanville', 'POLYGON((1 1, 1 3, 6 3, 6 0, 1 1))');
   INSERT INTO sample_cities 
   VALUES(1, 'Seaside', 'POLYGON((10 10, 10 20, 20 20, 20 15, 10 10))');
   INSERT INTO sample_streets 
   VALUES(1, 'Main Street', 'LINESTRING(2 2, 3 2, 4 1)');
   INSERT INTO sample_streets 
   VALUES(1, 'Coast Blvd', 'LINESTRING(12 12, 18 17)');

The following query uses the ST_Within method to test if any of the streets are within any of the cities:

   SELECT streetName, cityName
   FROM sample_cities, sample_streets
   WHERE streetShape.ST_Within(cityShape) = 1
   ORDER BY cityName;

The results are as follows:

streetName                               cityName
---------------------------------------- ----------------
Coast Blvd                               Seaside
Main Street                              Oceanville

Teradata Database also provides functions, methods, and stored procedures that are extensions to the SQL/MM Spatial standard. Some of the methods are defined for the MBR type and for the ST_Geometry type that represents a Geosequence value.

For details on stored procedures, see Chapter 14: “Administration.”