This discussion is archived
2 Replies Latest reply: Nov 16, 2008 1:51 AM by Luc Van Linden

# Query based on direction

Currently Being Moderated
Hi

Does anyone know if its possible to query data based on direction eg. select all objects that are east of object A.

Thanks

K
• ###### 1. Re: Query based on direction
Currently Being Moderated
K

Take a look at Re: Spatial Operators

Luc
• ###### 2. Re: Query based on direction
Currently Being Moderated
K

I quickly changed that function so it returns the direction (N, E, S, W) instead of the actual argument between two point geometries. So for polygons, I would take the centroid or a point_on_surface to evaluate the direction.

you could use it as follows.

select b.name, get_compas(SDO_GEOM.SDO_POINTONSURFACE(a.geometry, 0.05),
SDO_GEOM.SDO_POINTONSURFACE(b.geometry, 0.05))
from your_polygon_table a, your_polygon_table b
where a.id = YOUR_FILTER_ID

OR

select b.name, b.id
from your_polygon_table a, your_polygon_table b
where a.id = YOUR_FILTER_ID
and get_compas(SDO_GEOM.SDO_POINTONSURFACE(a.geometry, 0.05),
SDO_GEOM.SDO_POINTONSURFACE(b.geometry, 0.05)) = 'E'

Here is the raw function:

my_geometry1, my_geometry2 must be non-geodetic 2D points gemeotries
the function returns the direction (N, E, S, W) of the second point relative to the first

create or replace function GET_COMPAS
(my_geometry1 IN MDSYS.SDO_GEOMETRY, my_geometry2 IN MDSYS.SDO_GEOMETRY)
RETURN VARCHAR2 AS

my_XCoord2 REAL;
my_YCoord2 REAL;
my_XCoord1 REAL;
my_YCoord1 REAL;
pi CONSTANT REAL := 4 * ATAN(1);
my_DeltaX REAL;
my_DeltaY REAL;
my_Argument REAL;
my_TGOmega REAL;
my_Rotation REAL;
direction varchar2(1);

BEGIN

my_XCoord1 := my_geometry1.SDO_POINT.X;
my_YCoord1 := my_geometry1.SDO_POINT.Y;
my_XCoord2 := my_geometry2.SDO_POINT.X;
my_yCoord2 := my_geometry2.SDO_POINT.Y;

my_DeltaX := my_XCoord2 - my_XCoord1;
my_DeltaY := my_YCoord2 - my_YCoord1;

IF (my_DeltaY = 0) THEN
IF (my_DeltaX > 0) THEN
my_Argument := pi / 2;
ELSE
my_Argument := 3 * (pi / 2);
END IF;
ELSE
my_TGOmega := ABS(my_DELTAX) / ABS(my_DeltaY);
IF (my_DeltaX > 0) THEN
IF (my_DeltaY > 0) THEN
my_Argument := ATAN(my_TGOmega);
ELSE
my_Argument := pi - ATAN(my_TGOmega);
END IF;
ELSE
IF (my_DeltaY > 0) THEN
my_Argument := 2 * pi - ATAN(my_TGOmega);
ELSE
my_Argument := pi + ATAN(my_TGOmega);
END IF;
END IF;
END IF;

direction := case
when my_argument <= (pi / 4) then 'N'
when ((pi / 4) < my_argument AND my_argument < 3*(pi / 4)) then 'E'
when (3* (pi / 4) <= my_argument AND my_argument <= 5*(pi / 4)) then 'S'
when (5* (pi / 4) < my_argument AND my_argument < 7*(pi / 4)) then 'W'
when 7* (pi / 4) <= my_argument then 'N'
end;

return direction ;

END;

Edited by: lucvanlinden on Nov 16, 2008 10:49 AM

#### Legend

• Correct Answers - 10 points
• Helpful Answers - 5 points