地理编码 - 获取位置

Question

If I have a table with

id, location, latitude, longitude

and I would have a location code: (13.779242,100.472915) for example.

! have 2 questions: what field type should I use for the lat and lon? Double? or maybe the spacial things? but isn't that more for calculating the distance?

Anyway, I want to find the location (or the one most accurate) Am I thinking to easily if I would just do these 2 queries:

Select * from location_table WHERE latitude > 13.779242 AND longitude > 100.472915." LIMIT 1"; // return as row 1
Select * from location_table WHERE latitude < 13.779242 AND longitude < 100.472915." LIMIT 1"; // return as row2

Then do a compare

$lat_dis_1 = $row1->latitude - 13.779242;
$lat_dis_2 = 13.779242 - $row2->latitude;

if ($lat_dis_1 > $lat_dis_2) {
// row2 was closer
} else {
//row1 closer
}

and same for longitude... Or am i just thinking too simpel here?

Answer 1

The first thing you have to consider is that distance increases in a circle and not a square, meaning that just comparing longitude and latitude separately can especially in case of longer distances lead to false results. Here is a formula that explains the basic idea how you can relatively ieasily calcualte the true distance between two points: http://www.purplemath.com/modules/distform.htm.

An additional point is when you cross the date line, where 2 points can be geographically a few km appart, but using just this method would appear half a world away.

Yet another question is also what kind of performance you expect from this in comparison to accuracy. It would be a lot more efficient to use a simple square comparison if the accuracy is not that important, but speed is. Yet another possibility would be to combine the two techniques.

A sample of the first technique would be (didn't test :) ):

SELECT SQRT(POW(latitude-[INPUT LATITUDE], 2)+POW(longitude-[INPUT LONGITUDE])) as dist, * FROM location_table ORDER BY dist ASC LIMIT 1