15.10 - Tessellate_Search Function - Teradata Database

Teradata Database SQL Geospatial Types

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1181-151K

Table function that returns all cells at each level of the grid that contain the object passed in. The function is based on the concept of a multilevel g_nx by g_ny grid. The returned cell IDs are suitable for using as search keys for the tessellate_index generated key.

 

Argument …

Specifies …

in_key

a key that is passed back in the result rows to allow joining back to the proper object.

The data type of in_key is DECIMAL(18,0) or VARCHAR(32). The data type of the argument you pass in determines the data type of the out_key column in the result row.

o_xmin,
o_ymin,
o_xmax,
o_ymax

the coordinates of the object rectangle.

The data type of o_xmin, o_ymin, o_xmax, and o_ymax is FLOAT.

u_xmin,
u_ymin,
u_xmax,
u_ymax

the coordinates of the universe of interest.

The data type of u_xmin, u_ymin, u_xmax, and u_ymax is FLOAT.

g_nx,
g_ny

the number of grid cells to divide the universe into in the X and Y dimensions. The maximum number of grid cells is 228.

The data type of g_nx and g_ny is INTEGER.

If g_nx or g_ny is less than or equal to zero, Tessellate_Search returns an error.

levels

the number of levels in the two-dimensional grid. There are always levels + 1 levels, where level 0 is the entire universe. Higher levels are more granular.

The data type of levels is INTEGER and the range of values is 1 to 15.

scale

the scaling factor between grid levels. For example, if g_nx * g_ny is 100x100 and level is 2 and scale is 0.1, you have a 3 level grid (100x100, 10x10, 1x1).

The data type of scale is FLOAT.

The value of scale must be greater than 0.0 and less than 1.0.

shift

a numeric value that represents the number of times to shift the grid at each level. If the value of shift is 0, the method performs no shifting. If the value of shift is 1, the method creates four grids for each level, where it shifts each of the four grids in a unique manner.

Shifting the grid helps to avoid assigning smaller spatial objects to large cells if they cross a grid cell boundary.

The data type of shift is INTEGER.

If any input argument is NULL, Tessellate_Search returns an error.

For details on the rules for argument data types, see Chapter 10: “Embedded Services System Functions.”.

The columns in the table that Tessellate_Search returns are as follows.

 

Column Name

Data Type

Description

out_key

 

DECIMAL(18,0)

A copy of the in_key input argument.

The data type of out_key matches the data type of the in_key argument.

VARCHAR(32)

cellid

 

 

 

 

 

 

 

 

 

 

 

INTEGER

The INTEGER value that is returned codes for the cell number and level. The upper 28 bits identifies the cell number, and the lower four bits identifies the level. Use:

cellid / 16 

to derive the cell number and:

cellid MOD 16 

to derive the level.

Cell IDs are numbered from left to right, 0 to n-1, where n = g_nx * g_ny. Cell 0 is in the lower left corner. Here is an example that shows the cell numbering of a single-level grid (of unspecified size) that contains 16 cells.

CREATE TABLE sample_cities(
 skey INTEGER, cityName VARCHAR(200), cityShape ST_Geometry);
INSERT INTO sample_cities values(
 1067, New ST_Geometry('Polygon(
  (-43.5 20.2, -43.5 21.5, -40.5 21.5, -40.5 20.2, -43.5 20.2))'));

Consider the data in the cities_index table that is inserted using the Tessellate_Index method:

CREATE TABLE cities_index
   (skey INTEGER
   ,cellid INTEGER);
 
INSERT INTO cities_index
SELECT skey,
   cityShape.Tessellate_Index(-180, 0, 0, 90, 500, 500, 1, 0.01, 0)
FROM sample_cities;

The following statement uses the Tessellate_Search function with the same universe coordinates and grid specifications that were used by Tessellate_Index:

SELECT c.skey, c.cityName, s.streetName, s.streetShape
FROM sample_cities c
    ,cities_index ci
    ,(SELECT streetName, skey, streetShape ,streetShape.ST_MBR_Xmin()
            ,streetShape.ST_MBR_Ymin(), streetShape.ST_MBR_Xmax()
            ,streetShape.ST_MBR_Ymax() 
      FROM sample_streets) 
      AS s (streetName, skey, streetShape, xmin, ymin, xmax, ymax)
    ,TABLE ( Tessellate_Search (
                s.skey
               ,s.xmin, s.ymin, s.xmax, s.ymax
               ,-180, 0, 0, 90
               ,500,500
               ,1, 0.01, 0)) AS t
WHERE c.skey = ci.skey
AND ci.cellid = t.cellid
AND t.out_key = s.skey;