Lately, I’ve been digging into Oracle Spatial.  It’s a lot of fun, but wow, there’s a lot to learn.

I’m working on a series of tutorials that will walk through the process of building an island for our DinoDate dinosaurs to live on.  But in the meantime, I thought we could have some fun and look at a function to generate simple shapes.

For a detailed explanation of the SDO_GEOMETRY object, you can check out the docs, or watch for my upcoming tutorial.  In this post, I’ll just add brief comments to explain the parameters.

I realize the code formatting isn’t the best on this post, so I’ve created a gist with the finished function.

Our function accepts a latitude and longitude we’ll use to position our shape, the number of sides, a radius and a starting radian.

A Point

Accepting the defaults gives us a 1 sided object with a 0 radius, sounds like a point to me.  In fact, let’s make anything with a radius of 0 a point.

• 2001, –2 dimensions, 0 LRS, 01 is a point
• 4326, –SRID for Earth lat/lon system
• MDSYS.SDO_POINT_TYPE(p_lon, p_lat,NULL), –point located p_lon, p_lat
• NULL, –not used for a point
• NULL) –not used for a point;

Generating Shapes

When we generate the rest of our shapes, we will:

1. Start with the given point at p_lon / p_lat.
2. Go a distance of p_radius in meters.
3. In a direction of p_start_radian.
4. Get the lat / lon position of the new point.
5. Decrement our current radian by (2pi / sides. ) 2pi = (asin(1)*4)
6. Repeat for each corner.

If you’re like me and it’s been so long since you took a trig class that you’re a bit fuzzy on what a radian is here’s an excellent definition.  The example in that post shows a 0 radian starting in the 3 o’clock position and moving counter clockwise.  use a 0 radian at the 12 o’clock position and move clockwise.

Generating Corner Points

The function we’re using to generate corner points is sdo_util.point_at_bearing.  It considers 0 radians to be at the 12 o’clock position and moves clockwise.

Parameters:

• sdo_geometry point object
• radian from 0 to 2pi
• radius in meters

Once we have the new point, we’ll get the x / y coordinates of the point using sdo_util.getvertices and we’ll add those values to an sdo_ordinate_array.

The sdo_ordinate_array defines the corner point for our shapes (or end points for a line) and the sdo_elem_info_array acts as a key used to define how we use those coordinates.

Once again, if you want to dig into the details look at the docs, but here I’ll add short definitions.

Let’s add some new variables to work with.

A Line

A 1 sided object with a radius > 0 could be a line or a circle.  I’ve chosen to use an SRID of 4326 which is a lat/lon earth based system and is not a geodetic coordinate system, so circles are a bit more complex than I want to cover in this post.  So, a line it is and we’ll include 2 sided objects as lines also just to keep it simple.

I know, I used a bunch of fancy words and didn’t explain them.  I’m trying to keep this post simple, but if you’d like to dig into all of the details, take a look at the docs.

• 2002, –2 dimensions, 0 LRS, 02 is a line
• 4326, –SRID for Earth lat/lon system
• NULL, — only used for a point
• SDO_ELEM_INFO_ARRAY(1, –start with 1st ordinate
  • 2, –a straight line
  • 1), –a simple straight line
• SDO_ORDINATE_ARRAY(p_lon, p_lat, –start coordinates
• v_ordinate_array_points(1), v_ordinate_array_points(2))); –end coordinates

For a line, we use the p_lon, p_lat parameters as our first point and our generated values as our second point.

Polygons With 3 or More Sides

For our polygons, we use the p_lon, p_lat parameters as a center point and generate our corner points around the center.

When you define a simple polygon the exterior edge points need to be defined in a counter-clockwise direction.  If you create more complex polygons, such as a polygon with holes, the interior polygons are defined in a clockwise direct.  Check out the docks if you want to experiment with more complex objects.

When we define a closed polygon, our sdo_ordinate_array will contain a set of points for each corner.  In order to close our polygon, we add an extra point at the end that is the same as the start point.

• 2003,  — 2-dimensional polygon
• 4326, –SRID for Earth lat/lon system
• NULL, — only used for a point
• SDO_ELEM_INFO_ARRAY(1, –start with 1st ordinate
  • 1003, — simple polygon
  • 1), — simple polygon
• v_ordinate_array_points –coordinate array

Let’s make some shapes

First, we’ll make a point at the center of the Bermuda Triangle.

Using the SQL Developer Map View we can see our point.

Now let’s add a 100-meter line at 0 radians.

We’ll move the starting radian to -.75 * pi.  We’ll also set p_sides to 2 since we treat 1 and 2 as a line.

From here on, we’ll leave this point and line on the view for reference and we’ll just increment the p_sides parameter.

I think that’s enough examples.  I hope you’ve found this useful or at least a little fun.

Feel free to leave questions or suggestions in the comments.