Example: Using the ST_Geometry Data Type When Inserting Geospatial Data Into Tables - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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