## Introduction

Data comes in many, many forms. It comes temporally as well as spatially. GridDB’s Timeseries containers as well as its TQL and time-specific API operations cover the time aspect of data. GridDB has geospatial data covered as well in GridDB Standard Edition and Advanced Edition. Support is provided with **Geometry Columns and Data Types** along with specialized **Geometry Operations** through TQL as well as through the C and Java APIs.

In GridDB, Geometry values can be created to represent **5** general models or shapes.

### POINT

A coordinate point in either two dimensional or three dimensional space.

**2D and 3D Points**

Geometry point = Geometry.valueOf("POINT(1 3.4)"); Geometry threeDPt = Geometry.valueOf("POINT(2 -3 0.43)");

### LINESTRING

A set of one or more *interconnected* lines in two or three dimensional space. They are represented by two or more points.

### POLYGON

A closed area in either two or three dimensional space. The area consists of three or more points that are connected from a starting point, or `VERTEX`

, to the endpoints and back to the starting point. It is possible to have the surface or area to have gaps or islands that are not part of the model by specifying the internal areas to exclude from the outer area.

**2D Polygon**

Geometry polygon = Geometry.valueOf("POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))");

**3D Polygon**

Geometry threeDSurface = Geometry.valueOf("POLYGON((0 0 0, 1 0 1, 1 1 1, 0 1 0, 0 0 0))");

### POLYHEDRALSURFACE

The `POLYHEDRALSURFACE`

surface consists of *vertices, edges, and facets*. Simple examples tetrahedrons, polyhedrons, and octohedral surfaces. The *POLYHEDRALSURFACE* can only be a three-dimensional shape. This means all the points that make up its edges are supposed to contain **3 coordinates**. Polyhedral surfaces can be thought of as a full three-dimensional extension of the *POLYGON* in that the shape can enclose a volume where its faces are each comprised of a closed polygonal surface.

**Example**

Geometry polySurface = Geometry.valueOf( \ "POLYHEDRALSURFACE ( ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0))," + "((0 0 0, 0 1 0, 0 1 1, 0 0 1, 0 0 0 ))," + "((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0))," + "((1 1 1, 1 0 1, 0 0 1, 0 1 1, 1 1 1)) ," + "((1 1 1, 1 0 1, 1 0 0, 1 1 0, 1 1 1))," + "((1 1 1, 1 1 0, 0 1 0, 0 1 1, 1 1 1)) )");

Every set of coordinates should be enclosed by double-parenthesis `((`

, `))`

because it represents a closed face that will make up part of the three-dimensional enclosed surface. Think of the faces as `POLYGON`

‘s that make up the sides of the surface. All these are then grouped and connected together to form the three-dimensional surface object.

### QUADRATICSURFACE

A three dimensional quadratic surface. These objects are curved surfaces in three-dimensional space by the equation: `f(X) = <AX,X> + BX + C`

. Create a surface by inserting **16** numbers that represent the *coefficents* of making that surface equation.

The *QUADRATICSURFACE* is different from the other four Geometry objects in that its WKT (Well-Known-Text format) inputs are coefficients to an equation rather than coordinates in the 3D plane. This equation,

ax^{2}+by^{2}+cz^{2}+2fyz+2gzx+2hxy+2px+2qy+2rz+d = 0

When converted into a *quadratic-form* matrix:

a |
h |
g |
p |

h |
b |
f |
q |

g |
f |
c |
r |

p |
q |
r |
d |

**Example Surface**

GSRow* row; const GSChar* wkt = "QUADRATICSURFACE(( 84 2 3 5 6 7 8 9 10 12 13 14 15 16 17 18 ))"; // (snip) gsGetNextRow(rowSet,row); gsSetRowFieldByGeometry(row,4,wkt); gsPutRow(timeseries,NULL,row,NULL);

Examples of quadric surfaces include parallel planes, hyperboloids, cones, cylinders and spheres. These types of surfaces are mapped to different polynomial equations. Some of these equations may not have certain factors or coefficients. For example, there may be no `x`

‘s in the equation like in

y^{2}+ z^{2}- 1

Since there are no *x’s* in the equation, replace all coefficients that are near x like *a,h,p* with 0’s. When entering the matrix values for the **QUADRATICSURFACE** WKT, there must always be **16** values, with any missing values replaced with a 0.

More examples and information on quadric surfaces can be found in this Wolfram Alpha page.

## Creating Objects with WKT Format

Creating any of these objects in GridDB is fairly simple. All that is needed on the development end is to know how to establish the model in `WKT`

form. WKT (Well-Known-Text) text formatting is a text markup language used for representing vector geometry objects on a map or in a spatial reference system. If the WKT string is parsed correctly, the Geometry is created and inserted into its respective column into the Collection or Timeseries. There are certain characteristics of WKT format that should be noted and followed or else errors will be thrown or the Geometry object will not be created.

##### General Formatting Rules

When stating the shape of the Geometry (POINT,LINESTRING,etc.) ensure that you use **ALL UPPERCASE LETTERS** and correct syntax. If not done so, the object will not be created and *exceptions* could be raised. Ensure that the formatting is correct and there are no syntax errors such as not having correct amount of parenthesis or not using proper spacing.

If you wish to create an empty Geometry object, create a `POINT(EMPTY)`

geometry.

**Empty LINESTRING and POLYGON**

Geometry emptyPoint = Geometry.valueOf("POINT(EMPTY)");

You can learn more about general WKT formatting on this Wikipedia page.

##### Range Restrictions

In some *GIS*-supporting (Geographic Information System) databases, the ranges or values of points can include special values like `NaN`

, `INF`

, `-INF`

(Not-a-Number, Negative and Positive Infinity). Only numerical-types can be used which include positive or negative floating point numbers.

##### Forming Closed Surfaces

Usually Geometries beyond POINT and QUADRATICSURFACE consist of points that are connected. The points, once connected together, form lines that will make up the edges. The edges usually converge to form a closed surface. For all Geometries except *POINT*, more than one data point is needed.

##### Dimensions of Geometry Values

The points that make a Geometry can only be in *two-dimensional or three-dimensional* format. Also, when creating points to outline an object the dimensions must stay consistent (i.e a polygon that has a coordinate in 2D must have all its other coordinates in 2D). Points are separated by commas: `,`

and the `x,y,z`

coordinates are delimited by `SPACES`

## Defining Geometric Columns and Indexes

Geometry columns can be added to the column schema for a container either in a static fashion or a dynamic fashion. The `Geometry`

can be directly imported from the GridDB library and can be set as a column type for a *class schema* for a container. You can also set an index on a Geometry column by setting a `SPATIAL`

index flag.

**Java Container Creation**

// (snip) import com.toshiba.mwcloud.gs.Geometry; import com.toshiba.mwcloud.gs.GeometryOperator; //(snip) static class Shape { @RowKey String name; double perimeter; double volume; Date creation; Geometry geom; public String toString(){ return String.format("Shape: Name = %s, Perimeter = %f, \ Volume = %f, Creation Date = %s, WKT = %s",\ name,perimeter,volume,creation.toString(),geom.toString()); //(snip) Collection<String,Shape> geometryCollection = \ gridstore.putCollection("GeometryCollection101",Shape.class); // (snip) geometryCollection.createIndex("geom",IndexType.SPATIAL);

There is no explicit GSGeometry struct or enum in GridDB’s C API. When creating a container with a dynamic schema using `ContainerInfo`

and `ColumnInfo`

objects, set the ColumnInfo’s *type* as `GS_TYPE_GEOMETRY`

. If you are using **struct-bindings**, set the column type that you wish to set as Geometry to have the type `const GSChar*`

. From there, set the column’s type to Geometry when performing the struct-bindings. Simply set the column value with its **WKT** form and GridDB will convert it to its binary equivalent in the database.

**C Container Creation**

typedef struct { const GSChar* name; GSTimestamp timestamp; double volume; double perimeter; const GSChar* shape; } GeometryRow; GS_STRUCT_BINDING(GeometryRow, GS_STRUCT_BINDING_KEY(name, GS_TYPE_STRING) GS_STRUCT_BINDING_ELEMENT(timestamp,GS_TYPE_TIMESTAMP) GS_STRUCT_BINDING_ELEMENT(volume,GS_TYPE_DOUBLE) GS_STRUCT_BINDING_ELEMENT(perimeter,GS_TYPE_DOUBLE) GS_STRUCT_BINDING_ELEMENT(shape,GS_TYPE_GEOMETRY)); //(snip) GSCollection* collection; gsPutCollection(gridstore,"GeometryCollection311", \ GS_GET_STRUCT_BINDING(GeometryRow),NULL,GS_FALSE,&collection); gsCreateIndex(collection,"shape",GS_INDEX_FLAG_SPATIAL); GeometryRow gRow; gRow.name = "FirstPoint"; gRow.timestamp = gsCurrentTime(); gRow.volume = 0; double x, y, z; sscanf(wkt,"POINT(%lf %lf %lf)",&x,&y,&z); gRow.perimeter = sqrt((x * x) + (y * y) + (z * z)); gRow.shape = "POINT(1 4.23 4.2)"; gsPutRow(collection,NULL,&gRow,NULL);

## Spatial Operations in GridDB’s API

Geometric data values, when inserted into a GridDB database, are stored in *binary* form, much like `BLOB`

data types. This means that performing direct equality operations are not allowed with `TQL`

.

**Example of Invalid TQL**

SELECT * WHERE geom = ST_GeomFromText('POINT(2 4 5)') #(Throws Error)

In Java it is possible to compare the Geometry values directly to each using the API from GridDB.

Geometry pointToFind = Geometry.valueOf("POINT(0 4)"); // (snip) while(rowSet.hasNext){ Row row = rowSet.next(); Geometry rowPoint = row.getGeometry(4); boolean match = rowPoint.equals(pointToFind); System.out.println(match);

One spatial operation that is allowed through GridDB’s API and TQL is a `SPATIAL INTERSECTION`

. This translates to searching for a common area or volume between the two objects. A 3D intersection consists of determining a *Minimum Bounding Box*, known as a rectangular parallelepiped, where the two objects intersect. When objects are two-dimensional, the z-coordinates have range values of `(-INF,INF)`

. This means that checking against a 2D dimensional object with a 3D dimensional object will have a more inclusive z-range to increase the chances of intersection.

When comparing an object with a *POINT*, the bounding box’s vertices are made up of the points. The box’s vertices are all on the same side and have a length of 0. If comparing a POINT to a non-point object, GridDB will check if the bounding box contains the point in its area.

When comparing between shapes that are not points, such as two *POLYGON* objects, the bounding box will have minimum and maximum x, y, and z coordinates that will make up the intersection’s spatial structure.

**Examples of Spatial Intersections**

### Issuing Geometric Queries with GridDB API

Intersection queries can be made quickly without TQL on `Collection`

containers by issuing `.query(String column,Geometry geom,GeometryOperator operator)`

in Java or `gsQueryByGeometry()`

. This query will search and fetch all rows

whose ‘column’ value will intersect with `const GSChar* geometry`

.

**Java**

Geometry intersection = Geometry.valueOf("POLYGON((0 0 0, 2 0 0, 2 2 0, 0 2 0, 0 0 0))"); Queryquery = geometryCollection.query("geom",intersection, \ GeometryOperator.INTERSECT); RowSet rowSet = query.fetch(false); while(rowSet.hasNext()){ Shape shape = rowSet.next(); System.out.println(shape);

**C**

GSQuery* query; GSRowSet* rowSet; GeometryRow geometryRow; const GSChar* point = "POINT(1 4.56 4.2)"; gsQueryByGeometry(collection,"shape",point,GS_GEOMETRY_OPERATOR_INTERSECT,&query); gsFetch(query,GS_FALSE,&rowSet); while(gsHasNextRow(rowSet)){ gsGetNextRow(rowSet, &fetchedRow); GSChar timeStr[GS_TIME_STRING_SIZE_MAX]; gsFormatTime(fetchedRow.timestamp,timeStr,sizeof(timeStr)); printf("Name = %s",fetchedRow.name); printf(" Timestamp = %s",timeStr); printf(" Volume = %.2lf", fetchedRow.volume); printf(" Perimeter = %.2lf", fetchedRow.perimeter); printf(" Shape = %s\n",fetchedRow.shape);

### TQL Queries with Geometry Values

TQL can be used to check for geometric intersections along with other conditions you wish to specify. In the case of comparing two *non-quadratic* shapes, you can use the GIS function `ST_MBRIntersects(geometry1,geometry2)`

. If you wish to specify a Geometry to check for an intersection, simply enter the shape in WKT form and turn it into a Geometry object with TQL’s `ST_GeomFromText(wkt)`

function.

Let’s say we want to fetch up to 5 rows whose ‘geom’ column value intersects with **POLYGON**:

`POLYGON((2 2 4, 5 2 4, 5 2 8, 2 2 8, 2 2 4))`

**AND** whose *value* column is greater than 7.5 in *descending* order. You could issue the query as follows:

String tql = "SELECT * WHERE ST_MBRIntersects(geom," + "ST_GeomFromText('POLYGON((2 2 4, 5 2 4, 5 2 8, 2 2 8, 2 2 4))'))" + " AND value > 7.5 ORDER BY value DESC LIMIT 5"; Query<Row> query = collection.query(tql);

One thing to note about *ST_MBRIntersects* is that you cannot use *QUADRATICSURFACES* for comparison. In the case that you want to check for intersection between a *quadratic surface* and a *non-quadratic-surface*. You can use the `ST_QSFMBRIntersects(quadratic,geometry)`

to check for the intersection and use `ST_MakeQSF(A00,A10,A20,A01...)`

The *A00, A10, A20,A01* are used to represent the coefficents of the `<AX,X> + BX + C`

equation that make up the Quadratic Surface.

**Example of Quadratic Surface Query**

GSQuery* query; GSRowSet* rowSet; gsQuery(timeseries, \ "select * where STQSFMBRIntersects( \ ST_MakeQSF(84,2,3,5,6,7,8,9,10,12,13,14,15,16,17,18),geom) AND id='row1'",\ &query); gsFetch(query,GS_FALSE,&rowSet);

## Best Uses of Spatial Data

Geometric and spatial data types are highly useful in domains like engineering, science, architecture, geography, and IoT. In geography or IoT, spatial data can be used to represent latitude, longitude, and elevation of sensors or objects. On example could be the last three categories can be represented as three-dimensional point in the format `POINT(latitude longitude elevation)`

. You can use spatial intersections with *POINT’s* to find a row at a specific location.

*POLYGON*‘s can be used to find rows that are within a geographic range. For example, a geometric query can be used with `POLYGON(57 110, 59 110, 59 130, 57 130, 57 110)`

to find all rows within the area between 57 and 59 latitude and between 130 and 110 longitude.

Linestrings, polygons, and surfaces can be used to represent vectors or three dimensional object. Being able to create, store, query, and index spatial data is especially useful for fields like engineering and architecture where buildings and devices are based off of 3D designs.

A specific use case for GridDB could involve creating a collection of traffic sensors. It can store and track general data like whether its active or how many cars it has seen and other values. You can store its geographic location in terms of a **POINT** with the **GEOMETRY**. Intersection queries can be used to find filter based on locations using either API functions or TQL.

**Traffic Sensor in Java**

public class TrafficLight { @RowKey public String tag; // Sensor Id of Smart Traffic Light public Geometry point; // Latitude, Longitude, and Elevation of Sensor public Date installation; // Time of installation to the Traffic Grid public boolean active; // Whether the traffic light sensor has been activated public int reports; // Number of reports that the Traffic Light has issued public String streets; // Cross streets of Traffic Light // (snip)

**Querying the Traffic Collection in Java**

Collection<String,TrafficLight> trafficLightCollection = \ gridstore.putCollection("trafficLights101",TrafficLight.class); // (snip) trafficLightCollection.createIndex("point",IndexType.SPATIAL); // (snip) // Search for a specific POINT String ptTql = "SELECT * WHERE ST_MBRIntersects(point," + "ST_GeomFromText('POINT(47.33 84.72 0.7)'))"; Query<TrafficLight> ptQuery = trafficLightCollection.query(ptTql); // (snip) // Search for Point in a 2D Range using POLYGON String tql = "SELECT * WHERE ST_MBRIntersects(point," + "ST_GeomFromText('POLYGON((49.00 86.00, 51.00 86.00," + "51.00 87.00, 49.00 87.00, 49.00 86.00))'))"; Query<TrafficLight> query = trafficLightCollection.query(tql);

## Using GridDB Standard Edition

In GridDB Community edition it is possible to create **Geometry** objects but it is not possible to insert them as column values or use them as part of a container’s schema. These features are only available for **Standard Edition** and beyond. GridDB Standard Edition can be used with AWS. General setup and configuration information can be found in the Quick Start Guide.

More information on the GEOMETRY type can be found in this section of the GridDB SE Technical Reference. There are more details on Spatial operations in the API Reference manual. GridDB uses regular expressions patterns to determine if an enter format is valid WKT. The patterns that are acceptable can be found in this section of the GridDB source code.

If you have any questions about the blog, please create a Stack Overflow post here https://stackoverflow.com/questions/ask?tags=griddb .

Make sure that you use the “griddb” tag so our engineers can quickly reply to your questions.