Geospatial Queries With Oracle JSON Database

In this tutorial, learn about utilizing GeoSpatial Queries with Oracle JSON Database using Node.JS as the local development environment.

Doug Drechsel

Nov. 29, 22 · Tutorial

Likes (1)

Comment

Save

5.1K Views

In this article, I want to document how to write geospatial queries against the Oracle Autonomous Database. This example will use the node-oracledb add-on for Node.js and run against an Oracle JSON database. I will not talk about provisioning a JSON database on Oracle Cloud - that will be left as an exercise to the user, if so inclined. This first bit is about setting up the development environment, creating the Node.JS project, and installing the supporting configuration and software required to connect to the remote database.

I was investigating this functionality when building a simple phone application that would show places of interest based on the user's current coordinates. I created a small server-side Node.js application that ran in a Kubernetes cluster hitting an Oracle JSON database. The code presented here is a simplification that just demonstrates API usage. In the application, the user would select the distance from their current position to see all places of interest nearby. What is nice about this example is the code is all on the developer's side (Visual Studio on a Mac) accessing the remote database. This makes it easy to debug and iterate.

The Development Environment

First, provision a JSON database on Oracle Cloud. Try it on Oracle Free Tier:

Next, create a new Node.js project.

   
   cd myProject 

npm init

Install the node Oracle DB libraries.

npm install oracledb

I run this using Visual Code on macOS and connect to my remote JSON database, so there are 2 more things required to connect: an Oracle wallet for secure connections and Oracle instant client for macOS.

Download the wallet for your Oracle JSON Database. Note the TNS Name which will also be needed for constructing the Oracle Client.

IMPORTANT: You will need to update sqlnet.ora to point to the wallet location. Edit sqlnet.ora in the wallet folder and update:

   
   WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/myuser/myProject/wallet")))
SSL_SERVER_DN_MATCH=yes

Download the instant client for your development platform; in this case, macOS.

The directory structure for your project should look like this:

   
  
 
   user@user-mac myProject % ls
TestIt.js		
instantclient_19_8	
node_modules		
package-lock.json	
package.json		
wallet 
  

The Code

What are GeoSpatial queries? They are queries over a set of GeoJSON data types (Points, Polygons, etc.).

Oracle provides a set of QBE Operators ($near, $within and $intersects) that can find a set of locations based on the query parameters. The GeoJSON spec specifies a point as [longitude, latitude]. Maybe someone can explain this to me, because I always thought locations were given as "latitude, longitude," so keep that in mind when storing GeoJSON points.

Why [longitude, latitude]? It maps to a Cartesian coordinate system, [x,y]. That's the theory. So where did [latitude, longitude] originate? I asked the internet and found this:

I'm not an expert in this area, but I have done some reading on the subject, particularly on its history. I think the reason is accurate measurement of latitude came first as it was based on astronomical measurements. Longitude was not accurately measurable until a highly accurate time measuring device was developed.

Let's look at the code. First, import the oracledb package. This example sets autoCommit to true.

     JavaScript 
   
   const oracledb = require('oracledb');
oracledb.autoCommit = true;

Initialize the OracleClient by passing in the location of the wallet and instant client:

     JavaScript 
   
       oracledb.initOracleClient({
      libDir: "/Users/myuser/myProject/instantclient_19_8",
      configDir: "/Users/myuser/myProject/wallet"      
    });

Now connect to the database. The TNSNAME is taken from the same page where the wallet was downloaded.

     JavaScript 
   
 
 
   async function connect() {
  await oracledb.createPool({
    user: 'admin',
    password: 'password',
    connectString: 'tnsName',
})
.then(pool => this._connectionPool = pool)

return this._connectionPool
} 
  

Once connected, create a new Collection.

     JavaScript 
   
       const myConnectionPool = await connect();
    const myConnection = await myConnectionPool.getConnection();
    const mySodaDB = myConnection.getSodaDatabase();
    const myCollection = await mySodaDB.createCollection('GeoJsonExample');

Load up some GeoJSON Points with longitude decreasing and latitude increasing:

     JavaScript 
   
 
 
       // Load up some locations
    for (let i = 0; i < 10; i++) {
      latitude = 30 + (i*.1);
      longitude = 30 - (i*.1);
      let myContent = {
        "location": {
            "type": "Point",
            "coordinates": [
                longitude,
                latitude
            ]
        }
      };
      await myCollection.insertOne(myContent);
    } 
 
  

The points in the collection are as follows:

   
  
 
   Inital Load of GeoJson Points
{"location":{"type":"Point","coordinates":[29.8,30.2]}}
{"location":{"type":"Point","coordinates":[29.7,30.3]}}
{"location":{"type":"Point","coordinates":[29.6,30.4]}}
{"location":{"type":"Point","coordinates":[29.5,30.5]}}
{"location":{"type":"Point","coordinates":[29.4,30.6]}}
{"location":{"type":"Point","coordinates":[29.3,30.7]}}
{"location":{"type":"Point","coordinates":[29.2,30.8]}}
{"location":{"type":"Point","coordinates":[29.1,30.9]}}
{"location":{"type":"Point","coordinates":[30,30]}}
{"location":{"type":"Point","coordinates":[29.9,30.1]}} 
  

Now that we have some Points, find some points that are near a specific point (in this case, 60 miles), and log them. Each of the spatial QBE operators is followed by a JSON object whose fields must include $geometry. Operator $near must also include field $distance, and it can include $unit. A compile-time error is raised if $geometry is missing or if $distance or $unit is present with operator $intersects or $within(source: Oracle documentation).

     JavaScript 
   
 
 
       // Find all within a mile
    documents = await myCollection.find().filter({"location":{"$near":{"$geometry":{"type":"Point","coordinates":[30,30]},"$distance":60,"$unit":"mile"}}}).getDocuments();
    contentOfDocs = documents.map(i => i.getContent());
    console.log("Locations within a mile of corrdinate [30,30]")
    contentOfDocs.forEach(logIt);
    console.log(); 
  

The points returned from this query are as follows:

   
  
 
   Locations within 60 miles of corrdinate [30,30]
{"location":{"type":"Point","coordinates":[29.9,30.1]}}
{"location":{"type":"Point","coordinates":[29.8,30.2]}}
{"location":{"type":"Point","coordinates":[29.7,30.3]}}
{"location":{"type":"Point","coordinates":[29.6,30.4]}}
{"location":{"type":"Point","coordinates":[29.5,30.5]}}
{"location":{"type":"Point","coordinates":[29.4,30.6]}}
{"location":{"type":"Point","coordinates":[30,30]}}
 
  

Point [29.3,30.7] is more than 60 miles away. To verify, I use the NOAA Latitude/Longitude Distance Calculator, which shows that point being 55 nautical miles away, which is 55 * 1.1508 = 63.3 landlubber miles.

To see all the units of measure available, run:

select * from SDO_UNITS_OF_MEASURE;

There are currently over 140 distinct measurement units.

This query will look for all the points contained within the specified polygon using the $within QBE operator. Note that the coordinates are specified as if you were drawing the polygon. This is a square specified by 5 points.

     JavaScript 
   
 
 
       // Find all within a polygon,in this case a box
    documents = await myCollection.find().filter({"location":{"$within":{"$geometry":{"type":"Polygon","coordinates":[[[29,30],[30,30],[30,31],[29,31],[29,30]]]}}}}).getDocuments();
    contentOfDocs = documents.map(i => i.getContent());
    console.log("Locations within specified polygon")
    contentOfDocs.forEach(logIt);
    console.log(); 
  

This query returns 9 points:

   
  
 
   Locations within specified polygon
{"location":{"type":"Point","coordinates":[29.8,30.2]}}
{"location":{"type":"Point","coordinates":[29.7,30.3]}}
{"location":{"type":"Point","coordinates":[29.6,30.4]}}
{"location":{"type":"Point","coordinates":[29.5,30.5]}}
{"location":{"type":"Point","coordinates":[29.4,30.6]}}
{"location":{"type":"Point","coordinates":[29.3,30.7]}}
{"location":{"type":"Point","coordinates":[29.2,30.8]}}
{"location":{"type":"Point","coordinates":[29.1,30.9]}}
{"location":{"type":"Point","coordinates":[29.9,30.1]}} 
  

The only point not returned is [30,30] which is a point that falls upon the polygon and not within.
If you had wanted all the points on the polygon boundary and within the enclosed polygon space, the $intercepts QBE operator is for you.

     JavaScript 
   
 
 
       // Find all intersecting a polygon,in this case a box
    documents = await myCollection.find().filter({"location":{"$intersects":{"$geometry":{"type":"Polygon","coordinates":[[[29,30],[30,30],[30,31],[29,31],[29,30]]]}}}}).getDocuments();
    contentOfDocs = documents.map(i => i.getContent());
    console.log("Locations intersecting specified polygon")
    contentOfDocs.forEach(logIt);
    console.log(); 
  

This query returns the full set of points created at the beginning of this discussion.

   
  
 
   Locations intersecting specified polygon
{"location":{"type":"Point","coordinates":[29.8,30.2]}}
{"location":{"type":"Point","coordinates":[29.7,30.3]}}
{"location":{"type":"Point","coordinates":[29.6,30.4]}}
{"location":{"type":"Point","coordinates":[29.5,30.5]}}
{"location":{"type":"Point","coordinates":[29.4,30.6]}}
{"location":{"type":"Point","coordinates":[29.3,30.7]}}
{"location":{"type":"Point","coordinates":[29.2,30.8]}}
{"location":{"type":"Point","coordinates":[29.1,30.9]}}
{"location":{"type":"Point","coordinates":[30,30]}}
{"location":{"type":"Point","coordinates":[29.9,30.1]}} 
  

This was a quick overview of how to use the spatial operators provided by the Oracle JSON Database. These operators help developers support location functionality in their applications. Maybe for a bank application that wants to show the nearest ATMs or a boat manufacturer that wants to show the nearest retailers, the functionality can be easily implemented using QBE spatial operator and the Oracle JSON Database.

Complete Example Code

     JavaScript 
   
 
 
   const oracledb = require('oracledb');
oracledb.autoCommit = true;

async function connect() {
  await oracledb.createPool({
    user: 'admin',
    password: 'password',
    connectString: 'tnsname',
})
.then(pool => this._connectionPool = pool)

return this._connectionPool
}

function logIt(item){
  console.log(JSON.stringify(item));
}

const run = async () => {

  oracledb.initOracleClient({
     libDir: "/Users/myuser/myProject/instantclient_19_8",
     configDir: "/Users/myuser/myProject/wallet"      
  });

  try {  
    const myConnectionPool = await connect();
    const myConnection = await myConnectionPool.getConnection();
    const mySodaDB = myConnection.getSodaDatabase();
    const myCollection = await mySodaDB.createCollection('GeoJsonExample');


    // Load up some locations
    for (let i = 0; i < 10; i++) {
      latitude = 30 + (i*.1);
      longitude = 30 - (i*.1);
      let myContent = {
        "location": {
            "type": "Point",
            "coordinates": [
                longitude,
                latitude
            ]
        }
      };
      await myCollection.insertOne(myContent);
    } 

    // Log all the GEOJSon documents added
    documents = await myCollection.find().filter({}).getDocuments();
    let contentOfDocs = documents.map(i => i.getContent());
    console.log("Inital Load of GeoJson Points")
    contentOfDocs.forEach(logIt);
    console.log();
    // Find all within a mile
    documents = await myCollection.find().filter({"location":{"$near":{"$geometry":{"type":"Point","coordinates":[30,30]},"$distance":60,"$unit":"mile"}}}).getDocuments();
    contentOfDocs = documents.map(i => i.getContent());
    console.log("Locations within a mile of corrdinate [30,30]")
    contentOfDocs.forEach(logIt);
    console.log();

    // Find all within a polygon,in this case a box
    documents = await myCollection.find().filter({"location":{"$within":{"$geometry":{"type":"Polygon","coordinates":[[[29,30],[30,30],[30,31],[29,31],[29,30]]]}}}}).getDocuments();
    contentOfDocs = documents.map(i => i.getContent());
    console.log("Locations within specified polygon")
    contentOfDocs.forEach(logIt);
    console.log();

    // Find all intersecting a polygon,in this case a box
    documents = await myCollection.find().filter({"location":{"$intersects":{"$geometry":{"type":"Polygon","coordinates":[[[29,30],[30,30],[30,31],[29,31],[29,30]]]}}}}).getDocuments();
    contentOfDocs = documents.map(i => i.getContent());
    console.log("Locations intersecting specified polygon")
    contentOfDocs.forEach(logIt);
    console.log();


  } catch(error) {
    console.log(error);
    return;
  }

  console.log("END OF RUN");

}

run(); 
  

Database

Published at DZone with permission of Doug Drechsel. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

Trending