dongyue0263
2013-10-20 10:23 阅读 37
已采纳

MySQL查询从不同的表中获取数据并将其用于距离计算

I have two MySQL tables:

CREATE TABLE IF NOT EXISTS `wp_zipcode` (
  `zip` int(5) NOT NULL AUTO_INCREMENT,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  PRIMARY KEY (`zip`)
)

and

CREATE TABLE IF NOT EXISTS `wp_events` (
  `EventID` int(11) NOT NULL,
  `Event` varchar(256) NOT NULL,
  `Zip` int(11) NOT NULL,

Unfortunately I cannot alter the wp_events(it gets overwritten with new data al the time) and I am doing a "distance" search for the nearest events to the user. I am using Google's Example found here

( 3959 * acos( cos( radians(34.070358) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-118.349258) ) + sin( radians(34.070358) ) * sin( radians( lat ) ) ) ) AS distance FROM wp_events HAVING distance < 25 ORDER BY distance LIMIT 0 , 20

My issue is like this: For the algorithim to work, I need to have the lng and lat of a zipcode, but since Im searching in wp_events for the events, is there a way to get the event's zipcode from wp_events and somehow(this is where my question comes in) get the corresponding lat and lng for that specific zipcode from wp_zipcodes and return to the SQL statment to get the appropriate events near the desired zipcode.

thanks in advance

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    douzhan1935 douzhan1935 2013-10-20 10:51
    SELECT wp_events.*, 
           wp_zipcode.lat, 
           wp_zipcode.lng,
           ( 3959 * acos( cos( radians(34.070358) ) * cos( radians( wp_zipcode.lat ) ) * cos( radians( wp_zipcode.lng ) - radians(-118.349258) ) + sin( radians(34.070358) ) * sin( radians( wp_zipcode.lat ) ) ) ) AS distance
      FROM wp_events 
      LEFT JOIN wp_zipcode 
        ON wp_zipcode.zip = wp_events.Zip
    HAVING distance < 25 
     ORDER BY distance 
     LIMIT 0 , 20
    
    点赞 评论 复制链接分享
  • dtef9322 dtef9322 2013-10-20 10:38

    You can use JOIN, like this:

    SELECT wp_events.EventId, wp_events.Event, wp_events.Zip, wp_zipcode.lat, wp_zipcode.lng
    FROM wp_events
    JOIN wp_zipcode ON wp_events.Zip = wp_zipcode.zip
    
    点赞 评论 复制链接分享

相关推荐