Introduction
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
Types
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.
| 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.
| 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) |