Oracle Spatial for the thrifty developer: Part 1

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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s