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"}