TRENDING NEWS
GridDB Cloud Quick Start Guide
Creating an LLM to Generate SQL Queries for GridDB
Building an Online Text Storage Service using Spring Boot & GridDB
GridDB Developer
JA
Download

Geometry Data Application

By Joshua PascascioIn BlogPosted

Introduction

GridDB SE brings support for spatial/geometric data which can be useful for developers. We’ve already delved into some of the features in our previous post — we’re going to be demonstrating a few more real world examples in this one.

Support for spatial/geometric data means adding GEOMETRY column support and methods that allow developers to model and utilize many types of spatial data such as lines, shapes, and three-dimensional objects. Furthermore, these geometries can be created with points or equations. One of the most common domains for GIS (Geographic information systems) that use geometric data are for geographic applications; examples include geolocation, maps, and traffic systems.

Creating Spatial Queries

A benefit that GEOMETRY columns can bring to modeling locations is providing many ways to filter for locations. In one example, the geometries used are POINTS, using the ST_MBRIntersects() TQL operation to find specific locations. For example, we use intersection queries to search for a point at 36.48 Latitude, 73.34 Longitude, and at 0.6 feet elevation above sea level.

With geometry values, you can search for points within 3D volumes or 2D areas. Here these geometries can include quadratic surfaces, cubes, 3D objects, areas, and 3D geometric planes. GridDB offers filtering for all of these different types — all at once — with intersection functions in TQL.

The most consistent way to check if any camera locations are within certain geometric range is by forming a POLYHEDRALSURFACE, then inserting that geometry into a TQL query. Begin by declaring the geometry in its WKT (Well-Known Text) form. Follow by checking if the geometry value intersects with the GEOMETRY-type column that you choose. You can convert the geometry text with the ST_GeomFromText operation. Let’s say you want to search for a camera that is located at 36.48° Latitude, 73.34° Longitude, and 16 ft. elevation. This location can be represented as: POINT(36.48 73.34 16). Since the geometry is now established, you can issue the below TQL query.

SELECT * WHERE ST_MBRIntersects(geometry_column,ST_GeomFromText(‘POINT(36.48 73.34 16)’))

Filtering for Points or Areas

In these applications, the types of geometries used for querying were:

  •  QUADRATICSURFACE
  •  POINT
  •  POLYGON
  •  LINESTRING
  •  POLYHEDRALSURFACE

You should model individual locations or points with the POINT geometry.

Geographic areas or 3D geometric planes can be modeled using a POLYGON geometry.

On the other hand, 3D-objects like pyramids, cubes, and other polyhedrons can be used with POLYHEDRALSURFACE. Polyhedrons can be formed by creating each of its sides or faces in the POLYGON format. (All the sides must connect together to form a closed object).

More complex surfaces or shapes can be created with QUADRATICSURFACE WKT or ST_MakeQSF() and ST_QSFMBRIntersects() TQL functions.

Smart Traffic Cameras

In the first small program, StreetLightApplication.java, all the rows in our database represent street cameras. The initial container of this application will be a collection. The @RowKey will be a STRING id. Every row in this collection contains the location, installation date, id, and other information of each camera.

Row Schema (StreetCamera.java)

public class StreetCamera {
  @RowKey
  public String cameraId;
  public Geometry coordinates;
  public Date installation;
  public int carsSeen;
  public int violations;
  //(snip)

When put into a real-world scenario, someone might be in charge of maintaining street cameras. This person may want to select cameras as a function of their location. This may translate to filtering for cameras that are in an exact location or within a geographic range or within an elevation range. Users might also want to create more complex spatial ranges that might not be easily visualized as squares or bounding boxes. All these different range queries can be executed using intersection statements.

Sample Code

Geometry point = Geometry.valueOf("POINT(40.98 76.43 1.6)");
geometryLogic.getSearchResults(point);
geometryLogic.searchZRange(0.8,0.3);
geometryLogic.searchArea(40,79,45,85,true);
geometryLogic.searchVolume(36,73,0.6,42,77,1.7,true);
//(snip)
double matrix[] = {0.8,0,0,0,0,0.8,0,0,0,0,0.8,0,0,0,0,-1};
geometryLogic.searchQuadraticSurface(matrix,true);

Helper Functions

As you can see from the code above, there are some predefined helper functions used by a helper object called a GeometryLogic. This object takes in a GridDB Collection and one of its Geometry columns as inputs. From there this object will use one of its many functions to issue spatial queries on that container.

Example Code

Collection<String,StreetCamera> streetCollection = gridstore.putCollection("TrafficApplication_101",StreetCamera.class);
GeometryLogic geometryLogic = new GeometryLogic(streetCollection,"coordinates");

Listed below are the class methods used by GeometryLogic.java that can be useful for issuing Geometry queries in GridDB. The details on their implementation and functionality are described in later sections.

  •  formPoint()
  •  searchArea()
  •  searchVolume()
  •  formSurfaceWkt()
  •  searchXRange(), searchYRange(), searchZRange()
  •  searchQuadraticSurface()
  •  searchPlane()

Searching for Points

The simplest query that might be made against a spatial database would be for points. Users may want to search for points with either 2 or 3 values — 2 values for two-dimensional points and 3 values for three-dimensional points. The method in GeometryLogic.java that uses this functionality is formPoint().

public Geometry formPoint(double x, double y){
  String wkt = String.format("POINT(%.9f %.9f)",x,y);
  Geometry point = Geometry.valueOf(wkt);
  return point;
}
public Geometry formPoint(double x, double y, double z){
  String wkt = String.format("POINT(%.9f %.9f %.9f)",x,y,z);
  Geometry point = Geometry.valueOf(wkt);
  return point;
}

Setting up a 3D Spatial Range

For this application, there are no real limits as to how far a user can query for in the x, y, and z directions. Since INF and -INF values are not allowed in GridDB, we substitute them with Double.MAX_VALUE and -1 * Double.MAX_VALUE for the default minimum and maximum values respectively; the ranges can then be modified by the users at will. For example, if you want to search for cameras that have a latitude greater than the 35.5°. The bounding box for your spatial range would have minimum x of 35.5 and have the maximum x, minimum x, minimum and maximum y, and minimum and maximum z values as their defaults. As stated before, bounding boxes are POLYHEDRALSURFACE. Its WKT form is

  • 1.79xe308 is the value for Double.MAX_VALUE)

Example Bounding Box

POLYHEDRALSURFACE(
((35.5 -1.79e308 -1.79e308,35.5 1.79e308 -1.79e308,1.79e308 1.79e308 -1.79e308,1.79e308 -1.79e308 -1.79e308,35.5 -1.79e308 -1.79e308)),
((35.5 -1.79e308 -1.79e308,35.5 1.79e308 -1.79e308,35.5 1.79e308 1.79e308,35.5 -1.79e308 1.79e308,35.5 -1.79e308 -1.79e308)),
((35.5 -1.79e308 -1.79e308,1.79e308 -1.79e308 -1.79e308,1.79e308 -1.79e308 1.79e308,35.5 -1.79e308 1.79e308,35.5 -1.79e308 -1.79e308)),
((1.79e308 1.79e308 1.79e308,1.79e308 -1.79e308 1.79e308,35.5 -1.79e308 1.79e308,35.5 1.79e308 1.79e308,1.79e308 1.79e308 1.79e308)),
((1.79e308 1.79e308 1.79e308,1.79e308 -1.79e308 1.79e308,1.79e308 -1.79e308 -1.79e308,1.79e308 1.79e308 -1.79e308,1.79e308 1.79e308 1.79e308)),
((1.79e308 1.79e308 1.79e308,1.79e308 1.79e308 -1.79e308,35.5 1.79e308 -1.79e308,35.5 1.79e308 1.79e308,1.79e308 1.79e308 1.79e308)))

There are many points to declare and organizing is a tedious task. The number of points to create this geometry is 30. In reality, you only really need 6 points. Luckily in this application, there is a class (located in GeometryLogic.java) with functions that can make this easier.

public String formSurfaceWkt(double minX,double minY,double minZ,double maxX,double maxY,double maxZ){
  String bottomFace = String.format("((%.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f))",minX,minY,minZ,minX,maxY,minZ,maxX,maxY,minZ,maxX,minY,minZ,minX,minY,minZ);
  String leftFace = String.format("((%.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f))",minX,minY,minZ,minX,maxY,minZ,minX,maxY,maxZ,minX,minY,maxZ,minX,minY,minZ);
  String frontFace = String.format("((%.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f, %.9f %.9f %.9f))",minX,minY,minZ,maxX,minY,minZ,maxX,minY,maxZ,minX,minY,maxZ,minX,minY,minZ);
  //(snip)
  String wkt = \ 
 String.format("POLYHEDRALSURFACE(%s,%s,%s,%s,%s,%s)",bottomFace,leftFace,frontFace,topFace,rightFace,backFace);
  return wkt;
}

Querying an Axis Range

Sometimes even having to create a surface with 6 points can be tedious. Other methods that the GeometryLogic class in this application can be used for is to simply set limits on the x,y, or z axises.

The function(s) setXRange(), setYRange(), or setZRange() can create spatial ranges with just one or two parameters. If you choose to use only one numerical value, you must specify an additional parameter in the form of an enum called BoundingOption.

BoundingOptions simply state whether the value should be set as a minimum or maximum for the bounding box. BoundingOption.GREATER means set the value as the range’s minimums. BoundingOption.LESSER means set the value as the range’s maximum.

For example, if you want to select only street cameras that have a latitude of 32° or greater, you would issue:

GeometryLogic geometryLogic = new GeometryLogic(collection,column);
geometryLogic.searchXRange(32,BoundingOption.GREATER);

If you want to select for cameras with latitudes of 32° or lesser, issue: 

geometryLogic.searchXRange(32,BoundingOption.LESSER);

In the case you want to set the minimum and the maximum of a range, call the function with two numerical values. If you want to select only street cameras that are between 116° and 132° longitude, execute: 

geometryLogic.searchYRange(116.0,132.0);

public void searchXRange(double firstX, double secondX) {
  Geometry box = Geometry.valueOf("LINESTRING(EMPTY)");
  if(firstX == secondX){
    getSearchResults(box);
    return;
  }
  double min = firstX;
  double max = secondX;
  if(firstX > secondX){
    min = secondX;
    max = firstX;
  }
  minY = min;
  maxY = max;
  box = formBoundingSurface(Dimension.THREE_DIMENSIONAL);
  getSearchResults(box);
  setInitialRanges();
}

Two-Dimensional Areas

Users may also want to filter for cameras that are within certain geographic ranges. These ranges might be used to filter for areas like cities, counties, streets, etc. Let’s say we want to select all cameras that are in a city that are between 40° and 45° latitude and 79° and 85° longitude. 

geometryLogic.searchArea(40,79,45,85,true);

All that is needed to execute this search is to create the POLYGON shape for the intersection queries are 4 unique values. Those 4 values will be used to make the corners of the area. From there all the corners can be connected together to make the closed shape. The method that implements this is formBoundingSquare().

public String formBoundingSquare(double minX, double minY, \ 
 double maxX, double maxY){
  String bottomLeft = String.format("%.9f %.9f",minX,minY);
  String bottomRight = String.format("%.9f %.9f",maxX,minY);
  String topLeft = String.format("%.9f %.9f",minX,maxY);
  String topRight = String.format("%.9f %.9f",maxX,maxY);
  String wkt =  String.format("POLYGON((%s,%s,%s,%s,%s))",
    bottomLeft,bottomRight,topRight,topLeft,bottomLeft);
  return wkt;
}

Searching Against a Geometric Plane

There are times when areas need to be in three dimensions. They also may not be constrained to four sided squares. You may want to search for cameras in an area that could form a triangle and is limited to a certain height. In this example, the height will be 0.6.

List<Double> planePoints = new ArrayList<Double>();

planePoints.add(38.23);
planePoints.add(74.34);
planePoints.add(0.6);
planePoints.add(43.11);
planePoints.add(78.67);
planePoints.add(0.6);
planePoints.add(40.98);
planePoints.add(76.43);
planePoints.add(0.6);

System.out.println();
geometryLogic.searchPlane(planePoints,Dimension.THREE_DIMENSIONAL,true);

public String formPlane(List<Double> points, Dimension dimension){
  String polygon = "POLYGON((";
  int stopIndex = 2;
  if(dimension == Dimension.THREE_DIMENSIONAL){
    stopIndex = 3;
  }
  double value;
  //(snip)
    value = (double) points.get(i);
  //(snip)
  return polygon;
}

Quadratic Queries

Generally the ranges that users may want to check against can be created by individual points. However, there will be times that the ranges may be formed by quadratic curves. As mentioned in the last post about Geometries, all that is needed is a 16-element matrix of numbers.

The below function, makeQSFTql() forms a geometric-intersection TQL query. Its inputs take in the matrix of coefficients and uses them to help make a QUADRATICSURFACE geometry on-the-fly. This geometry is the one that will be used to test for intersections.

public String formQSFTql(String column,double matrix[],boolean include){
  int max = 16;
  int stopIndex = matrix.length > 16 ? max : matrix.length;		
  String wkt = "";
  String exclude = include ? " " : " NOT";
  //(snip)
      wkt += (String.valueOf(matrix[i]));
  //(snip)
  }
  String tql = String.format("%s %s 
 ST_QSFMBRIntersects(ST_MakeQSF(%s),%s)", \ 
 QUERY_PREFIX,exclude,wkt,column);
  return tql;
}

Smart Buildings

The second program in the application folder deals with rooms in a building. All the rooms may have their own specific function that may need to be monitored. Some rooms that might have their own function in one area may relate to each other in some other area. One example could be a hotel suite building. Many rooms may share the same plumbing or electrical connections or circuitry. The way that these rooms intersect depend on how the building is laid out or what schematic is used. This makes being able to store spatial data and make spatial queries very useful.

Row Schema (SmartBuilding.java)

public class SmartBuilding {
  @RowKey
  public String buildingId;
  public Geometry layout;
  public double volume;
  public Date installation;
  public String name;
//(snip)

Intersection Queries (BuildingApplication.java)

geometryLogic.searchVolume(0,5,0,5,0,5,true);
geometryLogic.searchXRange(-5,-2);
geometryLogic.searchArea(10,0,15,12,true);
double matrix[] = {0.5,0,0,0,0.5,0,0,0,0.5,0,0,0,-1,0,0,0};		geometryLogic.searchQuadraticSurface(matrix,true);

IoT Pipe Monitors

Another small application that is provided is related to pipes. These pipes might be underground pipes that are monitored by a maintenance crew. The pipe geometries are represented as LINESTRING geometries. Data that could be relevant to these pipes might be their radius and their elevation below ground level. The row schema is located in PipeMonitor.java

Row Schema (PipeMonitor.java)

public class PipeMonitor {
  @RowKey
  public String id; // Sensor Tag and Container RowKey
  public Geometry pipe; // Multi-Part Vector 
  public Date installation; // When the sensor was installed and first connected to GridDB
  public double radius; // Radius of the pipe
  public double elevation; // Location in reference to sea level

Now that the schema is defined, the GridDB collection can be inserted. From there, users can search for pipes that are within a certain area. They also are able to search for pipes by their individual geometries or elevations.

Sample Queries

Collection<String,PipeMonitor> pipeCollection = gridstore.putCollection(name,PipeMonitor.class);
Query<PipeMonitor> apiQuery = pipeCollection.query(column,Geometry.valueOf("LINESTRING(1 1 1, 1 0 1, 1 0 0)"),GeometryOperator.INTERSECT);
//(snip)
String tql = "SELECT * WHERE NOT ST_MBRIntersects(pipe,ST_GeomFromText('LINESTRING(7 2 0, 7 2 4)')) AND elevation > -5.3";

Traffic Camera Timeseries

This timeseries program uses Geometry values to represent areas of a camera’s image. Moreover, this container can be thought of containing the images that a camera has taken of traffic over time. Each row in this timeseries contains the timestamp of the image, the image’s url and its data, the Geometric area of traffic the image represents, the amount of cars in the image, and the elevation the image was taken at it. The row schema file is TrafficCamera.java.

Row Schema

public class TrafficCamera {
  @RowKey
  public Date timestamp;
  public String id;
  public String url;
  public Geometry area;
  public int cars;
  public double elevation;
  public Blob image;

Once all the TimeSeries rows have been inserted into GridDB, spatial queries can be made. Most of all, users will then create 2D areas to see any of the image’s areas that intersect.

Intersection Queries

String name = "CameraTs121";
TimeSeries<TrafficCamera> timeseries = gridstore.putTimeSeries(name,TrafficCamera.class);
String tql = "SELECT * WHERE ST_MBRIntersects(area,ST_GeomFromText('POLYGON((8 8, 9 8, 9 9, 8 9, 8 8))'))";
Query<TrafficCamera> query = timeseries.query(tql);

Source Code

All the code used for this post is available for download below.
geometry-sample.tar.gz

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.