Use Oracle Spatial to locate the dinosaurs

In a previous post, we used Oracle Spatial to generate a random polygon we can use as a home for our dinosaurs.  Now, let’s have the dinosaurs using DinoDate tell us where they are so we can update the system.

Our goal is to create a function that accepts a valid polygon (the island) and returns a point at a random location inside that polygon.  We’ll call it generate_random_location.  We’ll also add in a bunch of variables we’re going to need.

As we discussed in the previous post, every so often the island the dinosaurs live on randomly changes shape and possibly location. When this happens, the dinosaurs all group up at the center of the island until it’s reformed then they move out at random from there.  (At least that’s the best “explanation” I can come up with to avoid a discussion of “true randomness” in the distribution of points.)

We’re going to use some of the same Oracle Spatial functions we used in the previous post to generate a point (x) kilometers from the island’s center in a random direction and we’ll take a look at some new functions.

In the sdo_geom package, there are some functions we can use to determine properties of the minimum bounding circle (MBC) for our polygon.  This is basically the smallest circle that our island will fit in within a given tolerance.  We’ll need to figure out the coordinates for the center of the island and its maximum radius.

We’ll add a little to the radius just to make sure we have a distance slightly larger than the island and we’ll get the X, Y coordinates for the center.

Let’s create a random radian, generate a point in that direction at a distance of our v_radius we calculated above and get it’s X, Y coordinates.

We’re going to use this new point and the center of the island to create a line.  Then, using another function from the sdo_geom package, sdo_intersection,  we can create a line that is just the intersection of the new line and our island.  The length of this line tells us how far it is from the center of the island to the edge in the given direction.

Finally, we generate a new point along our new_radian at a random distance from 0 (center of the island) to v_length (edge of the island) and return that point.

You can find the full formatted code in this gist.

Now let’s figure out where those dinosaurs are.

Before we get started, let’s clear out any pre-existing data.

We’re going to need a couple variables to hold a new island and a list of dinosaurs.

Using the function from the previous post, we can create a new island at the center of the Bermuda Triangle, and insert it into the location table.

For reasons explained here, we’re going to use a bulk collect followed by a forall update instead of a simple update statement, to populate the dinosaurs locations.

Finally, we can print out the number of changed rows and commit.

You can find the full formatted code in this gist.

Using Oracle SQL Developer, we can see our island in the Map View.

Screenshot from 2016-07-26 14-48-31

We’ll put a dot up for each dinosaur on the island.

Screenshot from 2016-07-26 14-50-35

Notice the points tend to cluster in the center and spread out from there.  That’s due to always using the center point as one end of our random calculation.

Please experiment with other options and share in the comments below.

Leave a Reply