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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
create or replace function generate_random_location(p_island in sdo_geometry) return sdo_geometry is v_center sdo_geometry; v_radius number; new_radian number; new_point sdo_geometry; v_lon number; v_lat number; v_x number; v_y number; new_line sdo_geometry; final_point sdo_geometry; v_length number; begin |
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.
|
1 2 |
v_center := sdo_geom.sdo_mbc_center(p_island, 0.005); v_radius := sdo_geom.sdo_mbc_radius(p_island, 0.005) * 1.1; |
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.
|
1 2 3 |
/* get the island center's x y */ select t.x, t.y into v_lon, v_lat from table(sdo_util.getvertices(v_center)) t; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/* random radian from 0 to 2pi */ new_radian := dbms_random.value(0,asin(1)*4); /* generate a point on the new radian outside of the polygon */ new_point := sdo_util.point_at_bearing( sdo_geometry(2001, 4326, sdo_point_type(v_lon, v_lat, null), null, null), new_radian, v_radius ); /* get the new point's x y */ select t.x, t.y into v_x, v_y from table(sdo_util.getvertices(new_point)) t; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/* create a line from the center to the new point get the segment that is contained inside the polygon */ new_line := sdo_geom.sdo_intersection( sdo_geometry (2002, 4326, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (v_lon, v_lat,v_x, v_y) ), p_island, 0.005 ); /* get the length of the new line */ v_length := round(sdo_geom.sdo_length(new_line, 0.05)); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* using the length of the new line, generate a point on the current radian with a random distance from 0 to the lenght of the line */ final_point := sdo_util.point_at_bearing( sdo_geometry(2001, 4326, sdo_point_type(v_lon, v_lat, null), null, null), new_radian, dbms_random.value(0,v_length) ); return final_point; end; |
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.
|
1 2 3 4 5 6 7 8 |
update dd_members set location = null where location is not null; delete from dd_locations where location_name = 'dino-island'; commit; |
We’re going to need a couple variables to hold a new island and a list of dinosaurs.
|
1 2 3 4 5 6 7 8 9 10 11 |
declare v_dino_island sdo_geometry; type t_member_location is record (member_id dd_members.member_id%type, new_location sdo_geometry); type t_member_ids is table of t_member_location index by pls_integer; l_member_ids t_member_ids; begin |
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.
|
1 2 3 4 |
v_dino_island := dd.generate_polygon_rad(100000, 50, p_lat => 26.846786, p_lon => -69.322920); insert into dd_locations (location_name, geometry) values ('dino-island', v_dino_island); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select member_id, generate_random_location(v_dino_island) bulk collect into l_member_ids from dd_members fetch next 1000 rows only; forall indx in 1 .. l_member_ids.count update dd_members_t set location = l_member_ids (indx).new_location where member_id = l_member_ids (indx).member_id; dbms_output.put_line(sql%rowcount); commit; end; |
You can find the full formatted code in this gist.
Using Oracle SQL Developer, we can see our island in the Map View.
|
1 2 3 |
select geometry dinoisland from dd_locations where location_name = 'dino-island'; |
We’ll put a dot up for each dinosaur on the island.
|
1 2 3 |
select location from dd_members where location is not null; |
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.