Results 1 to 9 of 9
Thread: PL SQL Line intersect Help

122505, 07:22 #1Registered User
 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:=y2y1;
b1:=x1x2;
c1:=x2*y1x1*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:= y4y3;
b2:= x3x4;
c2:= x4*y3x3*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*b2a2*b1;
if(denom=0) then
return 'NULL';
they are collinear
else
num:=b1*c2b2*c1;
xc:= num/denom;
xc:=a2*c1a1*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; 122705 at 12:50.

122505, 10:06 #2Registered User
 Join Date
 Aug 2003
 Location
 Where the Surf Meets the Turf @Del Mar, CA
 Posts
 7,776
Provided Answers: 1You 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.

122505, 19:35 #3Registered User
 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?
ThanksLast edited by tata123; 122705 at 12:51.

122505, 21:54 #4Registered User
 Join Date
 Aug 2003
 Location
 Where the Surf Meets the Turf @Del Mar, CA
 Posts
 7,776
Provided Answers: 1visit 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.

122605, 05:49 #5Registered User
 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 PLS00530: Illegal type used for object type attribute: 'BOOLEAN' .
5/22 PLS00530: Illegal type used for object type attribute: 'BOOLEAN' .
Is my boolean created in the wrong place?

122605, 18:01 #6Registered User
 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.

122705, 02:18 #7Lost Boy
 Join Date
 Jan 2004
 Location
 Croatia, Europe
 Posts
 4,090
Provided Answers: 4This *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; /
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; /
Code:SQL> / Lines intersect at coordinates xc = 90 yc = 4 PL/SQL procedure successfully completed. SQL>

122705, 12:20 #8Registered User
 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?

122705, 13:43 #9Lost Boy
 Join Date
 Jan 2004
 Location
 Croatia, Europe
 Posts
 4,090
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.