Managing GeoJSON data

The GeoJson specification defines the structure and content of json objects that are supposed to represent geographical shapes on earth (called geometries).

According to the GeoJson specification, for a JSON object to be a geometry object it must have two fields called type and coordinates, where the value of the type field specifies the kind of geometry and the value of coordinates must be an array whose elements define the geometrical shape. See About GeoJSON Data for more details on the various types of geometry objects. All kinds of geometries are specified in terms of a set of positions. However, for line strings and polygons, the actual geometrical shape is formed by the lines connecting their positions. The GeoJson specification defines a line between two points as the straight line that connects the points in the (flat) cartesian coordinate system whose horizontal and vertical axes are the longitude and latitude, respectively. See Lines and Coordinate System for more details.

If you want to follow along with the examples, download the script geojsonschema_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table.

Start your KVSTORE or KVLite and open the SQL.shell.

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

Using the load command, run the script.

load -file geojsonschema_loaddata.sql

Oracle NoSQL Database implements a number of functions that interpret JSON objects as geometries and allow for the search for rows containing geometries that satisfy certain conditions.

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

geo_intersect

Determines geometries that intersect with a GeoJSON geometry.

boolean geo_intersect(any*, any*)

The first and the second parameters any* can be any geometric object.

The function determines if two geometries that are specified as parameters have any points in common. 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.

If both parameters return a single geometry object each, the function returns true if the 2 geometries have any points in common; otherwise false.

Example: Texas is considering regulating access to the underground water supply. An aquifer is an underground layer of water-bearing permeable rock, rock fractures, or unconsolidated materials. The government wants to impose new regulations for locations that are very close to an aquifer.

The coordinates of the aquifer have already been mapped. You want to know all counties in the Texas state that intersect with that aquifer so that you can notify the county government for each affected county to participate in talks for the new regulations.

SELECT t.poi.county AS County_needs_regulation,
t.poi.contact AS Contact_phone
FROM PointsOfInterest t WHERE
geo_intersect(
    t.poi.location,
    { 

     "type" : "polygon",
      "coordinates": [
          [
            [-97.668457031249, 29.34387539941801],
            [-95.207519531258, 29.19053283229458],
            [-92.900390625653, 30.37287518811801],
            [-94.636230468752, 32.21280106801518],
            [-97.778320312522, 32.45415593941475],
            [-99.799804687541, 31.18460913574325],
            [-97.668457031249, 29.34387539941801]
          ]
        ]
    }
);

Explanation:

The above query fetches the locations which intersect with the location of the aquifer. That is if the location coordinates have any points in common with the location of the aquifer.
You use geo_intersect to see if the coordinates of the location have any points common with the coordinates of the aquifer that are specified.

Result:

{"County_needs_regulation":"Tarrant","Contact_phone":"469 745 5687"}
{"County_needs_regulation":"Kinga","Contact_phone":"469 384 7612"}

geo_distance

Determines distance between two geospatial objects.

double geo_distance(any*, any*)

The first and the second parameters any* can be any geometric object.

The function returns the geodetic distance between the two 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.

Overview of Geodetic Line

A geodetic line between 2 points is the shortest line that can be drawn between the 2 points on the ellipsoidal surface of the earth. For a simplified, but more illustrative definition, assume for a moment that the earth's surface is a sphere. Then, the geodetic line between two points on the earth is the minor arc between the two points on the great circle corresponding to the points, i.e., the circle that is formed by the intersection of the sphere and the plane defined by the center of the earth and the two points.

The following figure shows the difference between the geodetic and straight lines between Los Angeles and London.
Description of geodetic-vs-straight-line.jpg follows
Description of the illustration geodetic-vs-straight-line.jpg

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 -1 if any parameter returns zero or more than 1 item.
Returns NULL if any parameter returns NULL.
Returns -1 if any of the parameters is not a geometry object.

Otherwise, the function returns the geodetic distance in meters between the 2 input geometries.

Note:

The results are sorted ascending by distance( displaying the shortest distance first).

Example: How far is the nearest restaurant from the given location?

SELECT 
t.poi.name AS restaurant_name,
t.poi.address.street AS street_name,
geo_distance(
    t.poi.location,
    { 
       "type" : "point",
       "coordinates": [-121.94034576416016,37.2812239247177]
    }
) AS distance_in_meters
FROM PointsOfInterest t
WHERE t.poi.kind = "restaurant" ;

Explanation:

You query the PointsOfInterest table to filter the rows for restaurant.
You provide the correct location point and determine the distance using the geo_distance function.

Result:

{"restaurant_name":"Coach Sports Bar & Grill","street_name":"80 Edward St","distance_in_meters":799.2645323337218}
{"restaurant_name":"Ricos Taco","street_name":"80 East Boulevard St","distance_in_meters":976.5361117138553}
{"restaurant_name":"Effie's Restaurant and Bar","street_name":"80 Woodeard St","distance_in_meters":2891.0508307646282}

The distance between the current location and the nearest restaurant is 799 meters.

geo_within_distance

Determines geospatial objects in proximity to a point.

boolean geo_within_distance(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 the 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. 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.

Example: Is a city hall there in the next 5 km? How far is it?

SELECT t.poi.address.street AS city_hall_address,
geo_distance(
    t.poi.location,
    { 
        "type" : "point",
        "coordinates" : [-120.653828125,38.85682013474361]
    }

) AS distance_in_meters
FROM PointsOfInterest t
WHERE t.poi.kind = "city hall" AND
geo_within_distance( 
    t.poi.location,
    { 
        "type" : "point",
        "coordinates" : [-120.653828125,38.85682013474361]
    },
    5000
);

Explanation:

You query the PointsOfInterest table to filter the rows for city hall.
You use the geo_within_distance function to filter city hall within 5 km (5000m) of the given location.
You also fetch the actual distance between your location and the city hall using the geo_distance function.

Result:

{"city_hall_address":"70 North 1st street","distance_in_meters":1736.0144040331768}

The city hall is 1736 m(1.73 km) from the current location.

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.

geo_is_geometry

Validates a geospatial object.

boolean geo_is_geometry(any*)

The parameter any* can be any geometric object.

The function determines if the given input is a valid geometry object.

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

Example: Determine if the location pointing to the city hall is a valid geometric object.

SELECT geo_is_geometry(t.poi.location) AS city_hall
FROM PointsOfInterest t
WHERE t.poi.kind = "city hall"

Explanation: You use the function geo_is_geometry to determine if a given location is a valid geometric object or not.

Result:

{ "city_hall" : true}