Methods Specific to the Subtype the ST_Geometry Type Represents - Analytics Database - Teradata Vantage

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;

Output:

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