17.10 - Methods Specific to the Subtype the ST_Geometry Type Represents - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Geospatial Data Types

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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;

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