hive - Finding the closest location to a lat and long value -


i have 2 tables t1 , t2 (t1 has 1/10th of size of t2). each table has 2 columns <lat, long> contain latitude , longitude of points. each row in t1 i'd find row in t2 closest it. efficient query doing this? hive have sort of libraries geospatial search?

you'll need bit of trig.

please refer article on database journal

the last routine believe looking (you'll need modify use):

create definer=`root`@`localhost` procedure closest_restaurants_optimized` (in units varchar(5), in lat decimal(9,6), in lon decimal(9,6),  in max_distance smallint, in limit_rows mediumint) begin     declare one_degree_constant tinyint;     declare earth_radius_constant smallint;     declare lon1, lon2, lat1, lat2 float;       if units = 'miles'         set one_degree_constant   = 69;         set earth_radius_constant = 3959;     else -- default kilometers         set one_degree_constant   = 111;         set earth_radius_constant = 6371;     end if;       set lon1 = lon-max_distance/abs(cos(radians(lat))*one_degree_constant);     set lon2 = lon+max_distance/abs(cos(radians(lat))*one_degree_constant);     set lat1 = lat-(max_distance/one_degree_constant);     set lat2 = lat+(max_distance/one_degree_constant);     select pm1.post_id, p.post_title,         round((earth_radius_constant * acos( cos( radians(lat) )              * cos( radians(pm1.meta_value) )              * cos( radians(pm2.meta_value) - radians(lon)) + sin(radians(lat))              * sin( radians(pm1.meta_value)))         ), 3) distance     goodfood_wp_md20m_postmeta pm1,          goodfood_wp_md20m_postmeta pm2,         goodfood_wp_md20m_posts    p      pm1.meta_key = 'latitude' , pm2.meta_key = 'longitude'     , pm1.post_id = pm2.post_id     , pm1.post_id = p.id     , p.post_status = 'publish'     , pm2.meta_value between lon1 , lon2      , pm1.meta_value between lat1 , lat2     order distance asc     limit limit_rows; end

-

Comments

Post a Comment

Popular posts from this blog

c# - Binding a comma separated list to a List<int> in asp.net web api -

the default binder in web api expecst http://url.com/webapi/report/?pageids=3243&pageids=2365 to bind public ihttpactionresult report(list<int> pageids){ // exciting webapi code} i wish bind http://url.com/webapi/report/?pageids=3243,2365 as running out of space in url get . i have created class public class commaseparatedmodelbinder : system.web.http.modelbinding.imodelbinder { public bool bindmodel(httpactioncontext actioncontext, modelbindingcontext bindingcontext) { //binding in here } } and registered in webapiconfig.cs var provider = new simplemodelbinderprovider( typeof(list<int>), new commaseparatedmodelbinder()); config.services.insert(typeof(modelbinderprovider), 0, provider); i have altered method signature use model binder so public ihttpactionresult report( [modelbinder] list<int> pageids){ // exciting webapi code} however break point in binder not being
Read more

Delphi 7 and decode UTF-8 base64 -

in delphi 7, have widestring encoded base64(that received web service widestring result) : pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg== when decoded it, result not utf-8: <?xml version="1.0"?> <string>طھط³طھ</string> but when decoded base64decode.org, result true : <?xml version="1.0"?> <string>تست</string> i have use encddecd unit decodestring function. the problem have using decodestring . function, in delphi 7, treats decoded binary data being ansi encoded. , problem text utf-8 encoded. to continue encddecd unit have couple of options. can switch decodestream . instance, code produce utf-8 encoded text file data: {$apptype console} uses classes, encddecd; const data = 'pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg=='; var input: tstringstream; output: tfilestream; begin input := tstringstream.create(data); try output := tfilestream.
Read more

html - Is there any way to exclude a single element from the style? (Bootstrap) -

so, have img logo, overlaid on div styled background-image: url("...") , , both elements inside 2 div classes .to-color , .to-overlay respectively, black transparent overlay. is there way exclude logo style given these 2 classes? here's jsfiddle example: link html , css example: - html - <div class="to-color"> <div class="to-overlay"> <div class="respo"> <div class="row"> <div class="col-xs-4 col-xs-offset-4 text-center"> <img src="http://ansonalex.com/wp-content/uploads/2011/06/google-logo-768x260.png" alt="..." class="img-responsive"> </div> </div> </div> </div> </div> - css - .to-color { background-color: #000; } .to-overlay { opacity : 0.5; } .respo { background-image: url("https://source.unsplash.com/category/objects/1600x900"); background-position: center center;
Read more