-1

i have a set of longitude and latitude that saved in database, now i want to calculate Travelled distance in a period of time .i want write this query in oracle 11g.

my table fields are 

GeoData Table fields:

Latitude : FLOAT
Longitude: FLOAT
insert_time: TIMESTAMP(6)

i want to pass from date and to date to this query and select calculated distance .

thanks in advance

user1738224
  • 23
  • 1
  • 9

2 Answers2

2

Try it. I think it will help you.

"SELECT ((ACOS(SIN($latitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS($latitude * PI() / 180) * COS(Latitude * PI() / 180) * COS(($longitude - Longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance`  FROM `table_name` "

where $latitude=> current latitude and $longitude=> current longitude

Atanu Mondal
  • 1,714
  • 1
  • 24
  • 30
  • It will calculate the list of distance between the current location and the stored database value. The distance will be calculated between two points only. – Atanu Mondal Jul 14 '14 at 11:20
  • what do you mean current location :i expect a query like this :select distance from myTable where insert_time between(date1,date2) – user1738224 Jul 14 '14 at 11:27
  • Current location means current latitude and current longitude. – Atanu Mondal Jul 14 '14 at 11:36
  • i ran this query :SELECT ((ACOS(SIN(latitude * 3.14 / 180) * SIN(latitude * 3.14 / 180) + COS(latitude * 3.14 / 180) * COS(latitude * 3.14 / 180) * COS((longitude - longitude) * 3.14 / 180)) * 180 / 3.14) * 60 * 1.1515) AS distance FROM avl_data where car_id=301 and time> to_date ('14-JUN-2014', 'DD-MON-YYYY'); – user1738224 Jul 14 '14 at 11:53
  • but it returns this exception :ORA-01428: argument '1.00000000000000000000000000000000000002' is out of range 01428. 00000 - "argument '%s' is out of range" – user1738224 Jul 14 '14 at 11:54
  • You have written all latitude and longitude as table's field name. But I have mention $latitude,Latitude and $longitude,Longitude where $latitude, $longitude are the supplied value and Latitude,Longitude are the table's field name. – Atanu Mondal Jul 14 '14 at 12:04
  • how to i use insert_time in this scenario – user1738224 Jul 14 '14 at 14:36
0

The solution of Atanu Mondal worked almost flawlessly.

However I've got the same problem of user1738224 and I also needed the distance in Kilometers, not in miles, so I chanded the formula to 

SELECT ((ACOS(ROUND(SIN($latitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS($latitude * PI() / 180) * COS(Latitude * PI() / 180) * COS(($longitude - Longitude) * PI() / 180), 35)) * 180 / 3.141592653 ) * 60 * 1.8531) AS Distance FROM `table_name`

Hope this helps.

mcsf
  • 21
  • 7