17.05 - Example: Using the ST_Geometry Data Type to Represent Other Geospatial Data Types for INSERTs - 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)

You can use the ST_Geometry data type to represent implicitly all of the geospatial data types defined by the ANSI SQL:2011 standard, as indicated by the following set of examples. See Teradata Vantage™ - Geospatial Data Types , B035-1181 . Note that in every case, the geospatial values are actually stored using the ST_Geometry data type, not the type specified in the INSERT request.

The POINT type has a 0-dimensional geometry and represents a single location in two-dimensional coordinate space.

The following example inserts such a point into table tab1:

     INSERT INTO tab1 VALUES (0, 'POINT(10 20)');

The LINESTRING type has a 1-dimensional geometry and is usually stored as a sequence of points with a linear interpolation between the individual points.

The following example inserts such a point sequence into table tab1:

     INSERT INTO tab1 VALUES (0, 'LINESTRING(1 1, 2 2, 3 3, 4 4)');

The POLYGON type has a 2-dimensional geometry consisting of one exterior boundary and 0 or more interior boundaries, where each interior boundary defines a hole.

The following example inserts such a polygon into table tab1:

     INSERT INTO tab1 VALUES (0, 'POLYGON((0 0, 0 20, 20 20, 20 0, 0 0),
                                          (5 5, 5 10, 10 10, 10 5, 5 5))
                                 ');

The GEOMCOLLECTION type is a collection of 0 or more ST_Geometry values.

The following example inserts such a geometric collection into table tab1:

     INSERT INTO tab1 VALUES (0, 'GEOMETRYCOLLECTION(
                                  POINT(10 10),
                                  POINT(30 30),
                                  LINESTRING(15 15, 20 20 ) )');

The MULTIPOINT type is a 0-dimensional geometry collection whose elements are restricted to POINT values.

The following example inserts such a geometric collection into table tab1:

     INSERT INTO tab1 VALUES (0,'MULTIPOINT(1 1, 1 3, 6 3, 10 5, 20 1)');

The MULTILINESTRING type is a 1-dimensional geometry collection whose elements are restricted to LINESTRING values.

The following example inserts such a geometric collection into table tab1:

     INSERT INTO tab1 VALUES (0, 'MULTILINESTRING((1 1, 1 3, 6 3),
                                                  (10 5, 20 1))');

The MULTIPOLYGON type is a 2-dimensional geometry collection whose elements are restricted to POLYGON values.

The following example inserts such a geometric collection into table tab1:

     INSERT INTO tab1 VALUES (0,'MULTIPOLYGON(
                                  ((1 1, 1 3, 6 3, 6 0, 1 1)),
                                  ((10 5, 10 10, 20 10, 20 5, 10 5)))');