Archive
Archive
ISVALID() — Determines if the specified GEOGRAPHY value is a valid polygon.
ISVALID( polygon )
The ISVALID() function returns true or false depending on whether the specified GEOGRAPHY value is a valid polygon or not. Polygons must follow rules defined by the Open Geospatial Consortium (OGC) standard for Well Known Text (WKT). Specifically:
A GEOGRAPHY polygon consists of one or more rings, where a ring is a closed boundary described by a sequence of vertices and the lines, or edges, between those vertices.
The first ring must be the outer ring and the vertices must be listed in counter clockwise order.
All subsequent rings represent "holes" in the outer ring. The inner rings must be wholly contained within the outer ring and their vertices must be listed in clockwise order.
Rings cannot intersect or have adjacent edges.
The edges of an individual ring cannot cross (for example, a figure "8" is invalid).
For each ring, the first vertex is listed twice: as both the first and last vertex.
If the specified GEOGRAPHY value is a valid polygon, the function returns true. If not, it returns false.
To maximize performance, VoltDB does not validate the GEOGRAPHY values when they are inserted. However, if you are not sure the WKT strings are valid, you can use ISVALID() to validate the resulting GEOGRAPHY values before inserting them or after they are inserted into the database.
The first example shows an UPDATE statement that uses the ISVALID() function to remove the contents of a GEOGRAPHY column (by setting it to NULL), if the current contents are invalid.
UPDATE REGION SET border = NULL WHERE NOT ISVALID(border);
The next example shows part of a stored procedure that uses ISVALID() to conditionally set the value of a column, mustbevalid, that is defined as NOT NULL. By setting the column valid to NULL, the procedure ensures that the INSERT statement fails and the stored procedure rolls back if the WKT border column is invalid.
V7.x Documentationpublic class ValidateBorders extends VoltProcedure {
public final SQLStmt insertrec = new SQLStmt(
"INSERT INTO REGION (name, border, mustbevalid)" +
" SELECT name, border," +
" CASE WHEN ISVALID(border) THEN 1 ELSE NULL END" +
" FROM anothertable WHERE name = ? LIMIT 1;"
);
public VoltTable[] run( String name)
throws VoltAbortException
{ voltQueueSQL( insertrec, name); return voltExecuteSQL(); }
}