计算400个城市之间的距离并为此优化MySQL? [英] Calculating distance between 400 cities and optimizing MySQL for it?

问题描述

我有大约400个城市的数据库.用户选择他所居住的城市，并输入他愿意旅行的距离(例如40公里). city_id与其他一些不相关的信息一起存储在搜索表中.用户提交表单时，他将重定向到结果页面，在该页面上显示搜索表中符合标准的所有结果，但我想按城市表单用户所在城市与结果城市之间的距离进行排序，并对结果进行排序那个距离(最接近的第一).我如何计算城市之间的距离，然后排序?我必须向每个城市添加哪些表格，以便实际计算距离，有什么办法可以运行一个循环来查找每个城市的坐标并将其存储?我当前的城市表只有id，name和zip

I've got a database of around 400 cities. User selects a city in which he lives, and enters the distance which he is willing to travel (for example, 40kilometers). The city_id is stored into search table, with some other irrelevant information. When user submits the form, he's redirected to results page, on which all results from search table are shown, that meet the criteria, but I want to order them by the distance between city form user's city and the result city, and order the results by that distance (closest first). How would I calculate the distance between the cities, and then order them? What tables would I have to add to each city, so I would actually calculate the distance, and is there any way I could just run a loop which would find the coordinates of each city and store them? My currenty cities table only has id, name and zip

任何帮助将不胜感激.

推荐答案

我看到了两种可能的解决方案.

I saw two possible solutions.

第一个:
对于数据库中每个城市商店的纬度和经度；当用户收到查询时，您可以计算与其他每个城市的距离并返回结果.
专业人士是，您可以在db中添加每个城市，而无需添加其他信息.
在这里，您也可以找到公式，示例和代码来查找经纬度距离计算...

First one:
For each city store in database its latitude and longitude; when user gets a query, you calculate distance with every other city and return results.
Pro is you can add every city in db without the need to add other info.
Here you can find formulas, samples and code too for latitude-longitude distance calc...

第二:
创建具有三个字段的表cities_dist: city1_id, city2_id, distance 并放入您城市之间的所有可能组合. 这样，您就可以编写查询，查询所选城市是city1_id还是city2_id.
优点是您可以使用不带任何calc的简单查询，而缺点是您每次在数据库中插入新城市时都必须填写此表.

Second:
Create a table cities_dist with three fields: city1_id, city2_id, distance and put inside every possible combination among your cities. With that you can write a query whit selected city beeing city1_id or city2_id.
Pro is that you can use a simple query without any calc, while cons are that you have to fill this table anytime you insert a new city in your database.

在用户评论后
假设您有三个城市

EDITED after user comment:
Imagine you have three cities

ID  NAME
1   New York
2   Rome
3   Berlin

那张桌子应该看起来像

CITY1  CITY2  DIST
1      2      1500
1      3      1200
2      3       400

当用户想要从柏林起飞时，您可以使用

When user want to fly from Berlin you could use

SELECT c1.name, c2.name, cd.dist 
FROM cities_dist cd
  INNER JOIN cities c1 ON cd.city1 = c1.id
  INNER JOIN cities c2 ON cd.city2 = c2.id
WHERE cd.city1 = your_id
   OR cd.city2 = your_id
ORDER BY cd.dist ASC

这篇关于计算400个城市之间的距离并为此优化MySQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！