Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    10

    Unanswered: PL SQL Help

    I require some help with the line intersection algorithm in PL SQl

    I have worked with it on C programs, but not to sure how to create type and variables in PL SQL

    I have written the code for the algorithm, i require help to create the data type and testing the algorithm.

    Code:

    -- How do i declare variables which will be data type number -- x1, y1, x2, y2, x3, y3, x4, y4,
    --xc, yc;
    --a1, a2, b1, b2, c1, c2; /* Coefficients of line eqns. */
    -- lr1, r2, r3, r4; /* 'Sign' values */
    -- denom, num; /* Intermediate values */

    -- Bsign and Asign will have to be type boolean

    a1:=y2-y1;
    b1:=x1-x2;
    c1:=x2*y1-x1*y2;

    r3:= a1*x3+b1*y3+c1;
    r4:= a1*x4+b1*y4+c1;

    asign:=r3<0;
    bsign:=r4<0;

    if ( (r3!=0) and (r4!=0) and (asign = bsign) ) then
    return 'False';
    -- /* Check signs of r3 and r4. If both point 3 and point 4 lie on
    -- * same side of line 1, the line segments do not intersect.
    -- */


    else
    a2:= y4-y3;
    b2:= x3-x4;
    c2:= x4*y3-x3*y4;
    r1:=a2*x1+b2*y1+c2;
    r2:=a2*x2+b2*y2+c2;

    asign:=r1<0;
    bsign:=r2<0;

    if (r1 != 0) and (r2 != 0) and (asign =bsign) then
    return 'False';

    /* Check signs of r1 and r2. If both point 1 and point 2 lie
    * on same side of second line segment, the line segments do
    * not intersect.
    */


    else

    denom :=a1*b2-a2*b1;
    if(denom=0) then
    return 'NULL';
    --they are collinear
    else

    num:=b1*c2-b2*c1;
    xc:= num/denom;
    xc:=a2*c1-a1*c2;
    yc:= num/denom;

    return 'True';
    ---- This shows they intersect. HOW can i code to get output for
    --intersection points xc yx?

    end if;
    end if;
    end if;
    Last edited by tata123; 12-27-05 at 13:50.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2005
    Posts
    10

    PL SQL Help

    I have made a type to hold the variables, is it correct? :
    -- How do i declare variables which will be data type number -- x1, y1, x2, y2, x3, y3, x4, y4,--xc, yc;
    --a1, a2, b1, b2, c1, c2; /* Coefficients of line eqns. */
    -- lr1, r2, r3, r4; /* 'Sign' values */
    -- denom, num; /* Intermediate values */
    -- Bsign and Asign will have to be type boolean


    Create or Replace type Intersect_point object
    ( x1 number, x2 number, y1 number, y2 number, x3 number, y3 number, x4 number, y4 number, xc number, yc number,
    a1 number, a2 number, b1 number, b2 number, c1 number, c2 number, r1 number, r2 number, r3 number, r4 number,
    demon number, num number,
    Bsign boolean, Asign boolean)


    If i want to implement the algorithm as a member function of a new data type, does that mean i create a member function in the above Intersect_point. Can someone give me an example on how to move from here?

    Thanks
    Last edited by tata123; 12-27-05 at 13:51.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    visit http://asktom.oracle.com for coding example
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2005
    Posts
    10
    Create or Replace type Intersect_point as object
    ( x1 number, x2 number, y1 number, y2 number, x3 number, y3 number, x4 number, y4 number, xc number, yc number,
    a1 number, a2 number, b1 number, b2 number, c1 number, c2 number, r1 number, r2 number, r3 number, r4 number,
    demon number, num number,
    Bsign BOOLEAN, Asign BOOLEAN,
    Member function Algorithm_line return number)

    when executed, i get these errors:

    LINE/COL ERROR
    5/7 PLS-00530: Illegal type used for object type attribute: 'BOOLEAN' .
    5/22 PLS-00530: Illegal type used for object type attribute: 'BOOLEAN' .

    Is my boolean created in the wrong place?

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    An object type in Oracle is an SQL type with a PL/SQL (or Java) implementation, and the SQL language does not support Booleans. You could perhaps use the words 'TRUE' and 'FALSE' or integers 0 and 1, and convert to actual Booleans within your code.

    I'm guessing but some of those complicated parameters look like coordinate sets. Perhaps it would simplify things to define a type for these, rather than passing a jumble of numbers.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This *might* be a way to code this task; as you need to return more than one value (whether they intersect, are collinear or do ont intersect and, if intersect, intersection points), the procedure with OUT parametres is used instead of a function.

    I didn't get deep into the procedure logic; I hope you did it well. However, why do you calculate XC value twice, line after line? The first value is never used (check red part of the procedure)
    Code:
    CREATE OR REPLACE PROCEDURE prc_intersect
      (x1 IN NUMBER, y1 IN NUMBER,
       x2 IN NUMBER, y2 IN NUMBER,
       x3 IN NUMBER, y3 IN NUMBER,
       x4 IN NUMBER, y4 IN NUMBER,
       out_xc OUT NUMBER,
       out_yc OUT NUMBER,
       out_retval OUT NUMBER
      )
    IS
      xc NUMBER;   yc NUMBER;
      -- coefficients of line eqns.
      a1 NUMBER;   b1 NUMBER;   c1 NUMBER;
      a2 NUMBER;   b2 NUMBER;   c2 NUMBER;
      -- signn values
      r1 NUMBER;   r2 NUMBER;
      r3 NUMBER;   r4 NUMBER;
      -- intermediate values
      denom NUMBER;
      num   NUMBER;
      -- 
      asign BOOLEAN;
      bsign BOOLEAN;
      -- return value: -1: no intersection, 0: collinear, 1: intersection
      retval NUMBER;
      --
    BEGIN
      a1 := y2 - y1;
      b1 := x1 - x2;
      c1 := x2 * y1 - x1 * y2;
      
      r3 := a1 * x3 + b1 * y3 + c1;
      r4 := a1 * x4 + b1 * y4 + c1;
      
      asign := r3 < 0;
      bsign := r4 < 0;
      
      IF r3 <> 0 AND r4 <> 0 AND asign = bsign 
      THEN
         /* check signs of r3 and r4. If both point 3 and point 4 lie on
            the same side of line 1, the line segments do not intersect
         */
         retval := -1; 
      ELSE
         a2 := y4 - y3;
         b2 := x3 - x4;
         c2 := x4 * y3 - x3 *y4;
         r1 := a2 * x1 + b2 *y1 + c2;
         r2 := a2 * x2 + b2 *y2 + c2;
    	
         asign := r1 < 0;
         bsign := r2 < 0;
    	 
         IF r1 <> 0 AND r2 <> 0 AND asign = bsign
         THEN
            /* check signs of r1 and r2. If both point 1 and point 2 lie on
               same side of second line segment, the line segments do not intersect
            */
            retval := -1;
         ELSE
            denom := a1 * b2 - a2 * b1;
            IF denom = 0
            THEN
               -- they are collinear
               retval := 0;
            ELSE
               num := b1 * c2 - b2 * c1;
               /* why do you first calculate xc = num/denom, and in the next line as
               a2 * c1 - a1 * c2? What is the purpose of the first line*
               */
               xc := num / denom;           
               xc := a2 * c1 - a1 * c2;
               yc := num / denom;
    
               -- this shows they intersect		   
               retval := 1;
            END IF;
         END IF;
      END IF;
    
      out_retval := retval;
      out_xc := xc;
      out_yc := yc;
    END;
    /
    The procedure is invoked using such a code:
    Code:
    DECLARE
      xc NUMBER;
      yc NUMBER;
      retval NUMBER;
    BEGIN
      -- examples (comment lines you don't need):
      prc_intersect(1, 2, 4, 5, 12, 7, 4, 5, xc, yc, retval);     -- intersect
      -- prc_intersect(1, 2, 3, 4, 12, 13, 14, 15, xc, yc, retval);  -- collinear
      -- prc_intersect(1, 2, 4, 7, 14, 6, 14, 5, xc, yc, retval);    -- do not intersect
    	 
      IF retval = -1 THEN
         dbms_output.put_line('Lines do not intersect');
      ELSIF retval = 0 THEN
         dbms_output.put_line('Lines are collinear');
      ELSIF retval = 1 THEN
         dbms_output.put_line('Lines intersect at coordinates '
                               || 'xc = ' || xc 
    	                   || 'yc = ' || yc);
      END IF;
    END;
    /
    Do not forget to enable screen output issuing SET SERVEROUTPUT ON if this procedure is to be run at the SQL*Plus prompt. The result looks like this (when 'intersection' example is enabled):
    Code:
    SQL> /
    Lines intersect at coordinates xc = 90 yc = 4
    
    PL/SQL procedure successfully completed.
    
    SQL>
    I hope you'll be able to enhance this code to suit your needs. And, read carefully William's words; he is a true expert.

  8. #8
    Join Date
    Dec 2005
    Posts
    10
    [QUOTE=Littlefoot]This *might* be a way to code this task; as you need to return more than one value (whether they intersect, are collinear or do ont intersect and, if intersect, intersection points), the procedure with OUT parametres is used instead of a function.

    QUOTE]

    Thanks for your Reply. I will try and understand it.

    If i do not need to return all the values but just the Points of Intersection, than can i use Function?
    How would i go about it?

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Points of Intersection" IS plural; what's the difference in returning 2 or 200 values?

    You can use function, of course; return datatype isn't limited to a scalar, but can also contain a complex datatype as PL/SQL table, for example.

    Read a lot more about PL/SQL Subprograms, or check the Functions chapter in an online issue of PL/SQL Programming book.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •