GridDB SE<\/a> 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<\/a> — we’re going to be demonstrating a few more real world examples in this one.<\/p>\n 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. <\/p>\n 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<\/i>, using the ST_MBRIntersects()<\/i> 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. <\/p>\n 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.<\/p>\n The most consistent way to check if any camera locations are within certain geometric range is by forming a POLYHEDRALSURFACE<\/i>, then inserting that geometry into a TQL query. Begin by declaring the geometry in its WKT<\/i> (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<\/i> 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: In these applications, the types of geometries used for querying were: <\/p>\n You should model individual locations or points with the Geographic areas or 3D geometric planes can be modeled using a On the other hand, 3D-objects like pyramids, cubes, and other polyhedrons can be used with More complex surfaces or shapes can be created with In the first small program, StreetLightApplication.java<\/b>, all the rows in our database represent street cameras. The initial container of this application will be a collection. The Row Schema<\/b> (StreetCamera.java)<\/p>\n 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.<\/p>\n Sample Code<\/b><\/p>\n As you can see from the code above, there are some predefined helper functions used by a helper object called a Example Code<\/b><\/p>\n Listed below are the class methods used by GeometryLogic.java<\/b> that can be useful for issuing Geometry queries in GridDB. The details on their implementation and functionality are described in later sections.<\/p>\n <\/p>\n 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<\/b> that uses this functionality is For this application, there are no real limits as to how far a user can query for in the x<\/i>, y<\/i>, and z<\/i> directions. Since INF<\/b> and -INF<\/b> values are not allowed in GridDB, we substitute them with Example Bounding Box<\/b><\/p>\n 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.<\/p>\n Sometimes even having to create a surface with 6 points can be tedious. Other methods that the GeometryLogic<\/i> class in this application can be used for is to simply set limits on the x,y, or z axises. <\/p>\n The function(s) BoundingOptions<\/i> simply state whether the value should be set as a minimum or maximum for the bounding box. For example, if you want to select only street cameras that have a latitude of 32° or greater, you would issue:<\/p>\n If you want to select for cameras with latitudes of 32° or lesser, issue: <\/p>\n 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: <\/p>\n 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. <\/p>\n All that is needed to execute this search is to create the 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.<\/p>\nCreating Spatial Queries<\/h2>\n
POINT(36.48 73.34 16)<\/code>. Since the geometry is now established, you can issue the below TQL query.<\/p>\n\nSELECT * WHERE ST_MBRIntersects(geometry_column,ST_GeomFromText(\u00e2\u20ac\u02dcPOINT(36.48 73.34 16)\u00e2\u20ac\u2122))\n<\/pre>\n
Filtering for Points or Areas<\/h2>\n
\n
POINT<\/code> geometry.<\/p>\nPOLYGON<\/code> geometry. <\/p>\nPOLYHEDRALSURFACE<\/code>. Polyhedrons can be formed by creating each of its sides or faces in the POLYGON<\/code> format. (All the sides must connect together to form a closed object). <\/p>\nQUADRATICSURFACE<\/code> WKT or ST_MakeQSF()<\/code> and ST_QSFMBRIntersects()<\/code> TQL functions.<\/p>\nSmart Traffic Cameras<\/h2>\n
@RowKey<\/code> will be a STRING id. Every row in this collection contains the location, installation date, id, and other information of each camera. <\/p>\n\npublic class StreetCamera {\n @RowKey\n public String cameraId;\n public Geometry coordinates;\n public Date installation;\n public int carsSeen;\n public int violations;\n \/\/(snip)\n<\/pre>\n\nGeometry point = Geometry.valueOf(\"POINT(40.98 76.43 1.6)\");\ngeometryLogic.getSearchResults(point);\ngeometryLogic.searchZRange(0.8,0.3);\ngeometryLogic.searchArea(40,79,45,85,true);\ngeometryLogic.searchVolume(36,73,0.6,42,77,1.7,true);\n\/\/(snip)\ndouble matrix[] = {0.8,0,0,0,0,0.8,0,0,0,0,0.8,0,0,0,0,-1};\ngeometryLogic.searchQuadraticSurface(matrix,true);\n<\/pre>\nHelper Functions<\/h2>\n
GeometryLogic<\/code>. This object takes in a GridDB Collection<\/i> and one of its Geometry<\/i> columns as inputs. From there this object will use one of its many functions to issue spatial queries on that container.<\/p>\n\nCollection<String,StreetCamera> streetCollection = gridstore.putCollection(\"TrafficApplication_101\",StreetCamera.class);\nGeometryLogic geometryLogic = new GeometryLogic(streetCollection,\"coordinates\");\n<\/pre>\n
\n
Searching for Points<\/h4>\n
formPoint()<\/code>.<\/p>\n\npublic Geometry formPoint(double x, double y){\n String wkt = String.format(\"POINT(%.9f %.9f)\",x,y);\n Geometry point = Geometry.valueOf(wkt);\n return point;\n}\npublic Geometry formPoint(double x, double y, double z){\n String wkt = String.format(\"POINT(%.9f %.9f %.9f)\",x,y,z);\n Geometry point = Geometry.valueOf(wkt);\n return point;\n}\n<\/pre>\nSetting up a 3D Spatial Range<\/h4>\n
Double.MAX_VALUE<\/code> and -1 * Double.MAX_VALUE<\/code> 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<\/code>. Its WKT form is <\/p>\n\n
\nPOLYHEDRALSURFACE(\n((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)),\n((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)),\n((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)),\n((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)),\n((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)),\n((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)))\n<\/pre>\n
\npublic String formSurfaceWkt(double minX,double minY,double minZ,double maxX,double maxY,double maxZ){\n 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);\n 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);\n 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);\n \/\/(snip)\n String wkt = \n String.format(\"POLYHEDRALSURFACE(%s,%s,%s,%s,%s,%s)\",bottomFace,leftFace,frontFace,topFace,rightFace,backFace);\n return wkt;\n}\n<\/pre>\nQuerying an Axis Range<\/h4>\n
setXRange()<\/code>, setYRange()<\/code>, or setZRange()<\/code> 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<\/i> called BoundingOption<\/code>. <\/p>\nBoundingOption.GREATER<\/code> means set the value as the range’s minimums. BoundingOption.LESSER<\/code> means set the value as the range’s maximum. <\/p>\n\nGeometryLogic geometryLogic = new GeometryLogic(collection,column);\ngeometryLogic.searchXRange(32,BoundingOption.GREATER);\n<\/pre>\n
\ngeometryLogic.searchXRange(32,BoundingOption.LESSER);\n<\/pre>\n
\ngeometryLogic.searchYRange(116.0,132.0);\n<\/pre>\n
\npublic void searchXRange(double firstX, double secondX) {\n Geometry box = Geometry.valueOf(\"LINESTRING(EMPTY)\");\n if(firstX == secondX){\n getSearchResults(box);\n return;\n }\n double min = firstX;\n double max = secondX;\n if(firstX > secondX){\n min = secondX;\n max = firstX;\n }\n minY = min;\n maxY = max;\n box = formBoundingSurface(Dimension.THREE_DIMENSIONAL);\n getSearchResults(box);\n setInitialRanges();\n}\n<\/pre>\nTwo-Dimensional Areas<\/h4>\n
\ngeometryLogic.searchArea(40,79,45,85,true);\n<\/pre>\n
POLYGON<\/code> 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()<\/code>.<\/p>\n\npublic String formBoundingSquare(double minX, double minY, \n double maxX, double maxY){\n String bottomLeft = String.format(\"%.9f %.9f\",minX,minY);\n String bottomRight = String.format(\"%.9f %.9f\",maxX,minY);\n String topLeft = String.format(\"%.9f %.9f\",minX,maxY);\n String topRight = String.format(\"%.9f %.9f\",maxX,maxY);\n String wkt = String.format(\"POLYGON((%s,%s,%s,%s,%s))\",\n bottomLeft,bottomRight,topRight,topLeft,bottomLeft);\n return wkt;\n}\n<\/pre>\nSearching Against a Geometric Plane<\/h4>\n
\nList<Double> planePoints = new ArrayList<Double>();\n\nplanePoints.add(38.23);\nplanePoints.add(74.34);\nplanePoints.add(0.6);\nplanePoints.add(43.11);\nplanePoints.add(78.67);\nplanePoints.add(0.6);\nplanePoints.add(40.98);\nplanePoints.add(76.43);\nplanePoints.add(0.6);\n\nSystem.out.println();\ngeometryLogic.searchPlane(planePoints,Dimension.THREE_DIMENSIONAL,true);\n<\/pre>\n
\npublic String formPlane(List<Double> points, Dimension dimension){\n String polygon = \"POLYGON((\";\n int stopIndex = 2;\n if(dimension == Dimension.THREE_DIMENSIONAL){\n stopIndex = 3;\n }\n double value;\n \/\/(snip)\n value = (double) points.get(i);\n \/\/(snip)\n return polygon;\n}\n<\/pre>\nQuadratic Queries<\/h4>\n