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)

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

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

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’.

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.

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.

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

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.

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

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.

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.