Skip to content

Instantly share code, notes, and snippets.

Last active June 17, 2016 21:19
Show Gist options
  • Save OsBlaineOra/c347a6ac32a857e15c4337a307bdd276 to your computer and use it in GitHub Desktop.
Save OsBlaineOra/c347a6ac32a857e15c4337a307bdd276 to your computer and use it in GitHub Desktop.
Oracle Spatial generate_shape function
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
v_generated_shape sdo_geometry;
v_cur_radian number := p_start_radian;
c_radian_increment CONSTANT number := (asin(1)*4)/p_sides;
v_x number;
v_y number;
/* array of points used to construct the shape */
v_ordinate_array_points sdo_ordinate_array := sdo_ordinate_array();
/* It's a point */
if p_radius = 0 then
v_generated_shape := SDO_GEOMETRY(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 at p_lon,p_lat
NULL, --not used for a point
NULL); --not used for a point
each itteration will add a point at a distance of p_radius from the center point
on the current radian.
asin(1)*4 = 2pi
for i in 1..p_sides loop
select t.x, t.y
into v_x, v_y
from table(sdo_util.getvertices(
sdo_point_type(p_lon, p_lat, null), null, null),
) t;
/* add x and y value of new point */
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;
/* It's a line */
if p_sides < 3 then
v_generated_shape := SDO_GEOMETRY(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
/* It's a polygon */
/* add x and y value of first point as the last point of the polygon to close it */
v_ordinate_array_points(v_ordinate_array_points.count-1) := v_ordinate_array_points(1);
v_ordinate_array_points(v_ordinate_array_points.count) := v_ordinate_array_points(2);
v_generated_shape := sdo_geometry(2003, -- 2 dimensions
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
end if;
end if;
return v_generated_shape;
end generate_shape;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment