[Home] [Help]
247:
248: --Open cursor to check the nearest geometry with in specified range from poi table
249: l_sql_stmt := 'SELECT 1 FROM csf_lf_pois' || l_dyn_tbl_name || ' r
250: WHERE SDO_WITHIN_DISTANCE(r.poi_point, SDO_GEOMETRY(2004,8307,null,SDO_ELEM_INFO_ARRAY(1,1,1,3,0,5),
251: SDO_ORDINATE_ARRAY(:1, :2)),:3) = ''TRUE'' and rownum = 1';
252:
253: OPEN cursor_poi_dist_chk for l_sql_stmt USING p_longitude,p_latitude,l_acc_dist;
254: LOOP
255: FETCH cursor_poi_dist_chk INTO l_within_dist;
261: IF(l_within_dist <> -1) THEN
262: l_sql_stmt := 'SELECT /*+ INDEX(r CSF_LF_POIS_N2) */ ROADSEGMENT_ID, SDO_NN_DISTANCE(1) dist
263: FROM csf_lf_pois' || l_dyn_tbl_name || ' r
264: WHERE SDO_NN(r.poi_point, SDO_GEOMETRY(2004,8307,null,SDO_ELEM_INFO_ARRAY(1,1,1,3,0,5),
265: SDO_ORDINATE_ARRAY(:1, :2)), ''sdo_num_res=1'', 1) = ''TRUE'' ORDER BY dist';
266:
267: OPEN cursor_poi for l_sql_stmt USING p_longitude,p_latitude;
268: LOOP
269: FETCH cursor_poi INTO l_roadsegmentid, l_dist; -- fetch data into local variables
279: if(l_dist = -1) THEN
280: l_within_dist := -1;
281: l_sql_stmt := 'SELECT 1 FROM csf_lf_roadsegments' || l_dyn_tbl_name || ' r
282: WHERE SDO_WITHIN_DISTANCE(r.ROADSEGMENT_GEOMETRY, SDO_GEOMETRY(2002,8307,null,SDO_ELEM_INFO_ARRAY(1,2,1),
283: SDO_ORDINATE_ARRAY(:1, :2)),:3) = ''TRUE'' and rownum = 1';
284:
285: --Open cursor to check the nearest geometry with in specified range from road segmen table
286: OPEN cursor_rdseg_dist_chk for l_sql_stmt USING p_longitude,p_latitude,l_acc_dist;
287: LOOP
294: IF(l_within_dist <> -1) THEN
295: l_sql_stmt := 'SELECT /*+ INDEX(r CSF_LF_RDSEGS_N2) */ ROADSEGMENT_ID, SDO_NN_DISTANCE(1) dist
296: FROM csf_lf_roadsegments' || l_dyn_tbl_name || ' r
297: WHERE SDO_NN(r.ROADSEGMENT_GEOMETRY, SDO_GEOMETRY(2002,8307,null,SDO_ELEM_INFO_ARRAY(1,2,1),
298: SDO_ORDINATE_ARRAY(:1, :2, :3, :4)),
299: ''sdo_num_res=1'', 1) = ''TRUE'' ORDER BY dist';
300:
301: OPEN cursor_rdseg for l_sql_stmt USING p_longitude,p_latitude,p_longitude,p_latitude;
302: LOOP