### Using SDO_UTIL Functions to Construct Geodetic Shapes

#### By jeanihm-Oracle on Mar 13, 2015

Guest Post By: Nick Salem, Distinguished Engineer, Neustar

Note: Thanks to Nick Salem, Technical Chair of the Oracle Spatial SIG , for contributing another valuable tip and script example! This is very useful for those who want to learn how to create grids for trade area/demographic analysis.

Oracle Spatial & Graph provides a feature rich toolkit for building and manipulating various types of geometries. There are a lot of great helper functions that you can utilize to construct a specific shape type. At Neustar, we leverage these functions to create square and hexagonal grids that we can overlay over a site’s trade area or some market. These grids can be then scored to show the variation of data across an area.

Below is a picture from Neustar’s ElementOne platform displaying a 0.5 mile grid layer over a 3 mile radius of a prospect bank site. The grids are scored by the number of households that have a bank savings account from high to low. Square grids are simple polygons that are easy to create in a planar surface since they have edges of equal length. However in a geodetic projection, you cannot simple use simple geometric calculations to construct these shapes or at least not as accurately as you may want especially if you are covering a larger area. Oracle provides the SDO_UTIL.CONVERT_UNIT and the SDO_UTIL.POINT_AT_BEARING functions that can be helpful in creating shapes on geodetic surfaces. You can use the CONVERT_UNIT function to convert units from degrees to radians and the POINT_AT_BEARING to place individual coordinates of a geometry.

Here’s a picture of a 1 mile hexagonal grid tessellation for the number of households with a home equity loan for the county of Anne Arundel in Maryland. Hexagonal grids have one advantage over square grids in that they are more circular in shape and so the distance from the center to the sides and edges is even.

Next I am going to show a simple PL/SQL script to create a simple trapezoid shape using the SDO_UTIL.CONVERT_UNIT and SDO_UNIT.POINT_AT_BEARING functions. In this example, I begin with a starting point located in San Diego county and then go in a counter clockwise order to plot the subsequent coordinates that will make up the trapezoid polygon. The angle passed into the SDO_UTIL.POINT_AT_BEARING function is measured clockwise from due north (i.e. bearing 0 is north, bearing 90 is east, bearing 180 is south, bearing 270 is west). I am creating a trapezoid polygon here as an example since a trapezoid can represent the top or bottom part of a hexagon.

**Simple PL/SQL script
example: **

declare

startPoint sdo_geometry;

nextPoint sdo_geometry;

coords sdo_ordinate_array
:=sdo_ordinate_array();

shape sdo_geometry;

begin

-- first starting point

startPoint :=
sdo_geometry(2001,4326,

sdo_point_type(-117.0655,32.7475,null),null,null);

coords.extend(2);

coords(coords.count-1) :=
startPoint.sdo_point.x;

coords(coords.count) :=
startPoint.sdo_point.y;

-- place second coordinate 5,000 meters away using 330 (northwest) degree angle

nextPoint :=
sdo_util.point_at_bearing( start_point => startPoint,

bearing
=> sdo_util.convert_unit(330,'degree','radian'),

distance => 5000 );

coords.extend(2);

coords(coords.count-1) := nextPoint.sdo_point.x;

coords(coords.count) :=
nextPoint.sdo_point.y;

-- place third coordinate 3,000 meters away using 270 (west) degrees angle

startPoint := nextPoint;

nextPoint :=
sdo_util.point_at_bearing( start_point => startPoint,

bearing
=> sdo_util.convert_unit(270,'degree','radian'),

distance => 3000 );

coords.extend(2);

coords(coords.count-1) := nextPoint.sdo_point.x;

coords(coords.count) :=
nextPoint.sdo_point.y;

-- place fourth coordinate 5,000 meters away using 210 (southwest) degrees angle

startPoint := nextPoint;

nextPoint :=
sdo_util.point_at_bearing( start_point => startPoint,

bearing =>
sdo_util.convert_unit(210,'degree','radian'),

distance => 5000 );

coords.extend(2);

coords(coords.count-1) := nextPoint.sdo_point.x;

coords(coords.count) :=
nextPoint.sdo_point.y;

-- add starting coordinate as 5th coordinate to close the polygon

coords.extend(2);

coords(coords.count-1) := coords(1);

coords(coords.count) := coords(2);

-- construct shape geometry

shape :=sdo_geometry( 2003,
4326,null,

sdo_elem_info_array(1,1003,1), coords ) ;

end;

Here’s the result of the shape geometry from script above displayed on a map.

In conclusion, Oracle Spatial & Graph provides us with some nice helper functions that we can use to construct shapes on a geodetic surface. In this post, we demonstrated how one can use the SDO_UTIL.POINT_AT_BEARING and SDO_UTIL.CONVERT_UNIT to plot coordinate points for a geometric polygon.