Oracle Spatial contains a wealth of spatial functions that cater for most needs. Problem is of course they all come with a ridiculously high price tag! No matter how simple your application may be, if you want to take full advantage of spatial functionality you have to pay for the Enterprise Edition of Oracle, plus the so-called ‘Spatial Option’ i.e. 1000’s of euros (or dollars, or whatever). A price that actually got increased in June 2009 as reported here.

Luckily, Oracle also provides Oracle Locator which is available on every Oracle version and is a cut down version of spatial which allows you to basically store spatial data in your tables along with some (actually 3) spatial functions. These are:

- SDO_GEOM.SDO_DISTANCE()
- SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
- SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()

So what I will try to do here is attempt to extent this poor offering, by providing a set of simple spatial functions that can be run on any Oracle edition including Oracle 10g Express. This obviously by no means intends to replace the full- blown oracle functions but should hopefully be useful for people who require some simple spatial functionality for their applications. This ‘series’ of posts will start with some simple coordinate geometry functions and will end (at least at the time of writing these lines) with creating some somewhat more complex routines like returning a point along a line at a specified instance and line merging.

**Create a Point from a pair of XYs**

Dead simple as you may expect, the function in question is:

`FUNCTION get_pt_from_xy(pi_x IN NUMBER`

,pi_y IN NUMBER

,pi_srid NUMBER DEFAULT NULL) RETURN mdsys.sdo_geometry IS

retval mdsys.sdo_geometry;

BEGIN

--

retval := mdsys.sdo_geometry(2001,

pi_srid,

mdsys.sdo_point_type(pi_x,pi_y,NULL),

NULL,

NULL);

RETURN retval;

END;

**Get Length from a pair of XYs**

This function returns the straight line/cartesian length given the start and end XY coordinates

`FUNCTION get_length_from_xy(pi_x1 NUMBER`

,pi_y1 NUMBER

,pi_x2 NUMBER

,pi_y2 NUMBER) RETURN NUMBER IS

retval NUMBER;

BEGIN

retval := sqrt(power((pi_x2 - pi_x1),

2) + power((pi_y2 - pi_y1),

2));

RETURN retval;

END get_length_from_xy;

**Get the angle from a pair of XYs**

This function returns the the angle between 2 points defined by start and end XY coordinates

FUNCTION get_grad_from_xy(pi_x1 NUMBER

,pi_y1 NUMBER

,pi_x2 NUMBER

,pi_y2 NUMBER) RETURN NUMBER IS

retval NUMBER;

dx NUMBER;

dy NUMBER;

gab NUMBER;

BEGIN

dx := pi_x2 - pi_x1;

dy := pi_y2 - pi_y1;

gab := atan(abs(dx / dy));

------Find quadrant ---------

-- IF DX >0

IF dx > 0 THEN

IF dy > 0 THEN

retval := gab;

END IF;

IF dy = 0 THEN

retval := c_pi / 2;

END IF;

IF dy < 0 THEN

retval := c_pi - gab;

END IF;

END IF;

-- IF DX < 0

IF dx < 0 THEN

IF dy > 0 THEN

retval := (c_pi * 2) - gab;

END IF;

IF dy = 0 THEN

retval := (c_pi * 1.5) - gab;

END IF;

IF dy < 0 THEN

retval := c_pi + gab;

END IF;

END IF;

-- IF DX=0

IF dx = 0 THEN

IF dy > 0 THEN

retval := 0;

END IF;

IF dy = 0 THEN

-- Start and end points are the same!

raise_application_error(-20014,'Could not determine the angle');

END IF;

IF dy < 0 THEN

retval := c_pi;

END IF;

END IF;

RETURN retval;

END get_grad_from_xy;

**Return distance at Point**

This function returns the point at a specified distance and angle from another given point.

`FUNCTION get_point_at_distance(pi_x NUMBER`

,pi_y NUMBER

,pi_dist NUMBER

,pi_grad NUMBER

,pi_srid NUMBER DEFAULT NULL)

RETURN mdsys.sdo_geometry IS

----------------------------------------------------------------------------------------

n_x NUMBER;

n_y NUMBER;

retval mdsys.sdo_geometry;

BEGIN

n_x := pi_x + pi_dist * sin(pi_grad);

n_y := pi_y + pi_dist * cos(pi_grad);

retval:=mdsys.sdo_geometry(2001

,pi_srid

,mdsys.sdo_point_type(n_x,n_y,NULL)

,NULL

,NULL);

RETURN retval;

Next : Oracle Spatial for the thrifty developer- Part 2: Calculating line lengths and more…