2 Replies Latest reply: Nov 16, 2008 3:51 AM by Luc Van Linden RSS

    Query based on direction

    466632
      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
          K

          Take a look at Re: Spatial Operators

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