Archive
Archive
DWITHIN() — Returns true or false depending whether two geospatial entities are within a specified distance of each other.
DWITHIN( polygon-or-point, polygon-or-point, distance )
The DWITHIN() function determines if two geospatial values are within the specified distance of each other. The values can be two points (GEOGRAPHY_POINT) or a point and a polygon (GEOGRAPHY). The maximum distance is specified as a numeric value measured in meters. If the distance between the two geospatial values is less than or equal to the specified distance, the function returns true. If not, it returns false.
The DWITHIN() function accepts multiple datatypes for its first two arguments, but there are constraints on which combination of datatypes are allowed. For example, the two arguments cannot both be of type GEOGRAPHY. Consequently, the VoltDB planner must know the datatype of the arguments when the statement is compiled. So using generic, untyped placeholders for these arguments is not valid. This means you cannot use syntax such as DWITHIN(?,?,?) in a stored procedure. However, you can use placeholders as long as they are cast to specific types. For example:
DWITHIN(POINTFROMTEXT(?),POLYGONFROMTEXT(?), ?)
The following example finds all the cities within five kilometers of a given user, by evaluating the distance between the GEOGRAPHY_POINT column user.loc and the GEOGRAPHY column city.boundary.
SELECT user.name, city.name, DISTANCE(user.loc, city.boundary) FROM user, city WHERE user.id=? AND DWITHIN(user.loc, city.boundary, 5000) ORDER BY DISTANCE(user.loc, city.boundary) ASC;
The next is a more generalized example, where the query returns all delivery trucks within a specified distance of a store, where both the distance and the store ID are parameterized and can be input at runtime.
V12.x DocumentationSELECT store.address, truck.license_number,
DISTANCE(store.loc, truck.loc)/1000 AS distance_in_km
FROM store, truck
WHERE DWITHIN(store.loc, truck.loc, ?) and store.id=?
ORDER BY DISTANCE(store.loc,truck.loc)/1000 ASC;