Ensuring polygon validity before creating or refreshing an index
When Vertica creates or updates a spatial index it does not check polygon validity. To prevent getting invalid results when you query your spatial index, you should check the validity of your polygons prior to creating or updating your spatial index.
The following example shows you how to check the validity of polygons.
-
Create a table and load spatial data.
=> CREATE TABLE polygon_validity_test (gid INT, geom GEOMETRY); CREATE TABLE => COPY polygon_validity_test (gid, gx FILLER LONG VARCHAR, geom AS St_GeomFromText(gx)) FROM STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 2|POLYGON((-31 74,8 70,8 50,-36 53,-31 74)) >> 3|POLYGON((-38 50,4 13,11 45,0 65,-38 50)) >> 4|POLYGON((-12 42,-12 42,27 48,14 26,-12 42)) >> 5|POLYGON((0 0,1 1,0 0,2 1,1 1,0 0)) >> 6|POLYGON((3 3,2 2,2 1,2 3,3 3)) >> \. -
Use ST_IsValid and STV_IsValidReason to find any invalid polygons.
=> SELECT gid, ST_IsValid(geom), STV_IsValidReason(geom) FROM polygon_validity_test; gid | ST_IsValid | STV_IsValidReason -----+------------+------------------------------------------ 4 | t | 6 | f | Self-intersection at or near POINT (2 1) 2 | t | 3 | t | 5 | f | Self-intersection at or near POINT (0 0) (5 rows)
Now that we have identifed the invalid polygons in our table, there are a couple different ways you can handle the invalid polygons when creating or refreshing a spatial index.
Filtering invalid polygons using a WHERE clause
This method is slower than filtering before creating an index because it checks the validity of each polygon at execution time.
The following example shows you how to exclude invalid polygons using a WHERE clause.
```
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index = 'valid_polygons') OVER()
FROM polygon_validity_test
WHERE ST_IsValid(geom) = 't';
```
Filtering invalid polygons before creating or refreshing an index
This method is faster than filtering using a WHERE clause because you incur the performance cost prior to building the index.
The following example shows you how to exclude invalid polygons by creating a new table excluding invalid polygons.
```
=> CREATE TABLE polygon_validity_clean AS
SELECT *
FROM polygon_validity_test
WHERE ST_IsValid(geom) = 't';
CREATE TABLE
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index = 'valid_polygons') OVER()
FROM polygon_validity_clean;
```