geo_near
Determines geospatial objects in proximity to a
point.
boolean geo_near(any*, any*, double)
The first and the second parameters any*
can be any
geometric object.
The function determines if the first geometry is within a distance of N meters from the second geometry.
If any of the two parameters does not return a single valid geometry object, and if it can be detected at compile time then the function raises an error.
The runtime behavior is as follows:
- Returns false if any parameter returns 0 or more than 1 item.
- Returns NULL if any of the first two parameters returns NULL.
- Returns false if any of the first two parameters returns an item that is not a valid geometry object.
Finally, if both of the first two parameters return a single geometry object each, 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 parameter; otherwise
false.
Note:
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.Example 1: Is there a hospital within 3km of the given
location?
SELECT
t.poi.name AS hospital_name,
t.poi.address.street AS hospital_address
FROM PointsOfInterest t
WHERE t.poi.kind = "hospital"
AND
geo_near(
t.poi.location,
{"type" : "point",
"coordinates" : [-122.03493933105469,37.32949164059004]
},
3000
);
Explanation:
- You query the
PointsOfInterest
table to filter the rows for hospital. - You use the
geo_near
function to filter hospitals within 3000m of the given location.
Result:
{"hospital_name":"St. Marthas hospital","hospital_address":"18000 West Blvd"}
{"hospital_name":"Memorial hospital","hospital_address":"10500 South St"}
Example 2: How far is a gas station within the next one mile from the given
location?
SELECT
t.poi.address.street AS gas_station_address,
geo_distance(
t.poi.location,
{
"type" : "point",
"coordinates" : [-121.90768646240233,37.292081740702365]
}
) AS distance_in_meters
FROM PointsOfInterest t
WHERE t.poi.kind = "gas station" AND
geo_near(
t.poi.location,
{
"type" : "point",
"coordinates" : [-121.90768646240233,37.292081740702365]
},
1600
);
Explanation:
- You query the
PointsOfInterest
table to filter the rows for gas station. - You use the
geo_near
function to filter gas stations within one mile(1600m) of the given location. - You also fetch the actual distance between your location and the gas station
using the
geo_distance
function.
Result:
{"gas_station_address":"33 North Avenue","distance_in_meters":886.7004173859665}
The actual distance to the nearest gas station within the next mile is 886m.