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;