blocks|key|1555790|text|您应该为每个城市存储一个city_id,+latitude,+longitude，然后根据运行时输入计算距离。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1555791|entityMap^0|C|S|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

you should store <code>city_id, latitude, longitude</code> one for each city - then calculate the distances based on runtime input.

blocks|key|1555819|text|不要存储它，用经度和纬度计算它的运行时。极具可扩展性，与节省城市之间的所有距离相反。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1555820|您有一个参考点(San+Jose)并循环遍历所有城市记录并计算它的运行时(如果有许多记录，则由客户端完成此计算，可能是使用javascript之类的，因为如果让服务器执行该操作，它将很快付出代价)。JavaScript看起来可能如下所示：|1555821|var+R+=+6371;+//+Radius+of+the+earth+in+km
var+dLat+=+(lat2-lat1).toRad();++//+Javascript+functions+in+radians
var+dLon+=+(lon2-lon1).toRad();+
var+a+=+Math.sin(dLat/2)+*+Math.sin(dLat/2)+%2B
++++++++Math.cos(lat1.toRad())+*+Math.cos(lat2.toRad())+*+
++++++++Math.sin(dLon/2)+*+Math.sin(dLon/2);+
var+c+=+2+*+Math.atan2(Math.sqrt(a),+Math.sqrt(1-a));+
var+d+=+R+*+c;+//+Distance+in+km|code-block|syntax|javascript|1555822|以上代码来自这里|offset|length|1555823|注意:它是以公里为单位的，因为我是荷兰人，因此使用公制|1555824|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/27928/how-do-i-calculate-distance-between-two-latitude-longitude-points^0|0|0|0|6|2|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@$K|10|L|11|1|12]]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Don't store it, calculate it runtime with longitude and latitude. Extremely scalable, contrary to saving all distances between cities.

You have a reference point (San Jose) and loop through all your city records and calculating it runtime (in case of many records, have this calculation done by the client, probably with javascript or something, because if you have the server do it, it'll cost its toll all too soon). The JavaScript might look something like this:

<pre><code>var R = 6371; // Radius of the earth in km
var dLat = (lat2-lat1).toRad(); // Javascript functions in radians
var dLon = (lon2-lon1).toRad(); 
var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
 Math.cos(lat1.toRad()) * Math.cos(lat2.toRad()) * 
 Math.sin(dLon/2) * Math.sin(dLon/2); 
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
var d = R * c; // Distance in km
</code></pre>

Above code comes from <a href="https://stackoverflow.com/questions/27928/how-do-i-calculate-distance-between-two-latitude-longitude-points">here</a>

Note: It's in kilometers as I'm Dutch and thus using the metric system

blocks|key|1008120|text|我使用Neo4J进行类似的操作，它对任何类型的数据都有很好的缩放能力，这些数据可以表示为一个图形。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1008121|entityMap|0|LINK|mutability|MUTABLE|url|http://neo4j.org/^0|3|5|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

I am using <a href="http://neo4j.org/" rel="nofollow">Neo4J</a> for something similar, it scales really well for any type of data that can be represented as a graph.

blocks|key|1008136|text|正如其他人所注意到的，您可以存储每个条目的Lat/Long和弦，并在运行时使用类似的方法计算距离，这提供了公里/英里的距离输出：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1008137|function+distance($lat1,+$lng1,+$lat2,+$lng2,+$miles+=+true)
{
++++++++$pi80+=+M_PI+/+180;
++++++++$lat1+*=+$pi80;
++++++++$lng1+*=+$pi80;
++++++++$lat2+*=+$pi80;
++++++++$lng2+*=+$pi80;

++++++++$r+=+6372.797;+//+mean+radius+of+Earth+in+km
++++++++$dlat+=+$lat2+-+$lat1;
++++++++$dlng+=+$lng2+-+$lng1;
++++++++$a+=+sin($dlat+/+2)+*+sin($dlat+/+2)+%2B+cos($lat1)+*+cos($lat2)+*+sin($dlng+/+2)+*+sin($dlng+/+2);
++++++++$c+=+2+*+atan2(sqrt($a),+sqrt(1+-+$a));
++++++++$km+=+$r+*+$c;

++++++++return+($miles+?+($km+*+0.621371192)+:+$km);
}|code-block|syntax|javascript|1008138|编辑:这不适用于半径搜索范围内的n个匹配项。考虑到给定半径内城镇/城市的密度，最好将距离计算移到SQL中，因为它的速度要快得多，并且可以与x公里/英里内的城镇/城市相匹配。|1008139|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You could, as others have noted, store the Lat/Long coords for each entry and compute the distance using something similar to the following at runtime, which provides km/miles distance output:

<pre><code>function distance($lat1, $lng1, $lat2, $lng2, $miles = true)
{
 $pi80 = M_PI / 180;
 $lat1 *= $pi80;
 $lng1 *= $pi80;
 $lat2 *= $pi80;
 $lng2 *= $pi80;

 $r = 6372.797; // mean radius of Earth in km
 $dlat = $lat2 - $lat1;
 $dlng = $lng2 - $lng1;
 $a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlng / 2) * sin($dlng / 2);
 $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
 $km = $r * $c;

 return ($miles ? ($km * 0.621371192) : $km);
}
</code></pre>

EDIT: This is not suitable for n matches within a radius search. Given the density of towns/cities within a given radius, better to move the distance calculations into SQL as its far faster and you can match against those within x km/miles.

blocks|key|1008150|text|我多次使用的一个简单解决方案(但不是mysql)是创建一个用户定义函数some_distance_function，它包含四个参数latitude1、longitude1、latitude2、longitude2，它返回距离，然后根据该距离函数测试所有内容，查看每个项的距离是否小于或等于给定的值。如果你只有几千个地点，这是相当好和有效的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1008151|如果您需要针对数百万条记录运行此查询，您可能希望看到哪些GIS+(地理信息系统)扩展可供您选择的数据库使用，因为有更好的持久数据结构(至少在搜索能力方面)可以通过大量的位置进行搜索。|1008152|编辑:要给出微软如何做到这一点的例子，请参阅http://technet.microsoft.com/en-us/library/bb964712(v=sql.105).aspx|1008153|看起来MySQL一般支持空间扩展：|1008154|http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html|1008155|http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html|1008156|编辑二：|1008157|看来这个问题也可能有帮助。|1008158|在MYSQL中找到两点之间的距离。(使用Point数据类型)|1008159|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/bb964712(v=sql.105).aspx|1|2|3|https://stackoverflow.com/questions/2270181/find-the-distance-between-two-points-in-mysql-using-the-point-datatype^0|Z|M|1T|9|23|A|2E|9|2O|A|0|0|M|1V|0|0|0|0|1O|1|0|0|1Q|2|0|0|0|0|U|3|0^^$0|@$1|2|3|4|5|6|7|17|8|@$9|18|A|19|B|C]|$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]|$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1I|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|1J|8|@]|D|@$9|1K|A|1L|1|1M]]|E|$]]|$1|J|3|K|5|6|7|1N|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|1O|8|@]|D|@$9|1P|A|1Q|1|1R]]|E|$]]|$1|N|3|O|5|6|7|1S|8|@]|D|@$9|1T|A|1U|1|1V]]|E|$]]|$1|P|3|Q|5|6|7|1W|8|@]|D|@]|E|$]]|$1|R|3|S|5|6|7|1X|8|@]|D|@]|E|$]]|$1|T|3|U|5|6|7|1Y|8|@]|D|@$9|1Z|A|20|1|21]]|E|$]]|$1|V|3|-4|5|6|7|22|8|@]|D|@]|E|$]]]|W|$X|$5|Y|Z|10|E|$11|12]]|13|$5|Y|Z|10|E|$11|M]]|14|$5|Y|Z|10|E|$11|O]]|15|$5|Y|Z|10|E|$11|16]]]]

A simple solution I've used multiple times (but not with mysql) is create a user defined function <code>some_distance_function</code> with four parameters <code>latitude1</code>,<code>longitude1</code>,<code>latitude2</code>,<code>longitude2</code> which returns the distance and then just test everything against that distance function and see for every item, whether or not the distance is less than or equal to a given value. If you are only going to have a few thousand locations, this is quite fine and efficient.

If you need to run this query against millions of records, you might want to see what GIS (Geography Information Systems) extensions are available for your database of choice, as there are better (at least in terms of search-ability) persistent data structures for searching though vast numbers of locations.

Edit:
To give an example of how Microsoft does it, see <a href="http://technet.microsoft.com/en-us/library/bb964712(v=sql.105).aspx" rel="nofollow noreferrer">http://technet.microsoft.com/en-us/library/bb964712(v=sql.105).aspx</a>

It looks like MySQL supports spatial extensions in general:

<a href="http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html</a> 
<a href="http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html</a>

Edit II:

Looks like this question might also be helpful.

<a href="https://stackoverflow.com/questions/2270181/find-the-distance-between-two-points-in-mysql-using-the-point-datatype">Find the distance between two points in MYSQL. (using the Point Datatype)</a>

blocks|key|1555933|text|下面是一个使用RDBMS的解决方案。保留两张桌子|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1555934|具有聚集索引的CityByLat+{纬度，city_id+}和|unordered-list-item|1555935|具有聚集索引的CityByLng+{逻辑，city_id+}|1555936|当您需要从给定的纬度和经度找到一定半径内的城市时，您可以对这两个表进行有效的范围查询，从而使城市在一定的纬度和经度范围内。然后，您可以计算出从这样检索到的城市的实际距离。|1555937|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|M|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|N|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|O|8|@]|9|@]|A|$]]]|J|$]]

Here is a solution using RDBMS. Keep two tables 

<ul>
<li>CityByLat { latitude, city_id } with clustered index on latitude and </li>
<li>CityByLng { logitude, city_id } with clustered index on longitude </li>
</ul>

When you need to find cities within a certain radius from a given latitude and longitude you can do an efficient range query on the two tables to get cities within a certain range of latitude and longitude. You can then calculate the actual distance from only the cities thus retrieved.

I need to be able to display distance to n cities/towns from a particular location chosen by user. Its like clicking on a map and getting all destinations within 100 miles, only that it wont be a map but a link on webpage. 

I need to choose a solution that would scale-up from within a state to a country to globally potentially - which means from thousand to hundred thousand locations.

I though of storing CITY1_ID, CITY2_ID &amp; DISTANCE in a Relational DB table, but I doubt if it would scale well for a web application (million of rows).

Could this be done more efficiently using a NoSQL Database or Graph DB ? Or is RDBMS good enough for this problem with proper design?

Added: If I do not store in DB then how will I get something like: Get me all cities within 100 miles of San Jose?

How to store distance between cities and towns in DB efficiently

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要能够显示从用户选择的特定地点到n个城镇的距离。这就像点击一张地图，在100英里内得到所有目的地，只是它不是地图，而是网页上的链接。我需要选择一个解决方案--从一个州扩大到一个国家，到全球范围--这意味着从上千个地点扩大到十万个地点。我想将CITY1_ID、CITY2_ID和table存储在关系数据库表中，但我怀疑...

问如何在数据库中有效地存储城镇之间的距离
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在数据库中有效地存储城镇之间的距离EN