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.
1 2 3 4 5 6 7 8 |
select SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(MDSYS.SDO_GEOMETRY(2002, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(-69.322920, 26.846786, -69.322920, 27.746786)), 0.005) line from dual; |
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.
1 2 3 4 5 6 7 8 |
select MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1<sup>st</sup> oordinate 1003, --an exterior polygon 3), --a rectangle MDSYS.SDO_ORDINATE_ARRAY(0,0, 0.9,0.9)) -–corners from dual; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 |
select MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1<sup>st</sup> oordinate 1003, --an exterior polygon 1), --a simple polygon MDSYS.SDO_ORDINATE_ARRAY(0,0, -–first corner 0.9,0, 0.9,0.9, 0,0.9, 0,0)) -–last corner is the same as the first from dual; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
select MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, --start with 1<sup>st</sup> oordinate 1003, --an exterior polygon 1), --a simple polygon MDSYS.SDO_ORDINATE_ARRAY(0,0, -–first point 0.2,-0.34, 0.5,0.14, 0.93,-0.21, 0.87,0.39, 0.96,0.48, 0.75,0.91, 0.62,0.81, 0.48,0.96, 0.1,0.89, -0.2,0.67, 0.1,0.31, 0,0)) -–last point is the same as the first from dual; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
delete from dd_locations where location_name = 'dino-island'; update dd_members set location = null where location is not null; insert into dd_locations (location_name, geometry) values ('dino-island', ....); update dd_members_t set location = .... where member_id = 0; commit; |
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’.
1 2 3 |
insert into dd_locations (location_name, geometry) values ('dino-island', …) |
2. Update our admin dinosaur (member_id = 0) and set the location to a point inside of ‘dino-island’.
1 2 3 |
update dd_members_t set location = … where member_id = 0; |
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.
1 2 3 4 5 6 7 8 |
select SDO_UTIL.POINT_AT_BEARING(SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(0, 0, NULL), NULL, NULL), 1, 100) from dual; |
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.)
1 2 3 4 5 6 |
SELECT t.x, t.y from TABLE(SDO_UTIL.GETVERTICES(MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(0.000755906246724255,0.000488632913734697,NULL), NULL, NULL))) t; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select SDO_GEOM.SDO_INTERSECTION( sdo_geometry (2002, 4326, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (0,0, 1.95,1.95) ), MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3), MDSYS.SDO_ORDINATE_ARRAY(0,0, .9,.9)), 0.005 --Tolerance ) intersection from dual; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select SDO_GEOM.SDO_LENGTH(SDO_GEOM.SDO_INTERSECTION( sdo_geometry (2002, 4326, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array (0,0, 1.95,1.95) ), MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3), MDSYS.SDO_ORDINATE_ARRAY(0,0, .9,.9)), 0.005 --Tolerance ), 0.005) line_length --Tolerance from dual; |
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.
1 2 3 4 5 6 7 |
select sdo_geom.sdo_mbc_center(mdsys.sdo_geometry(2003, 4326, null, mdsys.sdo_elem_info_array(1, 1003, 3), mdsys.sdo_ordinate_array(0,0, .9,.9)), 0.005) --Tolerance from dual; |
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.
1 2 3 4 5 6 7 |
select sdo_geom.sdo_mbc_radius(mdsys.sdo_geometry(2003, 4326, null, mdsys.sdo_elem_info_array(1, 1003, 3), mdsys.sdo_ordinate_array(0,0, .9,.9)), 0.005) --Tolerance from dual; |
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.
1 2 3 4 5 6 7 8 9 10 |
select * from dd_members dinos where dinos.location is not null and sdo_within_distance (dinos.location, mdsys.sdo_geometry(2001, 4326, mdsys.sdo_point_type(-69.322920,26.846786, null), null, null), 'distance=10 unit=km') = 'TRUE'; --parameter string |
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’.
1 2 3 4 5 |
select a.gid from polygons a, query_polys B where B.gid = 1 and SDO_RELATE(A.Geometry, B.Geometry, 'mask=touch') = '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.
1 2 3 4 5 6 |
select sdo_geom.relate(sdo_geometry(2001,4326,mdsys.sdo_point_type(-69.343504,26.803024,null),null,null), 'DETERMINE', --mask parameter Geometry, 0.005) from dd_locations where dd_locations.location_name = 'dino-island'; |
Exercises:
- Write a query that finds all of the dinosaurs within 5 KM of the beach.
- 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.
- 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.