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 JPQL 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] | |||
Spatial.isSimple(Geometry) | TRUE if this Geometry is simple, as defined in the Geometry Model. | OGC SF | [1] | |||
Spatial.boundary(Geometry) | Returns a Geometry that is the combinatorial boundary of the Geometry. | OGC SF | ||||
Spatial.envelope(Geometry) | Returns the rectangle bounding Geometry as a Polygon. | OGC SF |
[1] Oracle does not allow boolean expressions in the SELECT-list.
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] |
[1] Oracle does not allow boolean expressions in the SELECT-list.
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 |
[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.
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] MariaDB 5.3.3+ implements this.
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 | Desription | 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 Geospatial.
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 JPQL queries. In the examples we use a class from the test suite. Here's the source code for reference:
package mydomain.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; } .... }
<entity-mappings> <package>mydomain.samples.pggeometry</package> <entity class="mydomain.samples.pggeometry.SampleLineString"> <extension vendor-name="datanucleus" key="spatial-dimension" value="2"/> <extension vendor-name="datanucleus" key="spatial-srid" value="4326"/> <attributes> <id name="id"/> <basic name="name"/> <basic name="geom"> <extension vendor-name="datanucleus" key="mapping" value="no-userdata"/> [2] </basic> </attributes> </entity> </entity-mappings>
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.
Query q = em.createQuery("SELECT s FROM SampleLineString s WHERE geom IS NOT NULL AND Spatial.length(geom) < :limit"); q.setParameter("limit", new Double(100.0)); List list = q.getResultList();
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
q = em.createQuery("SELECT Spatial.pointN(geom, 2) FROM SampleLineString s WHERE id == :id"); q.setParameter("id", new Long(1001)); Geometry point = q.getSingleResult();
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 q = em.createQuery("SELECT s FROM SampleLineString s WHERE geom IS NOT NULL AND Spatial.equals(Spatial.endPoint(geom), :point)"); q.setParameter("point", point); List list = q.getResultList();