In the previous post, we explored creating basic geometrical shapes with Oracle Spatial with the intention of creating an island that the dinosaurs using DinoDate can live on.
One of the main reasons our dinosaurs have stayed hidden for so long is the island they live on constantly changes shape. Let’s build on the function we created in the previous post and add a little randomness to it.
First, let’s change the parameters of the function.
|
1 2 3 4 5 6 7 8 |
create or replace function generate_shape( p_lon in number default 0, p_lat in number default 0, p_sides in number default 1, p_radius in number default 0, p_start_radian in number default 0) return sdo_geometry is |
We’re going to have a random number of ‘sides’ so we can get rid of p_sides and since it will be a random shape we don’t need p_start_radian.
To control the how ‘spiky’ the island is, we’ll add a parameter that accepts a variance percent. We’ll use this as a percentage of the given minimum radius and calculate an upper limit for the radius.
Let’s add a constant for the upper limit of our island radius and a variable to track our current radian.
|
1 2 3 4 5 6 7 8 9 |
create or replace function generate_polygon_rad( p_min_radius in number, p_variance_pct in number, p_lon in number default 0, p_lat in number default 0) return sdo_geometry is c_upper_limit constant number := p_min_radius + p_min_radius*(p_variance_pct/100); v_cur_radian number := 0; |
We’re only generating a polygon, this time, so we’ll extract the loop and drop most of the rest of the code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
for i in 1..p_sides loop select t.x, t.y into v_x, v_y from table(sdo_util.getvertices( sdo_util.point_at_bearing( sdo_geometry(2001, 4326, sdo_point_type(p_lon, p_lat, null), null, null), v_cur_radian, p_radius))) t; /* add x and y value of new point */ v_ordinate_array_points.extend(2); v_ordinate_array_points(v_ordinate_array_points.count-1) := v_x; v_ordinate_array_points(v_ordinate_array_points.count) := v_y; /* It's a line, we only need the start and end points*/ exit when p_sides < 3; /* decrement the current radian by c_radian_increment so we move in a counter clockwise direction */ v_cur_radian := v_cur_radian - c_radian_increment; end loop; |
We still want to make a complete polygon but we don’t know how many sides we’ll be randomly creating. Let’s change the for loop to a while loop and we’ll loop until we exceed 2pi.
Our first point will start with a 0 radian. Remember we consider a 0 radian to be pointing in the direction of 12 o’clock.
Instead of subtracting radians we’ll convert our radian to a negative value when we generate the point. Either way is fine, so we’ll use this method this time to demonstrate both options.
This time, we’ll generate a radius with a random length from our p_min_radius to the c_upper_limit.
Finally, we’ll generate a new radian from .1 to .5 radians and increase the v_cur_radian.
The loop will continue until our v_cur_radian is > 2pi (asin(1)*4).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
/* Each itteration will add a point at a random distance from the center point on the current radian. asin(1)*4 = 2pi */ while v_cur_radian < asin(1)*4 loop select t.x, t.y into v_x, v_y from table(sdo_util.getvertices( sdo_util.point_at_bearing( sdo_geometry(2001, 4326, sdo_point_type(p_lon, p_lat, null), null, null), /* Since sdo_util.point_at_bearing increments radian in a clockwise direction and a valid polygon must be defined in a counter-clockwise direction we'll change our v_cur_radian to a negative value. */ v_cur_radian * -1, round(dbms_random.value(p_min_radius,c_upper_limit))))) t; /* add x and y value of new point */ v_ordinate_array_points.extend(2); v_ordinate_array_points(v_ordinate_array_points.count-1) := v_x; v_ordinate_array_points(v_ordinate_array_points.count) := v_y; /* increment the current radian by .1 to .5 radians */ v_cur_radian := v_cur_radian + round(dbms_random.value(.1,.5),2); end loop; |
When the loop is complete we’ll copy the starting point to the end point position to close our polygon and return a new island for our dinosaurs to live on.
Now we’ll use the new function to generate a randomly shaped polygon with a radius from 1000 to 1200 meters and we’ll put it at the center of the Bermuda Triangle.
|
1 |
select dd.generate_polygon_rad(p_min_radius => 1000, p_variance_pct => 20, p_lon => -69.322920, p_lat => 26.846786) from dual; |
You can find the full (nicely formatted) function in this gist.
Next time we’ll add some dinosaurs to our new island.