MySQL纬度和经度表设置 [英] MySQL latitude and Longitude table setup

问题描述

我想将位置的纬度和经度值存储在mysql数据库表中.考虑到未来，我将希望能够在特定位置的特定半径内找到这些位置.话虽如此，我应该将纬度和经度值存储在哪些数据类型中?请您为我提供诸如以下列的创建表脚本:

I want to store latitude and longitude values of places in a mysql database table. With the future in mind I will want to be able to find these places within a certain radius of a specific location. Having said that, what datatypes should I store the latitude and longitude values in? Please could you provide me with a create table script for columns like so:

place_id  |  lat  |  long

上表中可能缺少一列，该列会为我提供当前可能看不到的其他信息吗?

Is there perhaps a column I am missing in the above table that will provide me with additional information that I may not see I need at the current time?

感谢您的帮助.

推荐答案

您应该将这些点存储在数据类型为Point的单列中，您可以使用SPATIAL索引对其进行索引(如果表类型为MyISAM ):

You should store the points in a singe column of datatype Point which you can index with a SPATIAL index (if your table type is MyISAM):

CREATE SPATIAL INDEX sx_place_location ON place (location)

SELECT  *
FROM    mytable
WHERE   MBRContains
               (
               LineString
                       (
                       Point($x - $radius, $y - $radius),
                       Point($x + $radius, $y + $radius)
                       )
               location
               )
        AND Distance(Point($x, $y), location) <= $radius

这将大大提高查询的速度，例如在给定半径内查找所有对象".

This will drastically improve the speed of queries like "find all within a given radius".

请注意，最好使用普通的TM公制坐标(东和北)，而不要使用极坐标(纬度和经度).对于小半径，它们足够精确，并且计算也大大简化了.如果您所有的点都在一个半身中并且远离极点，则可以使用一个中央子午线.

Note that it is better to use plain TM metrical coordinates (easting and northing) instead of polar (latitude and longitude). For small radii, they are accurate enough, and the calculations are simplified greatly. If all your points are in one hemishpere and are far from the poles, you can use a single central meridian.

您当然仍然可以使用极坐标，但是计算MBR和距离的公式会更复杂.

You still can use polar coordinates of course, but the formulae for calculating the MBR and the distance will be more complex.

这篇关于MySQL纬度和经度表设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！