17.05 - Example: Using the ST_Geometry Data Type When Inserting Geospatial Data Into Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

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

This is a more detailed example of using the ST_Geometry data type to represent other geospatial data types when inserting geospatial data into tables. Create two tables in the test database: cities, which represents a list of cities and streets, which represents a list of streets. The cities are Polygons and the streets are LineStrings, and both are implemented using the ST_Geometry type as you can see from the table definitions for cities and streets. Insert some cities and streets into these tables using the well-known text format for these geospatial types and then submit a query to see if any of the streets are within any of the cities.

Here are the table definitions:

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

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

First 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))'

Then 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)'

Now 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