Functions on GeoJson Data

The GeoJson specification defines the structure and content of JSON objects that are supposed to represent geographical shapes on earth (called geometries). The following functions interpret the JSON objects as geometries and allow the search for rows containing geometries that satisfy certain conditions.

For more information on the functions and examples, see Managing GeoJSON data in the Developers Guide.

boolean geo_intersect(any*, any*)

Raises an error if it can be detected at compile time that an operand will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

• Returns false if any operand returns 0 or more than 1 items.
• Returns NULL if any operand returns NULL.
• Returns false if any operand returns an item that is not a valid geometry object.

Finally, if both operands return a single geometry object, it returns true if the 2 geometries have any points in common; otherwise false.

boolean geo_inside(any*, any*)

Raises an error if it can be detected at compile time that an operand will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

• Returns false if any operand returns 0 or more than 1 items.
• Returns NULL if any operand returns NULL.
• Returns false if any operand returns an item that is not a valid geometry object (however, if it can be detected at compile time that an operand will not return a valid geometry, an error is raised).
• Returns false if the second operand returns a geometry object that is not a polygon.

Finally, if both operands return a single geometry object and the second geometry is a polygon, it returns true if the first geometry is completely contained inside the second polygon, i.e., all its points belong to the interior of the polygon; otherwise false. The interior of a polygon is all the points in the polygon area except the points on the linear rings that define the polygon’s boundary.

boolean geo_within_distance(any*, any*, double)

Raises an error if it can be detected at compile time that any of the first two operands will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

• Returns false if any of the first two operands returns 0 or more than 1 items.
• Returns NULL if any of the first two operands returns NULL.
• Returns false if any of the first two operands returns an item that is not a valid geometry object.

Finally, if both of the first two operands return a single geometry object, it returns true if the first geometry is within a distance of N meters from the second geometry, where N is the number returned by the third operand; otherwise false. The distance between 2 geometries is defined as the minimum among the distances of any pair of points where the first point belongs to the first geometry and the second point to the second geometry. If N is a negative number, it is set to 0.

boolean geo_near(any*, any*, double)

geo_near is converted internally to geo_within_distance plus an (implicit) order-by the distance between the two geometries. However, if the query has an (explicit) order-by already, no ordering by distance is performed. The geo_near function can appear in the WHERE clause only, where it must be a top-level predicate, i.e, not nested under an OR or NOT operator.

double geo_distance(any*, any*)

Raises an error if it can be detected at compile time that an operand will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

• Returns -1 if any operand returns zero or more than 1 items.
• Returns NULL if any operand returns NULL.
• Returns -1 if any of the operands is not a geometry.

Otherwise it returns the geodetic distance between the 2 input geometries. The returned distance is the minimum among the distances of any pair of points where the first point belongs to the first geometry and the second point to the second geometry. Between two such points, their distance is the length of the geodetic line that connects the points.

boolean geo_is_geometry(any*)

• Returns false if the operand returns zero or more than 1 items.
• Returns NULL if the operand returns NULL.
• Returns true if the input is a single valid geometry object. Otherwise, false.