X

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

Power your Geo Enabled application with Oracle NoSQL Database

Tim Goh
Principal Product Manager

Introduction

GeoJSON is becoming a very popular data format among many GIS technologies and services — it's simple, lightweight and straightforward.

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 geometry types and allows you to query, create indexes and use geo defined functions to extract data.

GeoJSON

Types

GeoJSON is represented as JSON and defines the below geometrical entities

Type

Description

Example

Point

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

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

LineSegment

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

[121.9892,37.3182],[122.1554,

37.3882],[122.2899,37.4589],

[122.4273,37.6032],[122.4304,

37.6267],[122.3975,37.6144]]

}

Polygon

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

}

MultiPoint

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

}

MultiLineString

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

}

MultiPolygon

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

]

}

GeometryCollection

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

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]

]]

})

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,

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)

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. A more detailed explanation for indexes and geo hashing can be found here.

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

  }

}

Table

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

 

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

Index

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)

Queries

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

#

# All stores

#

declare

        $radius2 double;

        $point1 double;

        $point2 double;

 

select /* FORCE_PRIMARY_INDEX(points) */

       id, p.info.point

from points p

where

      geo_within_distance(p.info.point,

                          { "type" : "point",

                            "coordinates" : [$point1, $point2]

                          },

                          $radius2)

More detailed documentation and examples of GeoJson support can be found in the official Oracle NoSQL Database SQL Specification.

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.