8.2. Entering Geospatial Data

Documentation

VoltDB Home » Documentation » Guide to Performance and Customization

8.2. Entering Geospatial Data

As mentioned earlier, Well Known Text (WKT) is the standard presentation VoltDB uses for ingesting and reporting on geospatial data. However, you cannot insert WKT text strings directly as geospatial values. Instead, VoltDB provides SQL functions and Java classes and methods for translating from WKT to the internal geospatial values.

In SQL statements you can use the POINTFROMTEXT() and POLYGONFROMTEXT() functions to generate the appropriate geospatial datatypes from WKT. For example, the following SQL statement inserts the geographic location of New York City into the GEOGRAPHY_POINT column location:

INSERT INTO CITIES (name, location) VALUES
    ('New York City', POINTFROMTEXT('POINT(-74.0059 40.7127)');

In a Java stored procedure you can generate and store a GEOGRAPHY or GEOGRAPHY_POINT value from WKT using the classes GeographyValue and GeographyPointValue and the method .fromWKT(). For example, the following stored procedure takes two Java String objects, converts them to GEOGRAPHY and GEOGRAPHY_POINT values, then inserts them into a record via placeholders in the SQL statement:

import org.voltdb.*;
import org.voltdb.types.GeographyValue;
import org.voltdb.types.GeographyPointValue;   

public class InsertGeo extends VoltProcedure {

  public final SQLStmt insertrec = new SQLStmt(
     "INSERT INTO region VALUES (?,?,?);" );

  public VoltTable[] run( 
     String name, String poly, String point)
     throws VoltAbortException {
 
       GeographyValue g = GeographyValue.fromWKT(poly);
       GeographyPointValue p = GeographyPointValue.fromWKT(point);

       voltQueueSQL( insertrec, name, p, g);
       return voltExecuteSQL();
   }
}

A third option is to use the .fromWKT() method to create instances of GeographyValue and GeographyPointValue in the client application and pass the data to the stored procedure as native geospatial types.

When retrieving geospatial data from the database, the ASTEXT() SQL function converts from a GEOGRAPHY or GEOGRAPHY_POINT value to its textual representation. (You can also use the CAST( value AS VARCHAR) function). In a stored procedure or Java client application, you can use the .toString() method of the GeographyValue or GeographyPointValue class.