# Thread: PL SQL Line intersect Help

1. Registered User
Join Date
Dec 2005
Posts
10

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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776

3. Registered 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?

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

4. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776

5. Registered 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 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. Registered 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.

7. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
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. Registered 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]

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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113