Archive
Archive
DISTANCE() — Returns the distance between two points or a point and a polygon.
DISTANCE( point-or-polygon, point-or-polygon )
The DISTANCE() function returns the distance, measured in meters, between two points or a point and a polygon. The arguments to the function can be either two GEOGRAPHY_POINT values or a GEOGRAPHY_POINT and GEOGRAPHY value.
The DISTANCE() function accepts multiple datatypes for its 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 DISTANCE(?,?) in a stored procedure. However, you can use placeholders as long as they are cast to specific types. For example:
DISTANCE(POINTFROMTEXT(?),POLYGONFROMTEXT(?))
The following example finds the closest city to a specified user, using the GEOGRAPHY_POINT column user.location and the GEOGRAPHY column city.boundary.
V7.x DocumentationSELECT TOP 1 user.name, city.name,
DISTANCE(user.location, city.boundary)
FROM user, city WHERE user.id = ?
ORDER BY DISTANCE(user.location, city.boundary) ASC;The next example finds the distance in kilometers from a truck to stores, listed in order with closest first, using the two GEOGRAPHY_POINT columns truck.loc and store.loc.
SELECT store.address,
DISTANCE(store.loc,truck.loc) / 1000 AS distance
FROM store, truck WHERE truck.id = ?
ORDER BY DISTANCE(store.loc,truck.loc)/1000 ASC;