[Home] [Help]
PACKAGE BODY: APPS.CSF_LF_PUB
Source
1 PACKAGE BODY CSF_LF_PUB AS
2 /*$Header: CSFPLFB.pls 120.6.12010000.10 2008/11/19 10:16:28 ppillai ship $*/
3
4 -- ----------------------------------------------------------------------------
5 -- register JSP
6 -- ----------------------------------------------------------------------------
7 FUNCTION resolveaddress (
8 p_country VARCHAR2,
9 p_state VARCHAR2,
10 p_province VARCHAR2,
11 p_county VARCHAR2,
12 p_city VARCHAR2,
13 p_postalcode VARCHAR2,
14 p_roadname VARCHAR2,
15 p_buildingnum VARCHAR2,
16 p_alternate VARCHAR2,
17 -- added for LF enhancement of forced accuracy
18 p_requiredAccuracy VARCHAR2
19 )
20 RETURN csf_lf_resultarray_int
21 AS
22 LANGUAGE JAVA
23 NAME 'oracle.apps.csf.lf.server.Address.resolveAddress(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String
24 , java.lang.String, java.lang.String, java.lang.String, java.lang.String,
25 java.lang.String) return oracle.sql.ARRAY';
26
27 -- ----------------------------------------------------------------------------
28 -- private types and constants
29 -- ----------------------------------------------------------------------------
30 TYPE POINT_COORD IS RECORD(LAT NUMBER, LONG NUMBER);
31
32 EARTH_RADIUS CONSTANT NUMBER := 6378137;
33 PI CONSTANT NUMBER := 3.14159265359;
34
35 -- ----------------------------------------------------------------------------
36 -- forward declaration of private procedures
37 -- ----------------------------------------------------------------------------
38 -- duplicate of oracle.apps.csf.lf.server.LFCommon.deg2rad()
39 FUNCTION DEG2RAD ( DEG NUMBER ) RETURN NUMBER;
40
41 FUNCTION RAD2DEG ( DEG NUMBER ) RETURN NUMBER;
42
43 -- duplicate of oracle.apps.csf.lf.server.LFCommon.getDistance()
44 FUNCTION GEODISTANCE ( POS1 IN POINT_COORD
45 , POS2 IN POINT_COORD ) RETURN NUMBER;
46
47 PROCEDURE NEW_COORD ( POS1 IN POINT_COORD
48 , DISTANCE IN NUMBER
49 , HEAD IN NUMBER
50 , POS2 OUT NOCOPY POINT_COORD );
51
52 FUNCTION HEADING ( POS1 IN POINT_COORD
53 , POS2 IN POINT_COORD ) RETURN NUMBER;
54
55 PROCEDURE GETCOORDONSEGMENT ( segmentLength NUMBER
56 , segmentGeometry MDSYS.SDO_ORDINATE_ARRAY
57 , percentAlongSeg NUMBER
58 , coord OUT NOCOPY POINT_COORD );
59
60 PROCEDURE GETSEGMENTLENGTH ( segmentGeometry IN MDSYS.SDO_ORDINATE_ARRAY
61 , segmentLength OUT NOCOPY NUMBER );
62
63 -- ----------------------------------------------------------------------------
64 -- public procedures
65 -- ----------------------------------------------------------------------------
66
67 -- Start of Comments
68 -- API Name : CSF_LF_ResolveAddress
69 -- Type : Public
70 -- Pre-req :
71 -- Function : Return a locus from an input address specification
72 -- Parameters :
73 -- p_api_version IN NUMBER, required
74 -- p_init_msg_list IN VARCHAR2 := NULL optional
75 -- x_return_status OUT VARCHAR2,
76 -- x_msg_count OUT NUMBER,
77 -- x_msg_data OUT VARCHAR2,
78 -- p_country IN VARCHAR2 required
79 -- p_state IN VARCHAR2 := NULL optional
80 -- p_county IN VARCHAR2 := NULL optional
81 -- p_province IN VARCHAR2 := NULL optional
82 -- p_city IN VARCHAR2 required
83 -- p_postalCode IN VARCHAR2 := NULL optional
84 -- p_roadname IN VARCHAR2 required
85 -- p_buildingnum IN VARCHAR := NULL optioanl
86 -- p_alternate IN VARCHAR2 := NULL optional
87 -- Version: 1.0
88 -- History: 19-04-2000 L Mbekeani Added lat long to locus
89 -- End Comments
90 PROCEDURE CSF_LF_ResolveAddress
91 ( p_api_version IN NUMBER
92 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
93 , x_return_status OUT NOCOPY VARCHAR2
94 , x_msg_count OUT NOCOPY NUMBER
95 , x_msg_data OUT NOCOPY VARCHAR2
96 , p_country IN VARCHAR2
97 , p_state IN VARCHAR2 default NULL
98 , p_county IN VARCHAR2 DEFAULT NULL
99 , p_province IN VARCHAR2 DEFAULT NULL
100 , p_city IN VARCHAR2
101 , p_postalCode IN VARCHAR2 default NULL
102 , p_roadname IN VARCHAR2
103 , p_buildingnum IN VARCHAR2 default NULL
104 , p_alternate IN VARCHAR2 default NULL
105 , x_resultsArray OUT NOCOPY CSF_LF_RESULTARRAY
106 )
107 IS
108 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LF_ResolveAddress';
109 l_api_version CONSTANT NUMBER := 1.0;
110 l_result_int CSF_LF_RESULTARRAY_INT;
111 l_retCode NUMBER;
112 l_numResults NUMBER;
113 J NUMBER;
114 l_srid number;
115 -- added for LF enhancement of forced accuracy
116 l_requiredAccuracy VARCHAR2(50);
117 TYPE REF_SRID IS REF CURSOR;
118 C_SRID REF_SRID;
119 sql_stmt_str VARCHAR2(2000);
120 l_data_set_name VARCHAR2(40);
121 /*
122 CURSOR C_SRID
123 IS
124 SELECT nvl(cscp.default_display_center.sdo_srid, 8307) srid
125 FROM csf_sdm_ctry_profiles cscp
126 WHERE cscp.default_display_center IS NOT NULL;
127 */
128 l_country VARCHAR2(100);
129 CURSOR ctry_hr_to_spatial IS
130 SELECT SPATIAL_COUNTRY_NAME
131 FROM CSF_SPATIAL_CTRY_MAPPINGS
132 WHERE HR_COUNTRY_NAME = p_country;
133 BEGIN
134
135 if ( l_api_version <> p_api_version ) then
136 raise csf_lf_version_error;
137 end if;
138
139 if ( p_init_msg_list = 'TRUE' ) then
140 x_msg_count := 0; /* FND_MSG_PUB.initialize; */
141 end if;
142
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 --
146 -- Validate parameters
147 --
148
149 if ( p_country = NULL ) then
150 raise CSF_LF_COUNTRY_NOT_SET_ERROR;
151 end if;
152
153 if ( p_city = NULL ) then
154 raise CSF_LF_CITY_NOT_SET_ERROR;
155 end if;
156
157 if ( (p_roadname = NULL) or (p_roadname = '') ) then
158 raise CSF_LF_ROAD_NOT_SET_ERROR;
159 end if;
160
161 x_msg_count := 0;
162 x_msg_data := 'Success';
163
164 --l_result_int := RESOLVEADDRESS(p_country, p_state, p_city, p_postalCode, p_roadName, p_buildingnum, p_alternate);
165 --l_result_int := RESOLVEADDRESS(p_country, p_state, p_city, p_postalCode, p_roadName, '_', p_alternate);
166
167 -- added for LF enhancement of forced accuracy
168 l_requiredAccuracy := NVL(fnd_profile.value('CSF_LOC_ACC_LEVELS'),''+0);
169
170 /** Bug 7570463 **/
171 open ctry_hr_to_spatial;
172 fetch ctry_hr_to_spatial into l_country;
173 close ctry_hr_to_spatial;
174
175 IF (l_country is null) THEN
176 l_country := p_country;
177 END IF;
178
179 IF ((p_buildingnum = NULL) OR (p_buildingnum = ''))
180 THEN
181 l_result_int :=
182 resolveaddress (l_country,
183 p_state,
184 p_province,
185 p_county,
186 p_city,
187 p_postalcode,
188 p_roadname,
189 '_',
190 p_alternate,
191 -- added for LF enhancement of forced accuracy
192 l_requiredAccuracy
193 );
194 ELSE
195 l_result_int :=
196 resolveaddress (l_country,
197 p_state,
198 p_province,
199 p_county,
200 p_city,
201 p_postalcode,
202 p_roadname,
203 p_buildingnum,
204 p_alternate,
205 -- added for LF enhancement of forced accuracy
206 l_requiredAccuracy
207 );
208 END IF;
209
210 l_retCode := l_result_int(1).record_type;
211
212 --
213 -- TODO: Perform Geometry to Locus conversion then load the result array back
214 -- If the first record type is greater than 0 then we havw a problem */
215 --
216
217 if ( l_retCode = MISSING_COUNTRY_NAME ) then
218
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 x_msg_count := 1;
221 x_msg_data := l_result_int(1).msg_data;
222
223 raise CSF_LF_COUNTRY_NOT_SET_ERROR;
224
225 elsif ( l_retCode = MISSING_STATE_NAME ) then
226
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 x_msg_count := 1;
229 x_msg_data := l_result_int(1).msg_data;
230
231 raise CSF_LF_STATE_NOT_SET_ERROR;
232
233 elsif ( l_retCode = MISSING_CITY_NAME ) then
234
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 x_msg_count := 1;
237 x_msg_data := l_result_int(1).msg_data;
238
239 raise CSF_LF_CITY_NOT_SET_ERROR;
240
241 elsif ( l_retCode = MISSING_ROAD_NAME ) then
242
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 x_msg_count := 1;
245 x_msg_data := l_result_int(1).msg_data;
246
247 raise CSF_LF_ROAD_NOT_SET_ERROR;
248
249 -- added for LF enhancement of forced accuracy
250 elsif ( l_retCode = REQUIRED_ACCURACY_EXCEPTION ) then
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 x_msg_count := 1;
253 x_msg_data := l_result_int(1).msg_data;
254 RAISE CSF_LF_REQ_ACCURACY_NOT_GOT;
255
256 elsif ( l_retCode > 0 ) then
257
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 x_msg_count := 1;
260 x_msg_data := l_result_int(1).msg_data;
261
262 else
263
264 --
265 -- Load the result items
266 --
267 l_numResults := l_result_int.count();
268 x_resultsArray := CSF_LF_RESULTARRAY();
269
270 l_data_set_name := fnd_profile.value('CSF_SPATIAL_DATASET_NAME');
271 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL ) THEN
272 l_data_set_name := '';
273 END IF;
274
275 sql_stmt_str := 'SELECT nvl(cscp.default_display_center.sdo_srid, 8307) srid
276 FROM csf_sdm_ctry_profiles' || l_data_set_name || ' cscp
277 WHERE cscp.default_display_center IS NOT NULL';
278 --OPEN C_SRID;
279 OPEN C_SRID FOR sql_stmt_str;
280 FETCH C_SRID INTO l_srid;
281 CLOSE C_SRID;
282
283 for J in 1..l_numresults loop
284
285 x_resultsArray.extend(1);
286
287 x_resultsArray(J).locus := mdsys.sdo_geometry(2001,l_srid,
288 mdsys.sdo_point_type( l_result_int(J).lon
289 , l_result_int(J).lat
290 , 0),
291 mdsys.sdo_elem_info_array(1,1,1,3,0,5),
292 mdsys.sdo_ordinate_array( l_result_int(J).lon
293 , l_result_int(J).lat
294 -- added for LF enhancement of forced accuracy
295 , l_result_int(J).accuracy_fact
296 , -9999
297 , l_result_int(J).segment_id
298 , l_result_int(J).percentage
299 , l_result_int(J).segment_side
300 )
301 );
302 x_resultsArray(J).record_type := l_result_int(J).record_type;
303 x_resultsArray(J).accuracy_factor := l_result_int(J).accuracy_fact;
304 x_resultsArray(J).country := l_result_int(J).country;
305 x_resultsArray(J).state := l_result_int(J).state;
306 x_resultsarray (j).county := l_result_int (j).county;
307 x_resultsarray (j).province := l_result_int (j).province;
308 x_resultsArray(J).city := l_result_int(J).city;
309 x_resultsArray(J).postalcode := l_result_int(J).postalcode;
310 x_resultsArray(J).road := l_result_int(J).road;
311 x_resultsArray(J).buildingnum := l_result_int(J).buildingnum;
312 x_resultsArray(J).alternate := l_result_int(J).alternate;
313
314 end loop;
315
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317 x_msg_count := l_numResults;
318 x_msg_data := 'Success';
319
320 end if;
321
322 EXCEPTION
323
324 when CSF_LF_VERSION_ERROR then
325 x_return_status := FND_API.G_RET_STS_ERROR;
326 x_msg_count := 1;
327 x_msg_data := 'Incompatibale version';
328
329 when CSF_LF_REQ_ACCURACY_NOT_GOT then
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 x_msg_count := 1;
332 x_msg_data := l_result_int(1).msg_data;
333
334 END CSF_LF_ResolveAddress;
335
336 -- Start of Comments
337 -- API Name : CSF_LocustoGeometry
338 -- Type : Public
339 -- Pre-req :
340 -- Function : Converts a Locust to a geometry
341 -- Parameters :
342 -- IN
343 -- p_api_version IN NUMBER required :=
344 -- p_init_msg_list IN VARCHAR2 = NULL optional :=
345 -- x_return_status OUT VARCHAR2 :=
346 -- x_msg_count OUT NUMBER :=
347 -- x_msg_data OUT VARCHAR2 :=
348 -- p_locus MDSYS.SDO_GEOMETRY required :=
349 -- x_point OUT MDSYS.SDO_GEOMETRY
350 -- Version:
351 -- End Comments
352 PROCEDURE CSF_LocustoGeometry
353 ( p_api_version IN NUMBER
354 , p_init_msg_list IN VARCHAR2 default fnd_api.g_false
355 , x_return_status OUT NOCOPY VARCHAR2
356 , x_msg_count OUT NOCOPY NUMBER
357 , x_msg_data OUT NOCOPY VARCHAR2
358 , p_locus IN MDSYS.SDO_GEOMETRY
359 , x_point OUT NOCOPY MDSYS.SDO_GEOMETRY
360 )
361 IS
362 x_geom mdsys.sdo_geometry;
363 segmentGeometry mdsys.sdo_geometry;
364 segmentLength number;
365 coord POINT_COORD;
366 x_segid number;
367 x_offset number;
368 x_direction number;
369 l_msg_count number;
370 l_msg_data varchar2(1000);
371 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LocustoGeometry';
372 l_api_version number := 1.0;
373 TYPE REF_SEGID IS REF CURSOR;
374 C_SEGID REF_SEGID;
375 sql_stmt_str VARCHAR2(2000);
376 l_data_set_name VARCHAR2(40);
377
378 BEGIN
379
380 if ( l_api_version <> p_api_version ) then
381 raise CSF_LF_VERSION_ERROR;
382 end if;
383
384 if ( p_init_msg_list = 'TRUE' ) then
385 null; /* FND_MSG_PUB.initialize; */
386 end if;
387
388 x_return_status := FND_API.G_RET_STS_SUCCESS;
389
390 CSF_LOCUS_PUB.read_locus (
391 p_api_version => l_api_version
392 , x_return_status => x_return_status
393 , x_msg_count => l_msg_count
394 , x_msg_data => l_msg_data
395 , p_locus => p_locus
396 , x_geom => x_geom
400 );
397 , x_segid => x_segid
398 , x_offset => x_offset
399 , x_direction => x_direction
401
402 if ( x_return_status <> CSF_LOCUS_PUB.G_RET_LOCUS_SUCCESS ) then
403 return;
404 else
405 /* select roadsegment_geometry into segmentGeometry
406 from csf_lf_roadsegments
407 where roadSegment_id = x_segid; */
408
409 l_data_set_name := fnd_profile.value('CSF_SPATIAL_DATASET_NAME');
410 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL ) THEN
411 l_data_set_name := '';
412 END IF;
413
414 sql_stmt_str := 'SELECT roadsegment_geometry into segmentGeometry
415 FROM csf_lf_roadsegments' || l_data_set_name||'
416 WHERE roadSegment_id = '|| x_segid;
417 OPEN C_SEGID FOR sql_stmt_str;
418 FETCH C_SEGID INTO segmentGeometry;
419 CLOSE C_SEGID;
420
421 getSegmentLength(segmentGeometry.sdo_ordinates,segmentLength);
422 getCoordonSegment(segmentLength, segmentGeometry.sdo_ordinates,x_offset,coord);
423
424 x_point := mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(coord.long,coord.lat,0),null,null);
425
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427 end if;
428
429 -- Get the segId
430 -- Fetch The segment geometry
431 -- call GetLocusPosition
432 -- call create PointGeo.
433
434 EXCEPTION
435
436 when CSF_LF_VERSION_ERROR then
437 x_return_status := FND_API.G_RET_STS_ERROR;
438
439 when NO_DATA_FOUND then
440 x_return_status := FND_API.G_RET_STS_ERROR;
441
442 END CSF_LocustoGeometry;
443
444 -- Start of Comments
445 -- API Name : CSF_GeometrytoLocus
446 -- Type : Public
447 -- Pre-req :
448 -- Functiom : Converts a geomrty to a Locus
449 -- Parameters :
450 -- IN
451 -- p_api_version IN NUMBER required
452 -- p_init_msg_list IN VARCHAR2 : = NULL optional
453 -- x_return_status OUT VARCHAR2 :=
454 -- x_msg_count OUT NUMBER :=
455 -- x_msg_data OUT VARCHAR2 :=
456 -- p_point MDSYS.SDO_GEOMETRY := required
457 -- x_locus MDSYS.SDO_GEOMETRY
458 -- Version:
459 -- End Comments
460 PROCEDURE CSF_GeometrytoLocus
461 ( p_api_version IN NUMBER
462 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
463 , x_return_status OUT NOCOPY VARCHAR2
464 , x_msg_count OUT NOCOPY NUMBER
465 , x_msg_data OUT NOCOPY VARCHAR2
466 , p_point IN MDSYS.SDO_GEOMETRY
467 , x_locus OUT NOCOPY MDSYS.SDO_GEOMETRY
468 )
469 IS
470
471 l_api_name CONSTANT VARCHAR2(30) := 'CSF_GeometrytoLocus';
472 l_api_version NUMBER := 1.0;
473
474 BEGIN
475
476 if ( l_api_version <> p_api_version ) then
477 raise CSF_LF_VERSION_ERROR;
478 end if;
479
480 if ( p_init_msg_list = 'TRUE' ) then
481 null; /* FND_MSG_PUB.initialize; */
482 end if;
483
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485
486 EXCEPTION
487
488 when CSF_LF_VERSION_ERROR then
489 x_return_status := FND_API.G_RET_STS_ERROR;
490
491 END CSF_GeometrytoLocus;
492
493 -- Start of Comments
494 -- API Name : CSF_LocustoTimeZone
495 -- Type : Public
496 -- Pre-req :
497 -- Functiom : Converts a Locus to a time zone
498 -- Parameters :
499 -- IN
500 -- p_api_version IN NUMBER required
501 -- p_init_msg_list IN VARCHAR2 := NULL optional
502 -- x_return_status OUT VARCHAR2 :=
503 -- x_msg_count OUT NUMBER :=
504 -- x_msg_data OUT VARCHAR2 :=
505 -- p_point MDSYS.SDO_GEOMETRY := required
506 -- x_timezone OUT NUMBER
507 -- Version:
508 -- End Comments
509
510 PROCEDURE CSF_LocustoTimeZone
511 ( p_api_version IN NUMBER
512 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
513 , x_return_status OUT NOCOPY VARCHAR2
514 , x_msg_count OUT NOCOPY NUMBER
515 , x_msg_data OUT NOCOPY VARCHAR2
516 , p_locus IN MDSYS.SDO_GEOMETRY
517 , x_timezone OUT NOCOPY NUMBER
518 )
519 IS
520
521 l_api_name CONSTANT VARCHAR2(30) := 'CSF_LocustoTimeZone';
522 l_api_version CONSTANT NUMBER := 1.0;
523 l_result varchar2(1000);
524 TYPE REF_TIMEZONE IS REF CURSOR;
525 C_TIMEZONE REF_TIMEZONE;
526 sql_stmt_str VARCHAR2(2000);
527 l_data_set_name VARCHAR2(40);
528 BEGIN
529
530 if ( l_api_version <> p_api_version ) THEN
531 raise CSF_LF_VERSION_ERROR;
532 end if;
533
534 if ( p_init_msg_list = 'TRUE' ) THEN
535 x_msg_count := 0; /* FND_MSG_PUB.initialize; */
536 end if;
537
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539
540 CSF_LOCUS_PUB.VERIFY_LOCUS (
541 p_api_version => l_api_version
542 , x_return_status => x_return_status
543 , x_msg_count => x_msg_count
544 , x_msg_data => x_msg_data
545 , p_locus => p_locus
546 , x_result => l_result
547 );
548
549 if ( l_result = 'TRUE' ) then
550 /* select time_zone into x_timezone
551 from csf_lf_roadsegments
552 where roadsegment_id = p_locus.sdo_ordinates(6); */
553
557 END IF;
554 l_data_set_name := fnd_profile.value('CSF_SPATIAL_DATASET_NAME');
555 IF (l_data_set_name = 'NONE' OR l_data_set_name IS NULL ) THEN
556 l_data_set_name := '';
558 sql_stmt_str := 'SELECT time_zone into x_timezone
559 FROM csf_lf_roadsegments'|| l_data_set_name||'
560 WHERE roadsegment_id = '|| p_locus.sdo_ordinates(6);
561
562 OPEN C_TIMEZONE FOR sql_stmt_str;
563 FETCH C_TIMEZONE INTO x_timezone;
564 CLOSE C_TIMEZONE;
565
566 x_return_status := FND_API.G_RET_STS_SUCCESS;
567 else
568 x_return_status := FND_API.G_RET_STS_ERROR;
569 end if;
570
571 EXCEPTION
572
573 when NO_DATA_FOUND then
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 x_msg_data := 'INVALID ROADSEGMENT_ID';
576
577 when OTHERS then
578 x_return_status := FND_API.G_RET_STS_ERROR;
579 x_msg_data := 'UNKNOWN ERROR';
580 END;
581
582 -- ----------------------------------------------------------------------------
583 -- private procedures
584 -- ----------------------------------------------------------------------------
585
586 PROCEDURE getSegmentLength
587 ( segmentGeometry IN MDSYS.SDO_ORDINATE_ARRAY
588 , segmentLength OUT NOCOPY NUMBER)
589 IS
590 prevPos POINT_COORD;
591 currPos POINT_COORD;
592 distanceFromLastShapePoint NUMBER:= 0;
593 numCoordinates NUMBER := 0;
594
595 BEGIN
596 segmentLength := 0;
597 /* Starting from the first shape point which we know is the reference node....*/
598 prevPos.Long := segmentGeometry(1);
599 prevPos.lat := segmentGeometry(2);
600 numCoordinates := segmentGeometry.count / 2;
601
602 /* ..take a walk along the shape points until we have walked a distance greater
603 than segmentLength * percentAlongSeg */
604 FOR J IN 1..numCoordinates - 1
605 LOOP
606 currPos.LONG := segmentGeometry(J*2 + 1);
607 currPos.LAT := segmentGeometry(J*2 + 2);
608 segmentLength := segmentLength + GEODISTANCE(prevPos, currPos);
609 prevPos := currPos;
610 END LOOP;
611 END getSegmentLength;
612
613 PROCEDURE getCoordOnSegment
614 ( segmentLength NUMBER
615 , segmentGeometry MDSYS.SDO_ORDINATE_ARRAY
616 , percentAlongSeg NUMBER
617 , coord OUT NOCOPY POINT_COORD )
618 IS
619 prevPos POINT_COORD;
620 currPos POINT_COORD;
621 lengthSoFar NUMBER := 0;
622 prevLengthSoFar NUMBER := 0;
623 vheading NUMBER;
624 distanceFromLastShapePoint NUMBER:= 0;
625 numCoordinates NUMBER := 0;
626
627 BEGIN
628 /* Starting from the fist shape point which we know is the reference node....*/
629 prevPos.long := segmentGeometry(1);
630 prevPos.lat := segmentGeometry(2);
631 numCoordinates := segmentGeometry.count / 2;
632
633 /* ..take a walk along the shape points until we have walked a distance greater than segmentLength * percentAlongSeg */
634 FOR J IN 1..numCoordinates - 1
635 LOOP
636 currPos.LONG := segmentGeometry(J*2 + 1);
637 currPos.LAT := segmentGeometry(J*2 + 2);
638 lengthSoFar := lengthSoFar + GEODISTANCE(prevPos, currPos);
639 IF lengthSoFar >= segmentLength * percentAlongSeg
640 THEN
641 /* distanceFromLastShapePoint is the distance from the last shape point to the position */
642 distanceFromLastShapePoint := segmentLength * percentAlongSeg - prevLengthSoFar;
643 vheading := HEADING(prevPos, currPos);
644
645 /* Now use the heading, previous shape point and distance from last shape point to determine coord point */
646 NEW_COORD( prevPos, distanceFromLastShapePoint, vheading, coord);
647 EXIT;
648 ELSE
649 prevPos := currPos;
650 prevLengthSoFar := lengthSoFar;
651 END IF;
652 END LOOP;
653 END getCoordOnSegment;
654
655
656 -- DETERMINE NEW COORD FROM START COORD, DISTANCE BETWEEN POINTS AND HEADING (RADIANS);
657 PROCEDURE NEW_COORD( POS1 IN POINT_COORD, DISTANCE IN NUMBER, HEAD NUMBER, POS2 OUT NOCOPY POINT_COORD) IS
658 BEGIN
659 POS2.LAT := RAD2DEG(((DISTANCE * SIN(HEAD)) / EARTH_RADIUS) + DEG2RAD(POS1.LAT));
660 POS2.LONG := RAD2DEG(((DISTANCE * COS(HEAD)) / EARTH_RADIUS) + DEG2RAD(POS1.LONG));
661 END NEW_COORD;
662 -- HEADING IN RADIANS WILL ALWAYS BE POSITIVE AND IS DEFINES AS RADIANS FROM NORTH;
663
664 -- COMPUTES HEADING ANGLE BETWEEN TWO POINTS IN RADIANS
665 FUNCTION HEADING(POS1 IN POINT_COORD,POS2 IN POINT_COORD) RETURN NUMBER IS
666 NORTH_DIST NUMBER;
667 EAST_DIST NUMBER;
668 HEAD NUMBER;
669 BEGIN
670 NORTH_DIST := (DEG2RAD(POS2.LAT) - DEG2RAD(POS1.LAT)) * EARTH_RADIUS;
671 EAST_DIST := (DEG2RAD(POS2.LONG) - DEG2RAD(POS1.LONG)) * COS(DEG2RAD(POS2.LAT)) * EARTH_RADIUS;
672 IF (NORTH_DIST = 0) AND (EAST_DIST = 0) THEN
673 HEAD := 0;
674 ELSE
675 HEAD := ATAN2(EAST_DIST,NORTH_DIST);
676 IF HEAD < 0 THEN
677 HEAD := HEAD + 2* PI;
678 END IF;
679 END IF;
680 RETURN HEAD;
681 END HEADING;
682
683 -- COMPUTES DISTANCE BETWEEN TWO COORD.
684 FUNCTION GEODISTANCE(POS1 IN POINT_COORD,POS2 IN POINT_COORD) RETURN NUMBER IS
685 NORTH_DIST NUMBER;
686 EAST_DIST NUMBER;
687 DISTANCE NUMBER;
688 BEGIN
689 NORTH_DIST := (DEG2RAD(POS2.LAT) - DEG2RAD(POS1.LAT)) * EARTH_RADIUS;
690 EAST_DIST := (DEG2RAD(POS2.LONG) - DEG2RAD(POS1.LONG)) * COS(DEG2RAD(POS2.LAT)) * EARTH_RADIUS;
691 DISTANCE := SQRT((NORTH_DIST * NORTH_DIST) + (EAST_DIST *EAST_DIST));
692 RETURN DISTANCE;
693 END GEODISTANCE;
694
695 -- CONVERTS DEGREES UNITS TO RADIANS
696 FUNCTION DEG2RAD(DEG NUMBER) RETURN NUMBER IS
697 RADIANS NUMBER;
698 BEGIN
699 RADIANS := DEG * PI/180.0;
700 RETURN RADIANS;
701 END DEG2RAD;
702
703 -- CONVERTS RADIANS TO DEGREES
704 FUNCTION RAD2DEG(DEG NUMBER) RETURN NUMBER IS
705 DEGREES NUMBER;
706 BEGIN
707 DEGREES := ( 180.0/PI) * DEG;
708 RETURN DEGREES;
709 END RAD2DEG;
710
711 END CSF_LF_PUB;