geo_inside
Determines geometries within a bounding GeoJSON
geometry.
boolean geo_inside(any*, any*)
- The first parameter
any*
can be any geometric object. - The second parameter
any*
needs to be a polygon.
The function determines if the geometry pointed by the first parameter is completely contained inside the polygon pointed by the second parameter.
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 parameter returns NULL.
- Returns false if any parameter (at runtime) returns an item that is not a valid geometry object.
- Returns false if the second parameter returns a geometry object that is not a polygon.
- If both parameters return a single geometry object each 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.
- Else it returns false.
Note:
The interior of a polygon is all the points in the polygon area except the points on the linear ring that define the polygon’s boundary.Example: Look for nature parks in Northern
California.
SELECT t.poi.name AS park_name,
t.poi.address.street AS park_location
FROM PointsOfInterest t
WHERE t.poi.kind = "nature park"
AND geo_inside(t.poi.location,
{ "type" : "polygon",
"coordinates": [[
[-120.1135253906249, 36.99816565700228],
[-119.0972900390625, 37.391981943533544],
[-119.2840576171875, 37.97451499202459],
[-120.2069091796874, 38.035112420612975],
[-122.3822021484375, 37.74031329210266],
[-122.2283935546875, 37.15156050223665],
[-121.5362548828124, 36.85325222344018],
[-120.1135253906249, 36.99816565700228]
]]
});
Explanation:
- You query the
PointsOfInterest
table to filter the rows for nature park. - You specify a polygon as the second parameter to the
geo_inside
function. - The coordinates of the polygon you specify correspond to the coordinates of the northern portion of the state of California in the U.S.
- The
geo_inside
function only returns rows when the location of the nature park is completely contained inside the location points specified.
Result:
{"park_name":"portola redwoods state park",
"park_location":"15000 Skyline Blvd"}