开发者

Mapping location in MySQL

开发者 https://www.devze.com 2023-03-28 07:01 出处:网络
I have two tables: 1) One is the location table that is kept from android phone consists开发者_JAVA百科 of username, latitude, longitude, date, time.

I have two tables:

1) One is the location table that is kept from android phone consists开发者_JAVA百科 of username, latitude, longitude, date, time.

2) Another one is table that I kept country, region, province, postal code, city, latitude, longitude.

I want to mapping location(lat,lng) of table 1) using table 2) before insert to db. It's look simple but the problem is location of table 2) is just a stable point, otherwise the location of table 1) are points which traversal of each city. So, the location of table 1) is not similar to location of table 2).

Any one have idea for this problem ? Any formula or technique ? Appreciate your help.

edit: I tried this statement before insert statement

$city  =  mysql_query("SELECT p.city
    FROM place AS p
    ORDER BY ACOS(SIN(p.lng)*SIN('".$lng."')+COS(p.lng)*COS('".$lng."')*COS(p.lat-'".$lat."'))",$con);

but the result is Resource id #3 in the field, other fields also shown like this. c


so somthing like:

SELECT regions.*
FROM users, regions
WHERE users.user_id = $user_id
ORDER BY 
  ACOS ( 
    SIN(users.long) * SIN(regions.long) + 
    COS(users.long) * COS(regions.long) * COS(regions.lat - users.lat) 
  )
LIMIT 1

Added 2011-08-15

or in php like your exemple

$query  = "SELECT city
           FROM place
           ORDER BY
             ACOS(
               SIN(lng) * SIN({$lng}) +
               COS(lng) * COS({$lng}) * COS(lat - {$lat})
             )";
$resource = mysql_query($query);
$result = mysql_fetch_assoc($resource);
$city = $result['city'];


Thank you for every help.

$qc = mysql_query("SELECT *
    FROM place AS p
    ORDER BY MIN(ACOS(SIN(p.lng)*SIN({$lng})+COS(p.lng)*COS({$lng})*COS(p.lat-{$lat})))", $con);

while ($row = mysql_fetch_assoc($qc)) {
    $city = $row['city'];
    $district = $row['district'];
    $province = $row['province'];
    $region = $row['region'];
    $country = $row['country'];

    mysql_query("INSERT INTO " . $username . "_logs_" . $type . "(username,date,
                time,lat,lng,city,district,province,region,country,note,color)
                VALUES('".$username."','".$date."','".$time."',
                '".$lat."','".$lng."','".$city."','".$district."'
                ,'".$province."','".$region."','".$country."','unknown','unknown')", $con)
            or die("Cannot Insert to Table");
    mysql_close();
}

This is my final answer. I want to share for whoever weak in query like me :))

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号