Geo Distance calculations in Hive and Java
March 23, 2016
Eran Orgad
Geographical distance is the
distance measured along the surface of the earth.
The distances between points are defined by geographical
coordinates (Geo Location cords) in terms of latitude and longitude.
Each pair represent a location as:
Lexington MA:
Latitude : 42.4428, Longitude: -71.2317
Mountain View CA:
Latitude: 37.405990600586,
Longitude: -122.07851409912
Distance: 2676.09225228497
Miles
To be able to calculate the distance on Hortonworks HDP
using Hive (and Tez) let’s generate some sample data that includes some pairs
of Geolocation coordinates.
Data file called: distance1.csv
42.28,-71.87,42.28,-71.86
42.00,-71.87,42.28,-71.11
42.28,-71.87,42.28,-71.86
42.28,-71.87,42.28,-71.22
42.00,-71.87,42.28,-72.33
42.28,-71.87,42.28,-70.44
42.00,-71.87,42.28,-71.55
42.28,-71.87,41.28,-71.66
42.00,-71.87,43.28,-71.77
42.28,-71.87,44.28,-71.88
42.00,-71.87,45.28,-71.99
42.28,-71.87,46.11,-71.00
42.00,-71.87,47.22,-71.00
42.4428,-71.2317,37.405990600586,-122.07851409912
….
We can create a schema to be able to read the content in
Hive
Here is the table I created:
hive> show create table distancecalc;
OK
CREATE EXTERNAL TABLE distancecalc(
src_lat double,
src_long double,
dest_lat double,
dest_long double)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY
','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://sandbox.hortonworks.com:8020/tmp/dist2'
TBLPROPERTIES (
'COLUMN_STATS_ACCURATE'='false',
'numFiles'='1',
'numRows'='-1',
'rawDataSize'='-1',
'totalSize'='338',
'transient_lastDdlTime'='1458866694')
After placing my distance1.csv in the /tmp/dist2/ directory I can query the
content in hive:
The following query will produce a calculation of distance
with every given pair of geo location coordinates:
select src_lat, src_long, dest_lat, dest_long,
60*1.1515*(180*(acos(((sin(radians(src_lat))*sin(radians(dest_lat)))
+
(cos(radians(src_lat))*cos(radians(dest_lat))*cos(radians(src_long-dest_long))))))/PI())
as distancecalc
from distancecalc;
screen-shot-2016-03-25-at-122643-pm.png
Last entry is the distance between Lexington MA and Mountain
View CA.
Java code to calculate the distance: /* * Class to calculate geo-location distance * eorgad - Hortonworks.com 9/30/2014 */ package geodistance; import java.sql.SQLException; public class calcGeoDistance { public static double distance(double lat1, double lon1, double lat2, double lon2, String string) { double theta = lon1 - lon2; double dist = Math.sin(deg2rad(lat1)) * Math.sin(deg2rad(lat2)) + Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * Math.cos(deg2rad(theta)); dist = Math.acos(dist); dist = rad2deg(dist); dist = dist * 60 * 1.1515; if (string == "K") { dist = dist * 1.609344; } else if (string == "N") { dist = dist * 0.8684; } return (dist); } /*###################################################################*/ /*# This function converts decimal degrees to radians #*/ /*###################################################################*/ privatestaticdouble deg2rad(double deg) { return (deg * Math.PI / 180.0); } /*###################################################################*/ /*# This function converts radians to decimal degrees #*/ /*###################################################################*/ privatestaticdouble rad2deg(double rad) { return (rad * 180 / Math.PI); }
publicstaticvoid main(String[] args) throws SQLException {
System.out.println(distance(42.4428,
-71.2317, 37.405990600586, -122.07851409912, "M") + " Miles\n");
// lexington to mountain view
// 42.4428 -71.2317 37.405990600586 -122.07851409912
}
}
... View more
