RDBMS : JDOQL Spatial Methods

When querying spatial data you can make use of a set of spatial methods on the various Java geometry types. The list contains all of the functions detailed in Section 3.2 of the OGC Simple Features specification. Additionally DataNucleus provides some commonly required methods like bounding box test and datastore specific functions. The following tables list all available functions as well as information about which RDBMS implement them. An entry in the "Result" column indicates, whether the funcion may be used in the result part of a JDOQL query.

Functions for Constructing a Geometry Value given its Well-known Text Representation (OGC SF 3.2.6)


Method Description Specification Result [1] PostGIS MySQL Oracle Spatial
Spatial.geomFromText(String, Integer) Construct a Geometry value given its well- known textual representation. OGC SF
Spatial.pointFromText(String, Integer) Construct a Point. OGC SF
Spatial.lineFromText(String, Integer) Construct a LineString. OGC SF
Spatial.polyFromText(String, Integer) Construct a Polygon. OGC SF
Spatial.mPointFromText(String, Integer) Construct a MultiPoint. OGC SF
Spatial.mLineFromText(String, Integer) Construct a MultiLineString. OGC SF
Spatial.mPolyFromText(String, Integer) Construct a MultiPolygon. OGC SF
Spatial.geomCollFromText(String, Integer) Construct a GeometryCollection. OGC SF

[1] These functions can't be used in the return part because it's not possible to determine the return type from the parameters.


Functions for Constructing a Geometry Value given its Well-known Binary Representation
(OGC SF 3.2.7)

Method Description Specification Result [1] PostGIS MySQL Oracle Spatial
Spatial.geomFromWKB(String, Integer) Construct a Geometry value given its well-known binary representation. OGC SF
Spatial.pointFromWKB(String, Integer) Construct a Point. OGC SF
Spatial.lineFromWKB(String, Integer) Construct a LineString. OGC SF
Spatial.polyFromWKB(String, Integer) Construct a Polygon. OGC SF
Spatial.mPointFromWKB(String, Integer) Construct a MultiPoint. OGC SF
Spatial.mLineFromWKB(String, Integer) Construct a MultiLineString. OGC SF
Spatial.mPolyFromWKB(String, Integer) Construct a MultiPolygon. OGC SF
Spatial.geomCollFromWKB(String, Integer) Construct a GeometryCollection. OGC SF

[1] These functions can't be used in the return part because it's not possible to determine the return type from the parameters.


Functions on Type Geometry
(OGC SF 3.2.10)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.dimension(Geometry) Returns the dimension of the Geometry. OGC SF
Spatial.geometryType(Geometry) Returns the name of the instantiable subtype of Geometry. OGC SF
Spatial.asText(Geometry) Returns the well-known textual representation. OGC SF
Spatial.asBinary(Geometry) Returns the well-known binary representation. OGC SF
Spatial.srid(Geometry) Returns the Spatial Reference System ID for this Geometry. OGC SF
Spatial.isEmpty(Geometry) TRUE if this Geometry corresponds to the empty set. OGC SF [1] [2]
Spatial.isSimple(Geometry) TRUE if this Geometry is simple, as defined in the Geometry Model. OGC SF [1] [2]
Spatial.boundary(Geometry) Returns a Geometry that is the combinatorial boundary of the Geometry. OGC SF [2]
Spatial.envelope(Geometry) Returns the rectangle bounding Geometry as a Polygon. OGC SF

[1] Oracle does not allow boolean expressions in the SELECT-list.
[2] MySQL does not implement these functions.


Functions on Type Point
(OGC SF 3.2.11)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.x(Point) Returns the x-coordinate of the Point as a Double. OGC SF
Spatial.y(Point) Returns the y-coordinate of the Point as a Double. OGC SF

Functions on Type Curve
(OGC SF 3.2.12)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.startPoint(Curve)) Returns the first point of the Curve. OGC SF
Spatial.endPoint(Curve)) Returns the last point of the Curve. OGC SF
Spatial.isRing(Curve) Returns TRUE if Curve is closed and simple. . OGC SF [1] [2]

[1] Oracle does not allow boolean expressions in the SELECT-list.
[2] MySQL does not implement these functions.


Functions on Type Curve and Type MultiCurve
(OGC SF 3.2.12, 3.2.17)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.isClosed(Curve)
Spatial.isClosed(MultiCurve)
Returns TRUE if Curve is closed, i.e., if StartPoint(Curve) = EndPoint(Curve). OGC SF [1]
Spatial.length(Curve)
Spatial.length(MultiCurve)
Returns the length of the Curve. OGC SF

[1] Oracle does not allow boolean expressions in the SELECT-list.


Functions on Type LineString
(OGC SF 3.2.13)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.numPoints(LineString) Returns the number of points in the LineString. OGC SF
Spatial.pointN(LineString, Integer) Returns Point n. OGC SF

Functions on Type Surface and Type MultiSurface
(OGC SF 3.2.14, 3.2.18)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.centroid(Surface)
centroid(MultiSurface)
Returns the centroid of Surface, which may lie outside of it. OGC SF [1]
Spatial.pointOnSurface(Surface)
pointOnSurface(MultiSurface)
Returns a Point guaranteed to lie on the surface. OGC SF [1]
Spatial.area(Surface)
area(MultiSurface)
Returns the area of Surface. OGC SF

[1] MySQL does not implement these functions.


Functions on Type Polygon
(OGC SF 3.2.15)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.exteriorRing(Polygon) Returns the exterior ring of Polygon. OGC SF
Spatial.numInteriorRing(Polygon) Returns the number of interior rings. OGC SF
Spatial.interiorRingN(Polygon, Integer) Returns the nth interior ring. OGC SF

Functions on Type GeomCollection
(OGC SF 3.2.16)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.numGeometries(GeomCollection) Returns the number of geometries in the collection. OGC SF
Spatial.geometryN(GeomCollection, Integer) Returns the nth geometry in the collection. OGC SF

Functions that test Spatial Relationships
(OGC SF 3.2.19)

Method Description Specification Result [1] PostGIS MySQL Oracle Spatial
Spatial.equals(Geometry, Geometry) TRUE if the two geometries are spatially equal. OGC SF [2]
Spatial.disjoint(Geometry, Geometry) TRUE if the two geometries are spatially disjoint. OGC SF [2]
Spatial.touches(Geometry, Geometry) TRUE if the first Geometry spatially touches the other Geometry. OGC SF [2]
Spatial.within(Geometry, Geometry) TRUE if first Geometry is completely contained in second Geometry. OGC SF [2]
Spatial.overlaps(Geometry, Geometry) TRUE if first Geometries is spatially overlapping the other Geometry. OGC SF [2]
Spatial.crosses(Geometry, Geometry) TRUE if first Geometry crosses the other Geometry. OGC SF [3]
Spatial.intersects(Geometry, Geometry) TRUE if first Geometry spatially intersects the other Geometry. OGC SF [2]
Spatial.contains(Geometry, Geometry) TRUE if second Geometry is completely contained in first Geometry. OGC SF [2]
Spatial.relate(Geometry, Geometry, String) TRUE if the spatial relationship specified by the patternMatrix holds. OGC SF [3]

[1] Oracle does not allow boolean expressions in the SELECT-list.
[2] MySQL does not implement these functions according to the specification.They return the same result as the corresponding MBR-based functions.
[3] MySQL does not implement these functions.


Function on Distance Relationships
(OGC SF 3.2.20)

Method Description Specification Result PostGIS MySQL Oracle Spatial
Spatial.distance(Geometry, Geometry) Returns the distance between the two geometries. OGC SF [1]

[1] MySQL does not implement this function.


Functions that implement Spatial Operators
(OGC SF 3.2.21)

Method Description Specification Result PostGIS MySQL [1] Oracle Spatial
Spatial.intersection(Geometry, Geometry) Returns a Geometry that is the set intersection of the two geometries. OGC SF
Spatial.difference(Geometry, Geometry) Returns a Geometry that is the closure of the set difference of the two geometries. OGC SF
Spatial,union(Geometry, Geometry) Returns a Geometry that is the set union of the two geometries. OGC SF
Spatial.symDifference(Geometry, Geometry) Returns a Geometry that is the closure of the set symmetric difference of the two geometries. OGC SF
Spatial.buffer(Geometry, Double) Returns as Geometry defined by buffering a distance around the Geometry. OGC SF
Spatial.convexHull(Geometry) Returns a Geometry that is the convex hull of the Geometry. OGC SF

[1] These functions are currently not implemented in MySQL.They may appear in future releases.


Test whether the bounding box of one geometry intersects the bounding box of another

Method Description Result PostGIS MySQL Oracle Spatial
Spatial.bboxTest(Geometry, Geometry) Returns TRUE if if the bounding box of the first Geometry overlaps second Geometry's bounding box [1]

[1] Oracle does not allow boolean expressions in the SELECT-list.


PostGIS Spatial Operators

These functions are only supported on PostGIS.

Method Description Result
PostGIS.bboxOverlapsLeft(Geometry, Geometry) The PostGIS &< operator returns TRUE if the bounding box of the first Geometry overlaps or is to the left of second Geometry's bounding box
PostGIS.bboxOverlapsRight(Geometry, Geometry) The PostGIS &> operator returns TRUE if the bounding box of the first Geometry overlaps or is to the right of second Geometry's bounding box
PostGIS.bboxLeft(Geometry, Geometry) The PostGIS << operator returns TRUE if the bounding box of the first Geometry overlaps or is strictly to the left of second Geometry's bounding box
PostGIS.bboxRight(Geometry, Geometry) The PostGIS >> operator returns TRUE if the bounding box of the first Geometry overlaps or is strictly to the right of second Geometry's bounding box
PostGIS.bboxOverlapsBelow(Geometry, Geometry) The PostGIS &<@ operator returns TRUE if the bounding box of the first Geometry overlaps or is below second Geometry's bounding box
PostGIS.bboxOverlapsAbove(Geometry, Geometry) The PostGIS |&> operator returns TRUE if the bounding box of the first Geometry overlaps or is above second Geometry's bounding box
PostGIS.bboxBelow(Geometry, Geometry) The PostGIS <<| operator returns TRUE if the bounding box of the first Geometry is strictly below second Geometry's bounding box
PostGIS.bboxAbove(Geometry, Geometry) The PostGIS |>> operator returns TRUE if the bounding box of the first Geometry is strictly above second Geometry's bounding box
PostGIS.sameAs(Geometry, Geometry) The PostGIS ~= operator returns TRUE if the two geometries are vertex-by-vertex equal.
PostGIS.bboxWithin(Geometry, Geometry) The PostGIS @ operator returns TRUE if the bounding box of the first Geometry overlaps or is completely contained by second Geometry's bounding box
PostGIS.bboxContains(Geometry, Geometry) The PostGIS ~ operator returns TRUE if the bounding box of the first Geometry completely contains second Geometry's bounding box

MySQL specific Functions for Testing Spatial Relationships between Minimal Bounding Boxes

These functions are only supported on MySQL.

Method Result
MySQL.mbrEqual(Geometry, Geometry)
MySQL.mbrDisjoint(Geometry, Geometry)
MySQL.mbrIntersects(Geometry, Geometry)
MySQL.mbrTouches(Geometry, Geometry)
MySQL.mbrWithin(Geometry, Geometry)
MySQL.mbrContains(Geometry, Geometry)
MySQL.mbrOverlaps(Geometry, Geometry)

Oracle specific Functions for Constructing SDO_GEOMETRY types

These functions are only supported on Oracle Spatial.

Method Desription
Oracle.sdo_geometry(
Integer gtype,
Integer srid,
SDO_POINT point,
SDO_ELEM_INFO_ARRAY elem_info,
SDO_ORDINATE_ARRAY ordinates)
Creates a SDO_GEOMETRY geometry from the passed geometry type, srid, point, element infos and ordinates.
Oracle.sdo_point_type(
Double x, Double y, Double z)
Creates a SDO_POINT geometry from the passed ordinates.
Oracle.sdo_elem_info_array(
String numbers)
Creates a SDO_ELEM_INFO_ARRAY from the passed comma-separeted integers.
Oracle.sdo_ordinate_array(
String ordinates)
Creates a SDO_ORDINATE_ARRAY from the passed comma-separeted doubles.

Examples

The following sections provide some examples of what can be done using spatial methods in JDOQL queries. In the examples we use a class from the test suite. Here's the source code for reference:

package org.datanucleus.samples.pggeometry;
                
import org.postgis.LineString;
                
public class SampleLineString {			
    private long id;
    private String name;
    private LineString geom;
                
    public SampleLineString(long id, String name, LineString lineString) {
        this.id = id;
        this.name = name;
        this.geom = lineString;
    }
                
    public long getId() {
       return id;
    }
    ....
}
<jdo>
    <package name="org.datanucleus.samples.pggeometry">	
        <extension vendor-name="datanucleus" key="spatial-dimension" value="2"/>
		<extension vendor-name="datanucleus" key="spatial-srid" value="4326"/>
		
		<class name="SampleLineString" table="samplepglinestring" detachable="true">
			<field name="id"/>
			<field name="name"/>
			<field name="geom" persistence-modifier="persistent"/>
		</class>
	</package>
</jdo>

Example 1 - Spatial Function in the Filter of a Query

This example shows how to use spatial functions in the filter of a query. The query returns a list of SampleLineStrings whose line string has a length less than the given limit.

Double limit = new Double(100.0);
Query query = pm.newQuery(SampleLineString.class, "geom != null && Spatial.length(geom) < :limit");
List list = (List) query.execute(limit);

Example 2 - Spatial Function in the Result Part of a Query

This time we use a spatial function in the result part of a query. The query returns the length of the line string from the selected SampleLineString

query = pm.newQuery(SampleLineString.class, "id == :id");
query.setResult("Spatial.pointN(geom, 2)");
query.setUnique(true);
Geometry point = (Geometry) query.execute(new Long(1001));

Example 3 - Nested Functions

You may want to use nested functions in your query. This example shows how to do that. The query returns a list of SampleLineStrings, whose end point spatially equals a given point.

Point point = new Point("SRID=4326;POINT(110 45)");
Query query = pm.newQuery(SampleLineString.class, "geom != null && Spatial.equals(Spatial.endPoint(geom), :point)");
List list = (List) query.execute(point);