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

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
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