All posts by bcarter

Track-a-Watt – IoT to the Database: code walkthrough

This is a companion post to my Track-a-Watt – IoT to the Database presentation.

If I missed you at GLOC 2017, you can still catch it at KScope 2017 or OpenWest 2017.

I’ve packed loads of stuff into this presentation, including: soldering (no software involved), Python, Javascript, HTML, PL/SQL and a little SQL (there has to be at least a little SQL in any application! :-)).

Even if I had a few hours of presentation time, it’d be hard to do justice to all these different scripts in their different languages, without losing lots of my audience somewhere along the way. So the presentation keeps things brief and to the point, and I will use this post to provide more depth for some of the code sections.

Python modules

sensorhistory.py

I mention that there are some names and labels used in this module that reference “5 minutes”.

I didn’t find any instances where a value for 5 minutes (300 seconds) is used in the functionality.  Five minutes is only used as labels and object names.

The declarations for these can be found on lines:

  • 103 – cumulative5mwatthr.
    A variable used to store the cumulative watts per hour readings since the timer was started.  We’ll call this total-watt-hours below.
  • 105 – fiveminutetimer.
    A variable used to store the time when the timer was initialized.  We’ll call this start-time below.
  • 119 – reset5mintimer.
    A function to reset start-time and total-watts.
  • 123 – avgwattover5min.
    A function that prints the current data and returns the calculated average watts per hour since the timer started.
  • 124 – fivetimer.
    A text label in the print statement.
  • 125 – 5mintimer and 5minwatthr
    Labels in the text returned by the __str__ function.

This is just a demo, so I didn’t rename these objects.  I only highlight these in case the names cause confusion after I change the timer to 10 seconds.

xbee.py

I only made one change in this module due to an error I received.  I have been running this project on both Windows 7 and Fedora 25 machines.  On one machine the values for p are passed in as Unicode and the other they are Strings.

The change here just checks to see if p is a String if so, convert it to Unicode otherwise accept it as is.  Thanks, Anthony Tuininga for making this clean and compact.

wattcher.py to wattcher-min.py

The original code for the Tweet-a-Watt project has some functionality that I don’t intend to use for my simple graph.  I created the wattcher-min.py module by stripping out most of these features.

Average Watts/Hour calculation

As far as I can prove with my (cough cough) math skills, the algorithm used to calculate watts per hour works for whatever time slice you want to track.

I have not gone through all of the code that leads up to this point, but as I understand it:

  • The kill-o-watt is collecting a constant stream of readings.
  • The kill-o-watt X-Bee transmits the reading to the computer every 2 seconds where the data is stored in the array, wattdata[].
  • This code calculates and stores the average watts used in the last second.
To calculate the average W/Hr during our current time slice:

  • Calculate the number of seconds since the last reading.
  • Multiply the average watts per second by the elapsed seconds then divide by 3600 (seconds in an hour).
  • Reset the last reading timer.
  • Print the data.
  • Add the calculated average W/Hr for this time slice to the running total.
Here’s a basic explanation:

When a chunk of data comes in, we calculate the average W/Hr for the first second of that chunk.  Multiply that value by the number of seconds since the previous reading.  This gives us the average W/Hr for a 2 second time slice.  If we were to collect those slices for one hour and add them together we would have X watts used in one hour.

The cumulative watts used will continue to accrue until we pass the limit of the timer we’re using to determine how often to send the data up to ORDS.

To calculate the average W/Hr during the last 10 seconds:

  • Multiply the cumulative watts used by 3600 (seconds in an hour).
  • Divide by the seconds since the last time we sent data to ORDS.
The short explanation is if we were getting a consistent reading of 5 watts per hour for every sample, every 10 seconds this calculation would come out to 5 W/Hr during the last 10 seconds.  However, it’s not likely that we will get the same 5 W/Hr every reading so this function will give us the average W/Hr during the last 10 seconds.

I can understand if you’re a bit confused at this point. There seem to be a couple extra steps here than what should be needed for my simple graph.  I had to work out a simulation in a spreadsheet before I could accept that it was working.  However, I left the calculation code alone assuming that it may be needed for some of the more advanced versions of the project.

If your math skills are better than mine and you find that my explanation is wrong or you can explain it better, please leave a comment.

Oracle Jet

The Oracle Jet graph used in the example is the basic Line with Area Chart.  I’m using the Y axis for the W/Hr data and the X axis for the timestamps.

The graph has the capability to track multiple series of data which would be useful for multiple kill-a-watts, but I’m only using one in the presentation.

The relationship between the X and Y axises is positional using the array position for the data elements in two arrays.

JavaScript

This is a typical jQuery ajax GET function.

Inside the success function:

  • Get the items array from the response.
  • Create a variable for the X-axis data.
  • Create a variable for the Y-axis data.  Since we’re only tracking one sensor we can define the name value and initialize an items array for it.
  • Loop through the response items.
  • Populate the items array for our sensor (Y axis).
  • Populate the timestamp array (X axis).
  • Set the ko.observable objects for the two axises of the graph.

Next is a short function to call getData() every 5 seconds.

HTML

We copy the HTML from the cookbook for just the graph component.

Since we’re not using the additional functionality from the Jet Cookbook example we remove the highlighted lines (14, 15).

Go try something new

The goal of this presentation is to encourage people to go out and try something a little out of their comfort zone.  If you think your soldering skills are lacking find a maker group in your area and take a class.  If you are strong in one programming language try another.

This is a great project to experiment with, there are a few different skills all mixed together, each of them is fairly close to entry level and they are popular enough that there should be a lot of help available.

As always, if you run into issues feel free to leave a comment here or hit me up on twitter and I’ll be glad to help get you going.

I plan to update this post as questions arise.  If you’d like to see it all running together catch one of my upcoming sessions.

Three ways to make a REST call from Oracle JET

GET Data Into Your App

In this post, I will demonstrate three methods for loading data into your JET application using GET calls to retrieve data from a REST API.  You don’t need to know anything about JET to follow through the examples.  However, I do assume that you’re familiar with JET so I won’t be explaining most of the JET functionality.  If you would like to know more about Oracle JET, check out these resources.

Setup

The setup section will walk through creating a functioning JET application you can use to call each of the examples and display the data.  If you prefer to just read through the examples you can skip down to the REST GET Examples section.

In order to make the REST calls, we’ll need an application with a REST API.  I’ll be using DinoDate for the back end API.

Go to https://github.com/oracle/dino-date and follow the installation instructions.  Verify that DinoDate is running before proceeding.

DinoDate includes a JET front end that you can look through, but for this post, we’re going to replace it with the navbar JET template.

Start with a template

Rename the commonClient directory and create a new empty directory:

Install Oracle JET following the Oracle Jet Get Started guide:

Oracle JET is a very active open source project so the following may change over time.  If the version you’re using is different, try to locate the files mentioned in the following commands.  Locating the /src directory is required.  If you can’t find the rest of these files, the examples should still work, they just won’t be as pretty.

Alternatively, you can install the version of the generator used for this post instead of the global install above:

This JET template comes with a lot of pre-installed functionality added to the tempJet directory.  For this post, we’ll grab only the files that we need and put them in a jet directory

  • Create the directory structure ‘jet/css/libs/oj/v2.3.0/alta’.
  • Copy everything from tempJet/src to the root of the new jet folder.
  • Copy and rename the .css file.
  • Copy the fonts and images directories.
  • Delete the tempJet folder.
  • CD into jet.
Now if you haven’t already started DinoDate, follow the instructions to start your preferred middle tier.

Open a browser and go to http://localhost:3000/ (use the port for the mid-tier you started) and you should now see this.

Convert Existing Module

Rather than create a new module from scratch, I’m going to change the Customer module into a DinoDate member search module.  This way we can see what’s included in the template and how it’s ‘wired’ together.

If you’re not already there, navigate to dino-date/commonClient/jet/

Rename the files js/views/customers.html and js/viewModels/customers.js to search.html and search.js.

In order to change our the Navigation List Item from ‘Customer’ to ‘Search’, we need to edit js/appController.js.

Change the code on the following lines (Line numbers may change with future versions of JET):
22:  'customers': {label: 'Customers'}, to  'search': {label: 'Search'},

33:  {name: 'Customers', id: 'customers', to  {name: 'Search', id: 'search',

34:  iconClass: 'oj-navigationlist-item-icon demo-icon-font-24 demo-people-icon-24'}, we’ll use the magnifier icon  iconClass: 'oj-fwk-icon-magnifier oj-fwk-icon oj-navigationlist-item-icon'},

DinoDate creates a user token as part of the login process.  This token is required to access the API.  This process is not really relevant to the examples so while we’re in appController.js we’ll add a helper (cheater) function to make the examples work.

The $.ajax function will automatically log us in as the Administrator user and set the authorization token when the application starts.

The getHeaders function will be used later to generate the headers used by DinoDate for authorization and processing options.

Add this code after   self.navDataSource = .... , line 38.

Edit js/index.html and add an id property to the <div> with role=”main”.

Refresh the page and click the Search tab.

Prepare the viewModel

Edit js/viewModels/search.js

Add the Jet components we plan to use in our view, to the list of dependencies.

Change CustomerViewModel to SearchViewModel for the function name and in the return statement at the bottom.

Delete everything inside the SearchViewModel function except for var self = this;

Add a variable ‘rootViewModel’ to give us access to js/appController.js.  We’ll use this to access the getHeaders function.

If you used a different id value in the <div id="mainContent" role="main"  section above, use that id instead of ‘mainContent’.

Add some Knockout observables we’ll need for the view data-binding.  We’ll use the observable searchRun to display the method used to call the REST API.

Add a function to generate the URL for the search API.

Stub in the search functions.

Change the View

Now let’s switch over to our view, edit js/views/search.html.

Delete the all of the HTML in the file.

If you want to dig into the details on the components we’re using you can check out the CookBook.  For this example, we’ll just identify the components we’re using.

Add an ojInputText and an ojInputNumber to accept our search criteria for a Keyword and Distance.  Set their values to the correct observables in the viewModel.

Add three ojButton components, one for each method we’re going to demonstrate. We’ll set them to be disabled until the token in appController.js is populated to prevent querying before we’re logged in.

Display which search function as been run and add an ojTable component to display the returned data.

And finally, an ojPagingControl to add pagination controls.

Notice the data property for the table and paging controls are both using the same ko.observable, memberData.

The setup is complete, let’s flesh out our search functions.

REST GET Examples

viewModel

Using the viewModel method is typically the default approach for making REST calls in JET.  The JET components (oj.Model and oj.Collection) implement a lot of functionality behind the scenes allowing you to focus on your application instead of generic plumbing.

  • Define the Member model by extending oj.Model .  Since we don’t plan to do any single record functions, we only need to define the idAttribute of a Member.
  • Define a collection of by extending oj.Collection.
  • Set the base URL for the REST API.
  • Set the model to Member, which we defined above.
  • Set customURL to our getHeaders function in appController.js.  This adds the headers needed for DinoDate.
  • Parse the returned object and return the member array ‘items’.
    Sometimes we only need part of the data returned by the REST API, so we need to define a function to parse the response.  For our examples, all we need is the members.items array.  If your REST API returns only the array, you shouldn’t need to define a parse function at all.
  • Create a new Members collection.
  • Create the members variable using membersColl to create a new oj.CollectionTableDataSource which is then used to create a new oj.PagingTableDataSource.
  • Modify the URL of membersColl using the searchURL function.  This will create the URL using the ko.observables keywords and maxDistance.
  • Call the fetch function.
  • Set self.memberData(self.members); after the fetch() has returned.

Replace the stubbed searchViewModel function with this code.

In your browser, refresh your page and search for the letter ‘a’ using the ViewModel button. You should see a list of Member names. The ojPagingControl defines a page as 10 records so you may have multiple pages of members.

Shared Model

Sometimes you will want to use the same Model and/or Collection across multiple functions or viewModels.  Trying to keep our application as DRY as possible, we’ll move the model and collection definitions out to their own files.

Let’s create a new directory /js/models  and two new files /js/models/Member.js  and /js/models/Members.js .

For both files, we’ll define components we need to include and also add the variable rootViewModel.

In Member.js, we’ll create a Members Model.  This model could be used by itself to work with a single Member object, see oj.Model for more information.

  • Create a Member by extending oj.Model, as shown in the last example.
  • Add the urlRoot for the members endpoint of your RESTFull API.
  • The customURL property is used for the DinoDate headers, just like in the previous Collection example.  If your application doesn’t need to modify the headers, you could exclude this property.
  • Return the new Model.

Edit Member.js and add the following code.

For our Members Collection:

  • Add the new member model to the define dependencies array.
  • Accept the member model as an argument to the function.
  • Create a Members collection by extending oj.Collection, like in the last viewModel example.
  • Set the URL.  In this case, it’s the same as the rootUrl for Member.
  • Set the model to Member.
  • We use the customURL for the DinoDate headers, the same as in the other examples.
  • Parse the returned object.  When DinoDate returns a set of members it includes some extra data.  We are only interested in the items array.
  • Return the collection.

Edit Members.js and add the following code.

Back to search.js.

Include our new collection in the define dependencies array, add ‘models/Members’ right after ‘jquery’.

Accept the Members Collection into the function definition by adding the Members argument.

To flesh out the searchSharedModel function we simply copy the code from searchViewModel() excluding the Model and Collection definitions.

  • Create a new Members collection.
  • Create the members variable using membersColl to create a new oj.CollectionTableDataSource which is then used to create a new oj.PagingTableDataSource.
  • Modify the URL of membersColl using the searchURL function.  This will create the URL using the ko.observables keywords and maxDistance.
  • Call the fetch function.
  • Set self.memberData(self.members); after the fetch() has returned.

Replace the stubbed searchSharedModel function with this code.

Refresh your page and search for ‘a’ again using the Shared Model button, you should see the same results as the ViewModel button.

AJAX

Sometimes you may need some specific functionality not supported by the framework.  If extending oj.Collection or oj.Model doesn’t meet all of your needs, you can use the jQuery ajax method.

  • We’re using a standard $.ajax call with a type of “GET”.
  • Set the headers needed by DinoDate using the getHeaders() function in appController.js.
  • The searchURL function will create the URL using the ko.observables keywords and maxDistance.
  • Assuming everything is setup properly and we get a successful response we need to create the proper object types for the Jet components we’re using in the view.
    • Since we are using a paging control, we’ll need an oj.PagingTableDataSource object which we’ll create from an oj.ArrayTableDataSource object.
    • The oj.ArrayTableDataSource object is created from the items array returned in the GET response.
    • We also need to identify the idAttribute of our returned objects, which is ‘memberId’.
  • Finally, we’ll set the Knockout Observable self.memberData to our new resData object.
  • If there’s a failure, show it in an alert.

Replace the stubbed searchAJAX function with this code.

Refresh your page and search for ‘a’ using the AJAX button, you should see the same results as the other buttons.

If you are watching the execution time in the returned object, be aware that the time is only tracking the call from the middle tier to the database, so these changes to the client side code do not affect the execution time. Any differences for these examples should be ignored.

Be Flexible

As you build applications with Oracle JET, you will probably use the viewModel method the most.  However, don’t be afraid to use other methods when they make more sense for your application. It is perfectly acceptable to mix and match these different approaches as needed. Use the shared model to try and stay DRY and the AJAX method for special cases.

As the saying goes; When you only have a hammer, everything looks like a nail.  Keep your toolbox full.

JDBC Connection to Exadata Express Cloud Database from a Raspberry Pi

Spoiler Alert: It’s pretty much the same as any other Linux system.

The official documentation can be found on the Oracle website.

Install Linux on your Raspberry Pi

I prefer to use BerryBoot, it makes it real easy to install several flavors of Linux on my pi.  You can get it from their repo.  For this example, I’m using Raspbian.

If you plan to use SSH this is a good guide for setting up passwordless SSH access.

Java Version

Verify that you have the correct Java Version:
JDK 8 – JDK8u71 or higher
JDK 7 – JDK7u80 or higher

When this post was written, Raspbian was using an older version of Java 8 so an upgrade is needed.

There are already quite a few guides out there if you need help.  I used this one.

JCE Files

Next, we need to download and install the JCE Unlimited Strength Jurisdiction Policy Files.

After you download the .zip file, extract the files and place the two .jar files (local_policy.jar & US_export_policy.jar) in
<java-home>/lib/security.

<java-home> refers the home for the JRE not the JDK.  My full path is:
/opt/java/jdk1.8.0_121/jre/lib/security/

Oracle Exadata Express Cloud Database

Now that Linux and Java are setup, it’s time to setup your Oracle Exadata Express Cloud Database.

Log into your Service Console and find the Client Access section.

If client access is not already enabled, you will see a link to Enable Clent Access.  Click that link and follow the instructions.  Once it’s done it will look like this.

Click on Download Client Credentials and you’ll get this popup:

Enter and confirm a password.  This is the password that your JDBC client will use to access your credentials.

Click download then extract the file.  I extracted mine into my home directory in a directory named EE_Credentials.
/home/pi/EE_Credentials

Create a Test Class

Now we have everything in place we can try it out.

In order to ensure that nothing else is interfering with our test, let’s compile a simple test class.

  1. Replace the strings for your user and password.
  2. Save this into a file called testJDBC.java.
  3. Copy the oracle driver into the same directory.  We’ll manually set the classpath to keep our test self-contained.
  4. Compile the class.
    javac testJDBC.java

Required System Properties

The last thing we need to do is set some system properties using the -D option for Java.

The parameters we’ll be using are:

If you’re using JDK7 you’ll also need to include

Remember to replace YourPassword with the password you set above and /home/pi/EE_Credentials with the path you extracted the credentials file into.

To run the test: (all one line)

If you see the error:
Caused by: oracle.net.ns.NetException: could not resolve the connect identifier “dbaccess”
Make sure the path in the properties is where you extracted the credentials file to.  You should see a tnsnames.ora file in there.

Use Environment Variables

If everything worked then we can make it a bit easier by moving those properties to an environment variable.

Then run it with:

Now we can be confident that we can connect to and query from the Oracle Exadata Express Cloud Database.

To use the connection in your other applications, follow these instructions and read the documentation on setting the Java properties.

For example:
If you’re using Liquibase, set the environment variable like in the example above.  If you’re using OpenHab the environment variable would be named EXTRA_JAVA_OPTS.

Connecting to Oracle Exadata Express Cloud Database is easy and it provides a secure reliable database to support your applications.

Getting Started With Oracle Spatial part 2

Setup

See the previous post for setup instructions.

Is the object valid?

If you don’t have a tool with Map View or if you have problems displaying the objects (for example not enough resources to display them all) you can use the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() function to check if they are valid. Pass your object into the function with a tolerance. It will return TRUE or FALSE.

For example, I have used this function to validate the line exercise answer from the previous post.

Create a Polygon

We want to create a home for our dinosaurs so let’s look at some examples to create polygons using different methods.

Let’s make a simple rectangle using 2 points on opposite corners.

This creates a rectangle with one corner at Lon-0, Lat-0 and the opposite corner at Lon-0.9, Lat-0.9, approximately 100KM East and 100KM North.

Let’s make another simple rectangle using points for all corners.

Notice that when we create a simple exterior polygon we define the points in counter-clockwise order, also the first and last points are the same.

This could be an OK home for our dinosaurs, it’s 100 KM by 100 KM, but living on a rectangle could be kind of boring.

Let’s make it more interesting.

This time, we’ll create an irregular shaped polygon. This is basically the same as the last rectangle polygon but we’ll be using a few more points.

Exercises:

1. Create a rectangle using 2 points starting at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920 and having its opposite corner approximately 100KM East and 100KM North. (0.9 is approximately 100KM)

Answer

2. Create the same rectangle but this time, using 4 points to define the corners.

Answer

3. Create an irregular polygon near the center of the Bermuda Triangle using at least 8 points.

Answer

Note: when creating this type of polygon (1, 1003, 1), be careful to ensure that none of the lines cross or the object will be invalid.  Also, as with lines, if your object is “off the map”, it will be invalid.

Experiment with other polygons using different point sets.

Populate Tables

Now it’s time to start adding some data for our dinosaurs and their island.  We’ll start by building populateData.sql.  Here’s a stub to get you started.

At the start of our script we are clearing any previous changes in order to maintain a clean set of data.

We’ll be storing our data in 2 tables. Our polygons that designate an area will be stored in the DD_LOCATIONS table and we’ll keep track of where our dinosaurs are in their member record using the  DD_MEMBERS.SPTUT_LOCATION column we created in the setup.

We will be storing sdo_geometry objects. These objects must be created using the same SRID (4326) we used to populate user_sdo_geom_metadata in the setup script. We will not be going into detail on the metadata and indexes, but if you receive an error mentioning SRID, it’s probably due to mismatching the SRID.

Exercises:

1. Insert the polygon we previously defined into the dd_locations table with a location_name of ‘dino-island’.

2. Update our admin dinosaur (member_id = 0) and set the location to a point inside of ‘dino-island’.

New populateData.sql

Random Polygon

Most of the time you’ll be creating pre-defined objects. But, one of the reasons our dinosaurs have been able to stay undetected for so long is their island randomly changes shape every now and then.

In previous exercises, we created our polygons one point at a time in a counter-clockwise direction so we should be able to do this programmatically with some random coordinates.

On the first attempt to create a random polygon, I went with an idea to walk the edges of a square and define points a random distance in or out from the edge. While this did work, the function was a bit more complicated than I wanted, involving nested loops and multi-layered if/then statements.

I decided to go with a circular approach instead.

Helper Functions:

Here are examples of a couple useful functions.

SDO_UTIL.POINT_AT_BEARING accepts:

  • A standard SDO_GEOMETRY point object
  • A radian value from 0 to 2pi. The gif here explains what a radian is perfectly.
    Note: SDO_UTIL.POINT_AT_BEARING uses 12 o’clock as 0 radian and increases clockwise.
  • A distance in M.

The function returns an SDO_GEOMETRY point object, X meters from the given point in the direction of the radian value.

Since we are creating our polygon using an MDSYS.SDO_ORDINATE_ARRAY we need to extract the x/y coordinates from our new point and add them to our array. (Remember the first and last point in the array are the same.)

SDO_UTIL.GETVERTICES can be used to extract quite a lot of data from our point object, but since we’re only interested in the x / y coordinates we will treat the output as a table and select the values.

Exercise:

Create a function that accepts:

  • A minimum radius. We need to ensure that our dinosaurs have land to stand on.
  • A variance percent that we will use to calculate our random edge points.
  • A center point longitude.
  • A center point latitude.

The function should return a valid SDO_GEOMETRY simple polygon object.

Change populateData.sql to use the new function for the dino-island value, centered in the Bermuda Triangle.

New generate_polygon_rad.fnc

New populateData.sql

Random Point Inside a Given Polygon

Now that we have an island lets figure out where the rest of our dinosaurs are.

We want to make sure our dinosaurs are all on the island. We could take the easy way out and just use the POINT_AT_BEARING function with a random radian and distance from 0 to the minimum limit used to create the island, but that leaves a lot of unused space out by the beaches.

Helper Functions:

Here are examples of a couple useful functions.

SDO_GEOM.SDO_INTERSECTION accepts 2 SDO_GEOMETRY objects and returns just the overlapping section. Pass in your dino-island and a line starting at the center that is long enough to extend outside of the island. You will get back a line from the center that stops right at the edge of the island.

SDO_GEOM.SDO_LENGTH accepts an SDO_GEOMETRY object and returns different information depending on the object passed in. We’re passing in a line so it will return the length of that line.

You may notice that these two functions accept a Tolerance parameter. The tolerance value determines how close two adjacent points have to be together to be considered the same point. So passing a value of 0.05 means: Points which are closer together than 5 centimeters, are considered to be one point.

Exercise:

Create a function that accepts:

  • The island as an SDO_GEOMETRY simple polygon object.
  • The island’s minimum radius.
  • The island’s variance percent.
  • The island’s center point longitude.
  • The island’s center point latitude.

The function should return a valid SDO_GEOMETRY point object located inside the island polygon.

New generate_random_location.fnc

Change populateData.sql to use the new function to update the dinosaurs locations using the same values as the island.

New populateData.sql

Using this information and the methods you’ve used previously, you can generate random points around the island without having to worry about dropping any dinosaurs in the ocean.

Using this specific methodology will tend to favor placing dinosaurs in the center of the island since the location always starts from the center and goes out. Feel free to try other methods to create a more random dispersion.

Random Point Inside a Given Polygon using only the Polygon

The function generate_random_location works well enough to generate a point inside a polygon if you already know something about it.

Let’s change it so it can accept just the polygon and figure out the rest of the data we need.

We still want to make sure our dinosaurs are all on the island.

Helper Functions:

Here are examples of a couple useful functions.

SDO_GEOM.SDO_MBC_CENTER accepts an SDO_GEOMETRY object and a tolerance. The function calculates the Minimum Bounding Circle, which is the smallest circle that could contain the object. It then returns an SDO_GEOMETRY point object for the calculated center of the MBC.

SDO_GEOM.SDO_MBC_RADIUS accepts an SDO_GEOMETRY object and a tolerance. The function calculates the Minimum Bounding Circle, which is the smallest circle that could contain the object. It then returns the calculated radius of the MBC.

Exercise:

Change the generate_random_location function so it accepts:

  • The island as an SDO_GEOMETRY simple polygon object.

The function should return a valid SDO_GEOMETRY point object located inside the island polygon.

New generate_random_location.fnc

Change populateData.sql so it uses the new function to update the dinosaurs locations using only the island.

New populateData.sql

Find the Dinosaurs

Run populateData.sql to generate an island and update the dinosaur locations.

Now that we have an island and we’ve scattered the dinosaurs, let’s figure out where they are.

Helper Functions:

Here are examples of a couple useful functions.

SDO_WITHIN_DISTANCE accepts two SDO_GEOMETRY objects and a parameter string. The first SDO_GEOMETRY object must be in a table and have a spatial index. The parameter string is a set of parameters defined in the linked document, for our purposes we are using distance and unit. If the two SDO_GEOMETRY objects are within the given distance the function returns ‘TRUE’.

SDO_RELATE accepts two SDO_GEOMETRY objects and a mask parameter. The mask parameter specifies the relationship you are testing. See the Usage Notes section of the linked documentation for a list of valid options.

Alternatively, if your objects are not both in tables or you are trying to determine their relationship you could use SDO_GEOM.RELATE but it is less efficient since it does not leverage a Spatial Index.

Exercises:

  1. Write a query that finds all of the dinosaurs within 5 KM of the beach.
  2. Write a statement that creates a smaller polygon with a minimum radius of 10 KM and centered randomly inside the island. Using the location_name of ‘asteroid-impact’ insert this polygon into the dd_locations table.
  3. Write a query that finds the dinosaurs inside the ‘asteroid-impact’ so we can warn them to move to safety.
Answer 1
Answer 2
Answer 3

Summary

There are many more functions and capabilities included with Oracle Spatial, this tutorial has barely scratched the surface.

The ‘answers’ I have included are merely suggestions and there are many other ways to complete the same tasks. Please dig into the documentation and experiment.

Please leave comments if you have any questions, corrections or suggestions.

Getting Started With Oracle Spatial part 1

In this tutorial, we’ll go over how to create some basic spatial objects such as points, lines and polygons. We’ll create some random objects and run some fun queries.

We’ll be using the Spatial and Graph Developer’s Guide as our reference.

First, let’s take a look at the SDO_GEOMETRY Object.

SDO_GTYPE is a 4 digit number that defines what type of object we’re making, using a format of DLTT

D = Dimensions of the object 2, 3 or 4 (can not mix different dimensions in the same layer)

L = Linear Referencing System. This is a more advanced topic for another day. For our examples, we will be using 0.

TT = Type of Geometry, 00 through 09

We will be using the following Geometry Types. You can find the full list here.

Value Description
DL01 Point Geometry contains one point.
DL02 Line or Curve Geometry contains one line string that can contain straight or circular arc segments, or both. (LINE and CURVE are synonymous in this context.)
DL03 Polygon or Surface Geometry contains one polygon with or without holes,1 or one surface consisting of one or more polygons. In a three-dimensional polygon, all points must be on the same plane.

SDO_SRID is a number referencing the coordinate system used, it must either be NULL or the SRID value from the SDO_COORD_REF_SYS table.  We’ll be using 4326, which is the standard Earth-based Latitude / Longitude system.

SDO_POINT is used to define the object as a point. This is only used if this object is a point, otherwise, this should be NULL and the following two parameters will define the object.

SDO_ELEM_INFO is basically a set of numbers that work like a key for using the information in the SDO_ORDINATES parameter to create the object. These numbers are grouped into one or more sets of 3 numbers or triplets. If we are creating a complex object that has more than one segment, each segment will be defined by its own triplet.

  • The first number in the triplet is the SDO_STARTING_OFFSET and indicates which number in the following SDO_ORDINATE_ARRAY is the starting point for this segment. The first element is 1.
  • The second number is the SDO_ETYPE which is used to define the type of object we are creating in this segment. For example, 2 is a straight line and 1003 is a simple polygon.
  • The third number is the SDO_INTERPRETATION and is used in one of two ways.
    • For simple objects such as a polygon, the SDO_INTERPRETATION will further define the type of polygon. 1 is a simple straight line polygon, 2 uses circular arcs, 3 is a rectangle and 4 is a circle.
    • For complex objects such as a polygon with holes in it, SDO_INTERPRETATION is used to specify how many ordinates are used for this segment.

Please see Table 2-2 Values and Semantics in SDO_ELEM_INFO for further details.

SDO_ORDINATES are pairs of coordinates used along with SDO_ELEM_INFO to define the object

Setup

You’ll need a copy of the git repository.

  1. Go to https://github.com/oracle/dino-date
  2. Fork the repository.
  3. Clone the repository.
  4. Follow the DinoDate install instructions.
  5. Run the following as the new DD user.

This script will add the sptut_location column to our dd_members table, setup the spatial metadata and create the spatial indexes. We will not be covering these steps in the tutorial.

 

This script can also be used to reset the database if something goes wrong.

We’ll need some dinosaurs to locate so let’s randomly generate a few.

Connected as dd run the following command to generate 1,000 dinosaurs.

Cleanup

If you’d like to restore the DD schema to its default state, run this script to remove the new column and re-create the dd_locations index.

View the Objects

For my examples, I will be using Oracle SQL Developer which includes a Map View utility to display the objects.

Create a Point

Select the following example from dual to create a point.

When you run the query you’ll see it generates an SDO_GEOMETRY type object. In SQL Developer you can select the results, right click the object and “Invoke Map View on Results Set” to display the point in the Map Viewer.

Note that there is no background map displayed, just an empty background with a point. If there were a map, this point would be just west of Africa.

Exercise:

Create a point at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920.

If you invoke the map view for both points, you can change the marker type for one to identify which is which. If your Bermuda Triangle point is South East of 0,0 you have your Lat and Lon backward. If this is a consistent issue, you can use named parameters.

Answer

Create a Line

Select the following example from dual to create a line.

This creates a line starting at Lon-0, Lat-0 and ending at Lon-0.9, Lat-0, approximately 100KM East.

Exercise:

Create a line starting at the center of the Bermuda Triangle – Latitude 26.846786, Longitude -69.322920 and going approximately 100KM North. (0.9 is approximately 100KM)

Answer

Experiment with other lines. Keep in mind if your object is “off the map”, it will be invalid.

In the next post, I’ll go over creating polygons and run some fun spatial queries.

Delete (cruD) using Ruby-OCI8

In this post, we’re going to take a look at the D in CRUD: Delete.

We use the ruby-oci8 driver to delete some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the deletes worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.rb with the following code and run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)

Boilerplate template

The template we will be using is:

For each exercise, replace the “# Your code here” line with your code.

Reset the data

First, let’s run reset_data.rb to set up our data.

Simple delete

We will perform a simple delete that removes a single record from the lcs_people table.  These are the steps performed in the code snippet below.

  • Prepare a SQL DELETE statement, deleting the record with an id of 1.
  • Parse the statement to create a cursor.
  • Bind the id value.  (See the R part of this series for an explanation of bind variables.)
  • Execute the statement.
  • Commit the transaction.
When I run this code in my Ruby session, I see:

Extra Fun 1

Delete all the birds.

Your results should be:

Answer
Reset the data

Now is a good time to run reset_data.rb.

Boilerplate change

Change the boilerplate get_all_rows statements to get people and pet data.

Deleting records referenced by Foreign Keys

If you are using integrity constraints in your database (of course you are, because then you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes.

In our design, we have a Foreign Key constraint in lcs_pets that ensures if a pet has an owner, that owner exists.

This is the statement that creates the constraint in the Creating the Database Objects section of the Initial Setup post.

If we attempt to delete a record in lcs_people that is referenced in lcs_pets (Person has a pet,) we get an error.

When I run this code in my Ruby session, I see:

Before deleting the person you have to handle the pet (watch out for claws and teeth).

There are a few options here, depending on your database design:

  • If: pets are not required to have an owner and you only want to delete the person, not the pets.  Then: you can update the pets and set their owner to null.
  • If: pets are required to have an owner.  Then: you can delete the pets for the owner.

In either of the above scenarios, you can update the pets and set their owner to another person.

Bob is moving out of our area and his new apartment doesn’t allow pets, so he’s giving them to Kim.  Let’s use that last option here.

  • Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob).  Updating is covered in the U part of this series.
  • Parse the statement to create a cursor.
  • Bind the new and old owner values.
  • Execute the statement.
  • Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
  • Parse the statement to create a cursor.
  • Bind the id value.
  • Execute the statement.
  • Commit both transactions.
When I run this code in my Ruby session, I see:

When you change data it’s a good idea to verify the number of affected rows.  This is covered in the R part of this series.

Extra Fun 2

Due to a zoning change, snakes are no longer allowed in our area.  Stacey has decided to move and take Sneaky with her.

Let’s fix our data.

Your results should be:

Answer
Some other things you could try
  • Change the database constraints to delete or Null the child record on delete (a cascading delete).  Delete a person and let the database handle the children.
  • Remove the people who don’t have any pets.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Delete (cruD) using Perl and DBD::ORACLE

In this post, we’re going to take a look at the D in CRUD: Delete.

We use the DBD::Oracle driver to delete some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows encapsulates a select statement used to verify that the deletes worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.perl with the following code and run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)