The Oracle NoSQL Database Blog covers all things Oracle NoSQL Database. On-Prem, Cloud and more.

How to Build Geo-Enabled Applications with Oracle NoSQL Database

Tim Goh
Principal Product Manager


Mobile applications and devices have fundamentally changed how we communicate, connect, consume information, and interact with our surroundings. They have become an integral part of our everyday life. We are able to access relevant information anytime and anywhere. Businesses are able to stay connected with their customers and offer relevant services and deals at anytime based on the customer locations.

Developing geo-enabled applications is fast and easy with Oracle NoSQL Database which supports storing different types of geographic data in the GeoJSON format.

GeoJSON has become a very popular data format among many GIS technologies and services. 

According to GeoJSON Specification (RFC 7946):

GeoJSON is a format for encoding a variety of geographic data structures […]. A GeoJSON object may represent a region of space (a Geometry), a spatially bounded entity (a Feature), or a list of Features (a FeatureCollection). GeoJSON supports the following geometry types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

Oracle NoSQL Database supports all of the above GeoJSON object types and  provides a set of functions for applications to store, query, create indexes on geolocation data.

Developing with GeoJSON


Let's begin with the GeoJSON object definition. To specify GeoJSON data, two fields called "type" and "coordinates" are required. The value of "type" field specifies the kind of geometry and the value of "coordinates" specifies an array whose elements define the geometrical shape. 





For a Point the "coordinates" is for a single position

{ "type" : "point", "coordinates" : [ 23.549, 35.2908 ] }


A LineString is one or more connected lines; the end-point of one line is the start-point of the next line. The "coordinates" member is an array of two or more positions


"type" : "LineString",

"coordinates" : [ [121.9447, 37.2975],[121.9500,37.3171],







A polygon defines a surface area by specifying its outer perimeter and the perimeters of any potential

holes inside the area


"type" : "polygon", "coordinates" : [ [

[23.48, 35.16],[24.30, 35.16],

[24.30, 35.50],[24.16, 35.61],

[23.74, 35.70],[23.56, 35.60],

[23.48, 35.16]]]



For a MultiPoint the "coordinates" field is an array of two or more positions.


"type" : "MultiPoint",

"coordinates" : [ [121.9447,

37.2975], [121.9500, 37.3171],

[122.3975, 37.6144] ]



For a MultiLineString the "coordinates" member is an array of LineString coordinate arrays


"type": "MultiLineString",

"coordinates": [[ [100.0, 0.0], [01.0, 1.0] ], [ [102.0, 2.0], [103.0, 3.0] ]]



For a MultiPolygon the "coordinates" member is an array of Polygon coordinate arrays


"type": "MultiPolygon",

"coordinates": [[[

[102.0, 2.0],[103.0, 2.0],

[103.0, 3.0],[102.0, 3.0],

[102.0, 2.0] ]],

[ [

[100.0, 0.0], [101.0, 0.0],

[101.0, 1.0], [100.0, 1.0],

[100.0, 0.0] ]]




Instead of a “coordinates” field, a GeometryCollection has a “geometries” field. The value of

"geometries" is an array.


"type": "GeometryCollection",

"geometries": [ {

"type": "Point",

"coordinates": [100.0, 0.0] },


"type": "LineString",

"coordinates": [ [101.0, 0.0], [102.0, 1.0] ] } ]



Querying GeoJson Data

Oracle NoSQL Database provides four functions to query for Geo data that have a relationship based on a certain geometry.

geo_intersect - geometries that intersect with a GeoJSON geometry

select t.poi as park

from PointsOfInterest t

where t.poi.kind = “nature park” and



“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],




geo_inside - geometries within a bounding GeoJSON geometry.

geo_within_distance - geospatial objects in proximity to a point on a sphere

geo_near - geospatial objects in proximity to a point

select t.poi as gas_station,


{ “type” : “LineString”,

“coordinates” : [

[121.9447, 37.2975], [121.9500, 37.3171], [121.9892, 37.3182], [122.1554, 37.3882],



}) as distance

from PointsOfInterest t

where t.poi.kind = “gas station” and


{ “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]





Indexes on GeoJson Data

Indexing GeoJson data is similar to indexing a JSON type data in Oracle NoSQL Database. When defining a GeoJson index the “geometry” or “point” keyword must be used after an index etc.

For optimal performance, “point” must be used when rows in the table are expected to have single point geometries.

Indexing of geometries is based on geo hashing that encodes a longitude/latitude pair to a string.

create index idx_test1 on testTable(coord.point as point);


Using Geo Queries:

Hyperlocal marketing is the process of targeting prospective customers in a highly specific, geographically restricted area, sometimes just a few blocks or streets, often with the intention of targeting people conducting “near me” searches on their mobile device.

For example, let’s say you’re regular customer is in your vicinity. You as a book store that maintains customer profile and their buying details have created ad campaigns for different customer profiles to increase foot fall into your store. Hyperlocal marketing will help you push these notifications to your customers on probable personalized offers that you want to specifically send to your customers.

Sample data:


  "id" : 1,

  "info" : {

    "kind" : "store1",

    "country": "greece",

    "region" : "crete",

    "county" : "chania",

    "city" : null,

    "point" : { "type" : "point", "coordinates" : [ 23.549, 35.2908 ] }




  "id" : 2,

  "info" : {

    "kind" : "store2",

    "country": "greece",

    "region" : "crete",

    "county" : "iraklion",

    "city" : null,

    "point" : { "type" : "point", "coordinates" : [ 24.9, 35.4 ] }





The table structure would have 2 column with the GeoJSON data stored in “info”.


create table points (id integer, info json, primary key(id));



We will create 2 indexes, one which creates an index on the “point” attribute and another one on the “city” attribute

create index idx_ptn on points(info.point as point)


create index idx_kind_ptn_city on points(info.kind as string,

                                                                          info.point as point,

                                                                          info.city as string)



Here we are searching for all stores from the current point (location of the customer) within a radius (configured)


# All stores



        $radius2 double;

        $point1 double;

        $point2 double;


select /* FORCE_PRIMARY_INDEX(points) */

       id, p.info.point

from points p



                          { "type" : "point",

                            "coordinates" : [$point1, $point2]




Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.