15.10 - Tessellate UDF - Teradata Database

Table function that returns the set of cell IDs that intersect an object rectangle. The function is based on the concept of a single level g_nx by g_ny grid. The return value is suitable to use as an equijoin bind term.

Argument …	Specifies …
in_key	a DECIMAL(18,0) value for a key that is passed back in the result rows to allow joining back to the proper object.
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 data type of g_nx and g_ny is INTEGER.

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

Column Name	Data Type	Description
out_key	DECIMAL(18,0)	A copy of the in_key input argument.
cellid	INTEGER	Cell ID that intersects the object rectangle.

Here is an example that shows the cell numbering of a single-level grid (of unspecified size) that contains 16 cells.

Consider two tables, P500000 and R5000, each of which contains a column that is a geospatial shape (column A in table P500000 and column B in table R5000).

For simplicity, the two tables have MBRs as their shapes, but any type of shape can be used by passing the MBR of the shape to the tessellation function and then doing the overlap comparison between the two shapes via the ST_Overlaps method in the final WHERE clause.

The following statement counts the number of objects that overlap between the two tables.

   SELECT COUNT(*) 

   FROM ( SELECT * 

          FROM P500000 A

              ,TABLE(SYSSPATIAL.Tessellate(A.rkey, A.xmin, A.ymin,                         A.xmax, A.ymax, 0, 0, 100, 100, 100, 100 ) )T1

          WHERE A.rkey = T1.out_key ) S1

       ,( SELECT * 

          FROM R5000 B

              ,TABLE(SYSSPATIAL.Tessellate(B.rkey, B.xmin, B.ymin,                         B.xmax, B.ymax, 0, 0, 100, 100, 100, 100 ) )T2

          WHERE B.rkey = T2.out_key )S2							

   WHERE S1.cellid = S2.cellid 

      AND S1.xmax >= S2.xmin and S1.xmin <= S2.xmax

      AND S1.ymax >= S2.ymin and S1.ymin <= S2.ymax;

Here is how the tessellation works in the preceding example.

We define a tessellation grid that is large enough to contain the universe of shapes within our two tables and we define a granularity that is reasonable given the typical size of a spatial object. In this case, the grid is 100x100 in size as defined by a universe MBR of 0,0 to 100,100 and the “number of grids” in both the x and y direction is defined to be 100.

In the first SELECT statement, a row at a time is selected from P500000. For each row, the tessellate table function is called to return a table of cell ids that contain the spatial object. This produces a table (S1) of the spatial objects and the cells that contain them. For example, if a row from P500000 is processed and the spatial object is in four cells, four rows are added to S1, each with the same spatial object and a cell id. This continues for all rows in P500000.

The second SELECT statement is processed on table R5000 in the same way as the first select statement with the results going into S2.

Now there are two tables, S1 and S2. Next, the final WHERE clause does a join between S1 and S2 on the cell id and the overlaps computation, which is simple for an MBR shape. This produces a row if the two shapes overlap. If we assume that the overlaps computation can be expensive, the performance gain comes from the fact that only spatial objects that are in the same cell are compared via the overlaps computation. The overlaps computation is never executed for objects that are not physically close to one another.