Provide the implementation of the following stored procedures and function. include both the PL/SQL code and...

   

 

Transcribed Image Text:

Provide the implementation of the following stored procedures and function. include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality. a. Create a function to calculate the distance between 2 points using Euclidean distance formula. dist=√√(Latitude - Latitude₂)² + (Longitude - Longitude₂)² * 111 Km b. A user is located at coordinate (-37.7206,145.0500). Show the distance from the user's current location to all other point objects. Show the result from the nearest objects. c. Using the trajectorypoint table, identify the period of time from the first point in each trajectory using the table below. Create a PL/SQL script to show the TrajID and period of time. Time 4am-12pm 12pm - 5pm 5pm - 10pm 10pm - 4am TRJ0001 10 Morning TRJ0002 : 13: Afternoon TRJ0003 : 10: Morning TRJ0004 : 14: Afternoon TRJ0005 : 13: Afternoon TRJ0006 : 16 : Afternoon TRJ0007 : 11: Morning TRJ0008 : 15: Afternoon TRJ0009 : 13 : Afternoon TRJ0010 : 08: Morning PL/SQL procedure successfully completed. Period of Time Morning Afternoon Evening Night Using the distance function, create a procedure named total_dist that accept objectid as the input parameter. This procedure will calculate the road length for a road type object. Hint: Use coorlist table. The length must be calculated from two consecutive points. SQL> exec total_dist ('OB0021'); Seq 1 (-37.700009, 145.0582327) Length = 0 km Seq 2 (-37.7022254,145.0574304) Seq 3 (-37.7034948, 145.0562599) Seq 4 (-37.7077456,145.0519525) Seq 5 (-37.7117952,145.047455) Length Seq 6 (-37.7128434,145.0459294) Length Length Length = : .2616 km = .4533 km Length = 1.125 km 1.7968 km 2.0023 km = 2.0664 km = 2.5718 km Seq 7 (-37.7131704, 145.0454536) Length Seq 8 (-37.7169925, 145.0429797) Length Seq 9 (-37.7177539, 145.0423095) Length Seq 10 (-37.7226433,145.0302035) Length = 4.1336 km (-37.7245782,145.0270209) 2.6844 km Seq 11 Length 4.547 km Length = 4.9438 km Seq 12 (-37.7254311,145.0235489) Total Length: 4.9438 km PL/SQL procedure successfully completed. = = = Provide the implementation of the following stored procedures and function. include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality. a. Create a function to calculate the distance between 2 points using Euclidean distance formula. dist=√√(Latitude - Latitude₂)² + (Longitude - Longitude₂)² * 111 Km b. A user is located at coordinate (-37.7206,145.0500). Show the distance from the user's current location to all other point objects. Show the result from the nearest objects. c. Using the trajectorypoint table, identify the period of time from the first point in each trajectory using the table below. Create a PL/SQL script to show the TrajID and period of time. Time 4am-12pm 12pm - 5pm 5pm - 10pm 10pm - 4am TRJ0001 10 Morning TRJ0002 : 13: Afternoon TRJ0003 : 10: Morning TRJ0004 : 14: Afternoon TRJ0005 : 13: Afternoon TRJ0006 : 16 : Afternoon TRJ0007 : 11: Morning TRJ0008 : 15: Afternoon TRJ0009 : 13 : Afternoon TRJ0010 : 08: Morning PL/SQL procedure successfully completed. Period of Time Morning Afternoon Evening Night Using the distance function, create a procedure named total_dist that accept objectid as the input parameter. This procedure will calculate the road length for a road type object. Hint: Use coorlist table. The length must be calculated from two consecutive points. SQL> exec total_dist ('OB0021'); Seq 1 (-37.700009, 145.0582327) Length = 0 km Seq 2 (-37.7022254,145.0574304) Seq 3 (-37.7034948, 145.0562599) Seq 4 (-37.7077456,145.0519525) Seq 5 (-37.7117952,145.047455) Length Seq 6 (-37.7128434,145.0459294) Length Length Length = : .2616 km = .4533 km Length = 1.125 km 1.7968 km 2.0023 km = 2.0664 km = 2.5718 km Seq 7 (-37.7131704, 145.0454536) Length Seq 8 (-37.7169925, 145.0429797) Length Seq 9 (-37.7177539, 145.0423095) Length Seq 10 (-37.7226433,145.0302035) Length = 4.1336 km (-37.7245782,145.0270209) 2.6844 km Seq 11 Length 4.547 km Length = 4.9438 km Seq 12 (-37.7254311,145.0235489) Total Length: 4.9438 km PL/SQL procedure successfully completed. = = =