Example: Using the ST_Geometry Data Type When Inserting Geospatial Data into Tables - Teradata Vantage

The cities are Polygons and the streets are LineStrings, implemented with the ST_Geometry type:

CREATE TABLE test.cities (
  pkey      INTEGER,
  CityName  VARCHAR(40),
  CityShape ST_Geometry);

CREATE TABLE test.streets (
  pkey        INTEGER,
  StreetName  VARCHAR(40),
  StreetShape ST_Geometry);

Insert three rows into the cities table:

     INSERT INTO test.cities VALUES(0, 'San Diego',
                                    'POLYGON((1 1, 1 3, 6 3, 6 0, 1 1))'
                                    );

     INSERT INTO test.cities VALUES(1, 'Los Angeles',
                                    'POLYGON((10 10, 10 20, 20 20,
                                              20 15, 10 10))'
                                    );

     INSERT INTO test.cities VALUES(2, 'Chicago',
                                    'POLYGON((100 100, 100 200,
                                              300 200, 400 0, 100 100))'
                                    );

Insert three rows into the streets table:

     INSERT INTO test.streets VALUES(1, 'Lake Shore Drive',
                                     'LINESTRING(110 180, 300 150)'
                                     );

     INSERT INTO test.streets VALUES(1, 'Via Del Campo',
                                     'LINESTRING(2 2, 3 2, 4 1)'
                                     );

     INSERT INTO test.streets VALUES(1, 'Sepulveda Blvd',
                                     'LINESTRING(12 12, 18 17)'
                                     );

Join cities and streets on Streetshape.ST_Within(CityShape)=1 and select the StreetName and CityName column values from them:

     SELECT StreetName, CityName
     FROM test.cities, test.streets
     WHERE StreetShape.ST_Within(CityShape) = 1
     ORDER BY CityName;

If you use BTEQ, the result looks like this:

     StreetName                               CityName
     ---------------------------------------- ----------------
     Lake Shore Drive                         Chicago
     Sepulveda Blvd                           Los Angeles
     Via Del Campo                            San Diego