15.10 - PolygonSplit - Teradata Database

Teradata Database SQL Geospatial Types

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

Table operator that accepts a table containing ST_Geometry Polygon types and recursively processes qualifying polygons into sets of smaller sub-polygons, each having fewer vertices than the original polygon. Taken together, the sub-polygons closely approximate the shape and area of the original polygon.

If the input polygon exceeds a specified maximum number of vertices, PolygonSplit divides the polygon into four sub-polygons, one occupying each quadrant of the input polygon. The sub-polygons thus formed are similarly, recursively split until each resulting polygon has fewer than the specified maximum number of vertices.

The input table must include an ST_Geometry column to hold a polygon and an identifier column to hold a unique identifier for each polygon. The sub-polygons that are created all share the identifier of the original polygon that was split.

 

 

Argument …

Specifies …

polygon_ID

a numeric or character value that uniquely identifies the polygon or multipolygon to be split. This same value is returned for each of the sub-polygons that is returned as a result of the splitting, and can be used to associate the sub-polygons with the original polygon.

polygon_ID can be any valid Teradata Database numeric or character data type except CLOB.

If polygon_ID is NULL for a given polygon, the results will show corresponding NULLs for the IDs of the unchanged polygon or for the sub-polygons that are generated.

geom

the polygon or multipolygon geometry to be split.

PolygonSplit accepts all types of ST_Geometry, but only 2D polygons and 2D multipolygons are split. Other geometries are returned unchanged.

If geom is NULL, PolygonSplit returns NULL for that geometry.

max_vertices

the maximum number of vertices that a returned sub-polygon can include. If the original polygon or multipolygon has fewer than this number of vertices, it is returned unchanged.

max_vertices must be greater than or equal to 10. The default is 300.

If max_vertices is NULL, PolygonSplit defaults to 300.

[database_name.]
table_name

the name and optional database location of the table that contains the polygon and multipolygon geometries to be split.

correlation_name

A name to be assigned to the table returned by the table operator.

column_list

Optional list of custom names for the columns returned by the table operator. These names override the column names that would be otherwise generated by the operator. If you specify any column names, you must provide names for all columns returned. If more than one column is returned by the operator, separate the names with commas.

Returns the a table having the following columns:

 

Column Name

Data Type

Description

out_polygon_ID

Matches type of polygon_ID input parameter

All sub-polygons that result from the splitting of a polygon or multipolygon will share the ID of the input polygon or multipolygon. Geometries that are unchanged by PolygonSplit will retain their original ID in the results table.

sub_polygon_ID

INTEGER

Uniquely identifies each subpolygon that came from a particular input polygon or multipolygon. The subpolygons are numbered sequentially as they are created. Numbering starts at 0.

split_geom

ST_Geometry

A sub-polygon that results from splitting a polygon or multipolygon. For input polygons and multipolygons that have fewer than the maximum specified number of vertices, and for all other geometries, this column contains the original, unchanged input geometry.

The splitting process is subject to precision and rounding errors that are inherent with floating-point arithmetic. Consequently, there can be small discrepancies between the area of the original polygon and the sum of the areas of the resulting sub-polygons.

CREATE TABLE feature_tbl(poly_id INTEGER, geom ST_Geometry);
INSERT INTO feature_tbl VALUES(100, new ST_Geometry('POLYGON ((50 50,50 60,50 70,50 80,50 90,50 100,50 110,50 120,50 130,50 140,50 150,50 160,50 170,50 180,50 190,50 200,50 210,50 220,50 230,50 240,50 250,50 260,50 270,50 280,50 290,50 300,50 310,50 320,50 330,50 340,50 350,50 360,50 370,50 380,50 390,50 400,50 410,50 420,50 430,50 440,50 450,50 460,50 470,50 480,50 490,50 500,50 510,50 520,50 530,50 540,50 550,50 560,50 570,50 580,50 590,50 600,50 610,50 620,50 630,50 640,50 650,50 660,60 670,50 680,50 690,50 700,50 710,50 720,50 730,50 740,50 750,50 760,50 770,50 780,50 790,50 800,50 810,50 820,50 830,50 840,50 850,50 860,50 870,50 880,50 890,50 900,50 910,50 920,50 930,50 940,50 950,50 960,50 970,50 980,50 990,50 1000,50 1010,50 1020,50 1030,50 1040,50 1050,50 1060,50 1070,50 1080,50 1090,50 1100,50 1110,50 1120,50 1130,50 1140,50 1150,50 1160,50 1170,50 1180,50 1190,50 1200,60 1200,70 1200,80 1200,90 1200,100 1200,110 1200,120 1200,130 1200,140 1200,150 1200,160 1200,170 1200,180 1200,190 1200,200 1200,210 1200,220 1200,230 1200,240 1200,250 1200,260 1200,270 1200,280 1200,290 1200,300 1200,310 1200,320 1200,330 1200,340 1200,350 1200,360 1200,370 1200,380 1200,390 1200,400 1200,410 1200,420 1200,430 1200,440 1200,450 1200,460 1200,470 1200,480 1200,490 1200,500 1200,510 1200,520 1200,530 1200,540 1200,550 1200,560 1200,570 1200,580 1200,590 1200,600 1200,610 1200,620 1200,630 1200,640 1200,650 1200,660 1200,670 1200,680 1200,690 1200,700 1200,710 1200,720 1200,730 1200,740 1200,750 1200,760 1200,770 1200,780 1200,790 1200,800 1200,810 1200,820 1200,830 1200,840 1200,850 1200,860 1200,870 1200,880 1200,890 1200,900 1200,910 1200,920 1200,930 1200,940 1200,950 1200,960 1200,970 1200,980 1200,990 1200,1000 1200,1010 1200,1020 1200,1030 1200,1040 1200,1050 1200,1060 1200,1070 1200,1080 1200,1090 1200,1100 1200,1110 1200,1120 1200,1130 1200,1140 1200,1150 1200,1160 1200,1170 1200,1180 1200,1190 1200,1200 1200,1200 1190,1200 1180,1200 1170,1200 1160,1200 1150,1200 1140,1200 1130,1200 1120,1200 1110,1200 1100,1200 1090,1200 1080,1200 1070,1200 1060,1200 1050,1200 1040,1200 1030,1200 1020,1200 1010,1200 1000,1200 990,1200 980,1200 970,1200 960,1200 950,1200 940,1200 930,1200 920,1200 910,1200 900,1200 890,1200 880,1200 870,1200 860,1200 850,1200 840,1200 830,1200 820,1200 810,1200 800,1200 790,1200 780,1200 770,1200 760,1200 750,1200 740,1200 730,1200 720,1200 710,1200 700,1200 690,1200 680,1200 670,1200 660,1200 650,1200 640,1200 630,1200 620,1200 610,1200 600,1200 590,1200 580,1200 570,1200 560,1200 550,1200 540,1200 530,1200 520,1200 510,1200 500,1200 490,1200 480,1200 470,1200 460,1200 450,1200 440,1200 430,1200 420,1200 410,1200 400,1200 390,1200 380,1200 370,1200 360,1200 350,1200 340,1200 330,1200 320,1200 310,1200 300,1200 290,1200 280,1200 270,1200 260,1200 250,1200 240,1200 230,1200 220,1200 210,1200 200,1200 190,1200 180,1200 170,1200 160,1200 150,1200 140,1200 130,1200 120,1200 110,1200 100,1200 90,1200 80,1200 70,1200 60,1200 50,1190 50,1180 50,1170 50,1160 50,1150 50,1140 50,1130 50,1120 50,1110 50,1100 50,1090 50,1080 50,1070 50,1060 50,1050 50,1040 50,1030 50,1020 50,1010 50,1000 50,990 50,980 50,970 50,960 50,950 50,940 50,930 50,920 50,910 50,900 50,890 50,880 50,870 50,860 50,850 50,840 50,830 50,820 50,810 50,800 50,790 50,780 50,770 50,760 50,750 50,740 50,730 50,720 50,710 50,700 50,690 50,680 50,670 50,660 50,650 50,640 50,630 50,620 50,610 50,600 50,590 50,580 50,570 50,560 50,550 50,540 50,530 50,520 50,510 50,500 50,490 50,480 50,470 50,460 50,450 50,440 50,430 50,420 50,410 50,400 50,390 50,380 50,370 50,360 50,350 50,340 50,330 50,320 50,310 50,300 50,290 50,280 50,270 50,260 50,250 50,240 50,230 50,220 50,210 50,200 50,190 50,180 50,170 50,160 50,150 50,140 50,130 50,120 50,110 50,100 50,90 50,80 50,70 50,60 50,50 50))'));

SELECT SplitTable.poly_id, SplitTable.sub_poly_id, SplitTable.splitGeom
FROM PolygonSplit
 (ON
    (
     SELECT poly_id, geom, cast(400 AS INTEGER)
     FROM feature_tbl
     WHERE poly_id=100
    )
 )
AS SplitTable(poly_id, sub_poly_id, splitGeom) 
ORDER BY 1,2,3;

The following output shows that the polygon with an id of 100 was split into four pieces. The actual vertex values that define each polygon are not shown, and are instead replaced with "…"

   poly_id  sub_poly_id  splitGeom
   -------  -----------  ---------------
       100            0  POLYGON ((...))
       100            1  POLYGON ((...))
       100            2  POLYGON ((...))
       100            3  POLYGON ((...))