Searching for GeoJson Data

Oracle NoSQL Database provides 4 functions to search for GeoJson data that have a certain relationship with a search geometry.

boolean geo_intersect(any*, any*)

Raises an error if it can be detected at compile time that an operand will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

Returns false if any operand returns 0 or more than 1 items.
Returns NULL if any operand returns NULL.
Returns false if any operand returns an item that is not a valid geometry object.

Finally, if both operands return a single geometry object, it returns true if the 2 geometries have any points in common; otherwise false.

boolean geo_inside(any*, any*)

Raises an error if it can be detected at compile time that an operand will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

Returns false if any operand returns 0 or more than 1 items.
Returns NULL if any operand returns NULL.
Returns false if any operand returns an item that is not a valid geometry object (however, if it can be detected at compile time that an operand will not return a valid geometry, an error is raised).
Returns false if the second operand returns a geometry object that is not a polygon.

Finally, if both operands return a single geometry object 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; otherwise false. The interior of a polygon is all the points in the polygon area except the points on the linear rings that define the polygon’s boundary.

boolean geo_within_distance(any*, any*, double)

Raises an error if it can be detected at compile time that any of the first two operands will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

Returns false if any of the first two operands returns 0 or more than 1 items.
Returns NULL if any of the first two operands returns NULL.
Returns false if any of the first two operands returns an item that is not a valid geometry object.

Finally, if both of the first two operands return a single geometry object, 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 operand; otherwise false. The distance between 2 geometries is defined as the minimum among the distances of any pair of points where the first point belongs to the first geometry and the second point to the second geometry. If N is a negative number, it is set to 0.

boolean geo_near(any*, any*, double)

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.

In addition to the above search functions, the following two functions are also provided:

double geo_distance(any*, any*)

Raises an error if it can be detected at compile time that an operand will not return a single valid geometry object. Otherwise, the runtime behavior is as follows:

Returns -1 if any operand returns zero or more than 1 items.
Returns NULL if any operand returns NULL.
Returns -1 if any of the operands is not a geometry.

Otherwise it returns the geodetic distance between the 2 input geometries. The returned distance is the minimum among the distances of any pair of points where the first point belongs to the first geometry and the second point to the second geometry. Between two such points, their distance is the length of the geodetic line that connects the points.

boolean geo_is_geometry(any*)

Returns false if the operand returns zero or more than 1 items.
Returns NULL if the operand returns NULL.
Returns true if the input is a single valid geometry object. Otherwise, false.

Notice that the above geo functions operate on geometry objects, but not on Features or FeatureCollections. Nevertheless, Features and FeatureCollections can still be queried effectively by passing their contained geometry objects to the geo function. An example of this is shown in the following section.

Example 11-1 Searching for GeoJson Data

Consider a table whose rows store points of interest. The table has an id column as its primary key and a poi column of type json.

CREATE TABLE PointsOfInterest (
    id INTEGER, poi JSON, 
PRIMARY KEY(id))


INSERT INTO PointsOfInterest VALUES (
    1,
    {
        "kind" : "city hall",
        "address" : { 
            "state" : "CA",
            "city" : "Campbell",
            "street" : "70 North 1st street"
        },
        "location" : {
            "type" : "point", 
            "coordinates" : [121.94,37.29] 
        }
    }
)
INSERT INTO PointsOfInterest VALUES (
    2,
    {
        "kind" : "nature park",
        "name" : "castle rock state park",
        "address" : { 
            "state" : "CA",
            "city" : "Los Gatos",
            "street" : "15000 Skyline Blvd"
        },
        "location" : { 
            "type" : "polygon",
            "coordinates" : [
                [
                    [122.1301, 37.2330],
                    [122.1136, 37.2256],
                    [122.0920, 37.2291],
                    [122.1020, 37.2347],
                    [122.1217, 37.2380],
                    [122.1301, 37.2330]
                ]
            ]
        }
    }
)

The following query looks for nature parks in northern California. The query uses geo_intersect, instead of geo_inside, to include parks that straddle the border with neighbor states.

SELECT t.poi AS park
FROM PointsOfInterest t
WHERE t.poi.kind = "nature park" 
AND
geo_intersect(
    t.poi.location,
    { 
        "type" : "polygon",
        "coordinates" : [
            [
                [121.94, 36.28],
                [117.52, 37.38],
                [119.99, 39.00],
                [120.00, 41.97],
                [124.21, 41.97],
                [124.39, 40.42],
                [121.94, 36.28]
            ]
        ]
    }
)

The following query looks for gas stations within a mile of a given route. The returned gas stations are ordered by ascending distance from the route.

SELECT 
t.poi AS gas_station,
geo_distance(
    t.poi.location,
    { 
        "type" : "LineString",
        "coordinates" : [
            [121.9447, 37.2975],
            [121.9500, 37.3171],
            [121.9892, 37.3182],
            [122.1554, 37.3882],
            [122.2899, 37.4589],
            [122.4273, 37.6032],
            [122.4304, 37.6267],
            [122.3975, 37.6144]
        ]
    }
) AS distance
FROM PointsOfInterest t
WHERE t.poi.kind = "gas station" 
AND
geo_near( 
    t.poi.location,
    { 
        "type" : "LineString",
        "coordinates" : [
            [121.9447, 37.2975],
            [121.9500, 37.3171],
            [121.9892, 37.3182],
            [122.1554, 37.3882],
            [122.2899, 37.4589],
            [122.4273, 37.6032],
            [122.4304, 37.6267],
            [122.3975, 37.6144]
        ]
    },
    1609
)

Example 11-2 Searching for GeoJson data

This example shows how FeatureCollections can be queried in Oracle NoSQL Database. Consider a "companies" table that stores info about companies, including the locations where each company has offices and some properties for each office location.

CREATE TABLE companies (
    id INTEGER, info JSON, PRIMARY KEY(id))


INSERT INTO companies VALUES (
    1,
    {
        "id" : 1,
        "info" : {
            "name" : "acme",
            "CEO" : "some random person",
            "locations" : {
                "type" : "FeatureCollection",
                "features" : [
                    {
                        "type" : "Feature",
                        "geometry" : { 
                            "type" : "point", 
                            "coordinates" : [ 23.549, 35.2908 ] 
                        },
                        "properties" : {
                            "kind" : "development",
                            "city" : "palo alto"
                        }
                    },
                    {
                        "type" : "Feature",
                        "geometry" : { 
                            "type" : "point", 
                            "coordinates" : [ 23.9, 35.17 ] 
                        },
                        "properties" : {
                            "kind" : "sales",
                            "city" : "san jose"
                        }
                    }
                ]
            }
        }
    }
)

The following query looks for companies that have sales offices within a search region and returns, for each such company, an array containing the geo-locations of the sales offices within the same search region.

SELECT id,
c.info.locations.features [
    geo_intersect( 
        $element.geometry,
        { 
            "type" : "polygon",
            "coordinates" : [ 
                [
                    [23.48, 35.16],
                    [24.30, 35.16],
                    [24.30, 35.70],
                    [23.48, 35.70],
                    [23.48, 35.16]
                ] 
            ]
        } 
    )
    AND
    $element.properties.kind = "sales"
].geometry AS loc
FROM companies c
WHERE EXISTS c.info.locations.features [
    geo_intersect(
        $element.geometry,
        { 
            "type" : "polygon",
            "coordinates" : [ 
                [
                    [23.48, 35.16],
                    [24.30, 35.16],
                    [24.30, 35.70],
                    [23.48, 35.70],
                    [23.48, 35.16]
                ] 
            ]
        } 
    )
    AND
    $element.properties.kind = "sales"
]

For efficient execution of this query, the following index can be created:

CREATE INDEX idx_kind_loc ON companies (
info.locations.features[].properties.kind AS STRING,
info.locations.features[].geometry AS POINT)