1 PACKAGE BODY CSFW_SPATIAL_PUB as
2 /*$Header: csfwsplb.pls 120.0 2005/05/24 17:43:49 appldev noship $*/
3
4 /*
5 FUNCTION GET_AERIAL_DISTANCE( p_start_point MDSYS.SDO_GEOMETRY,
6 p_end_point MDSYS.SDO_GEOMETRY,
7 p_unit_of_measure varchar2 )
8 This function would find oput the distance between 2 points(MSDSYS.SDO_GEOMETRY)
9 In 8i, the distance computation assumes the coordinate system is NULL. So we would
10 convert the geometries to a projected coordinate system(Cartesian) and then do distance computation.
11 in that case this function would find out the longitude and latituide of the to and from points
12 and then calls
13 FUNCTION GET_AERIAL_DISTANCE( p_start_longitude number,
14 p_start_latitude,
15 p_end_longitude ,
16 p_end_latitude ,
17 p_unit_of_measure varchar2 )
18
19 */
20
21 FUNCTION GET_AERIAL_DISTANCE(p_start_point MDSYS.SDO_GEOMETRY,
22 p_end_point MDSYS.SDO_GEOMETRY,
23 p_unit_of_measure varchar2 )
24 RETURN number IS
25 x_distance NUMBER;
26 l_start_logitude number;
27 l_start_latitude number;
28 l_end_logitude number;
29 l_end_latitude number;
30 l_db_version number;
31
32 BEGIN
33 --initialize with 0;
34 x_distance := 0;
35
36 --MDSYS.GEOMETRY has longitude defined as SDO_POINT.x
37 -- and latitude as SDO_POINT.y.So lets populate the longitude and latitude
38 --for the start and end points
39 l_start_logitude := p_start_point.sdo_point.x ;
40 l_start_latitude := p_start_point.sdo_point.y ;
41 l_end_logitude := p_end_point.sdo_point.x ;
42 l_end_latitude := p_end_point.sdo_point.y ;
43
44
45
46 --Check if the points are same
47 IF ( (l_start_logitude = l_end_logitude) AND
48 (l_start_latitude = l_end_latitude) ) THEN
49 return 0;
50 END IF;
51
52
53 --now lets find out the distance by calling the other function
54 x_distance :=
55 GET_AERIAL_DISTANCE( l_start_logitude,l_start_latitude, l_end_logitude ,l_end_latitude , p_unit_of_measure );
56
57 --end if;
58
59
60 -- Now lets return the Distance
61 RETURN x_distance ;
62
63 END GET_AERIAL_DISTANCE;
64
65
66
67 /*
68 This function does the distance(big Circle) calculation depending on the longitude and latitude of the two points
69 now, the formula we use to calculate the distance is
70 Ths distance between two points P and A is
71 Distance = ACOS ( (sin a * sin p) + (cos a * cos p * cos (dL) ) )
72 where,
73 a = latitude of point A,
74 p = latitude of point P,
75 dL = is the absolute value of the difference in longitude between P and A
76 */
77
78 FUNCTION GET_AERIAL_DISTANCE( p_start_longitude number,
79 p_start_latitude number,
80 p_end_longitude number,
81 p_end_latitude number,
82 p_unit_of_measure varchar2 )
83 RETURN NUMBER IS
84 PI NUMBER := 3.14159265358979323846;
85 lat1 NUMBER := p_start_latitude * (PI/180.0);
86 lat2 NUMBER := p_end_latitude * (PI/180.0);
87 lon1 NUMBER := p_start_longitude * (PI/180.0);
88 lon2 NUMBER := p_end_longitude * (PI/180.0);
89 x_distance_in_meters number;
90
91 BEGIN
92
93 --Check if the points are same
94 IF ( (p_start_longitude = p_end_longitude) AND
95 (p_start_latitude = p_end_latitude) ) THEN
96 return 0;
97 END IF;
98
99
100 x_distance_in_meters :=
101 ACOS(SIN(lat1)*SIN(lat2) + COS(lat1)*COS(lat2)*COS(lon2 - lon1)) * 6371200.0;
102
103 IF ( p_unit_of_measure = 'KM' OR p_unit_of_measure = 'KILOMETER' ) THEN
104 RETURN x_distance_in_meters/1000;
105 END IF;
106
107
108
109
110 RETURN (x_distance_in_meters/1609.344); --Distance in Miles
111
112 END GET_AERIAL_DISTANCE;
113
114
115
116 FUNCTION CHECK_GEOMETRY_POINT( p_point MDSYS.SDO_GEOMETRY)
117 RETURN VARCHAR is
118
119 l_longitude NUMBER;
120 l_latitude NUMBER;
121 x_return_value VARCHAR2(1);
122
123
124 BEGIN
125
126 x_return_value := 'T';
127
128 IF (p_point.sdo_point IS null)THEN
129 RETURN 'F';
130 end if;
131
132 l_longitude := p_point.sdo_point.x;
133 l_latitude := p_point.sdo_point.y;
134
135 IF ((l_longitude = NULL) OR (l_latitude = NULL))
136 THEN
137 x_return_value := 'F';
138 END IF;
139
140 RETURN x_return_value;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 RETURN 'F';
145
146 END CHECK_GEOMETRY_POINT;
147
148
149
150
151 END CSFW_SPATIAL_PUB;
152
[Home] [Help]