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

Query based on direction

466632 Newbie
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
    Luc Van Linden Pro
    Currently Being Moderated
    K

    Take a look at Re: Spatial Operators

    Luc
  • 2. Re: Query based on direction
    Luc Van Linden Pro
    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