如何在数据库中存储世界各地的所有地理位置? [英] How to store all geographical locations around the world in Database?
问题描述
我正在为一个旅游站点工作，我需要在该站点中存储游客前往的旅游景点.我需要地点在位置表中是唯一的，这样我才能知道特定地点的受欢迎程度等. 我还需要与我一起存储的所有国家，州和城市，因为我不能依赖用户输入. 该数据库是MySQL. 看到这些位置可用的数据集，我发现存在一个在国家之间嵌套城市的问题，这些国家可能使用省，州，县等.
I am working for a travel site, where i need to store the tourist spots which tourists traveled to. I need the spots to be unique in the locations table so that i can know the popularity of a particular spot etc. I will also need also need all countries, states, cities stored with me because i cannot depend on user input. The database is MySQL. Seeing the data sets available for such locations i see there is a problem of nesting of cities across countries which may use provinces, states, counties etc.
所以，我的问题是如何设计架构，以便我可以存储所有位置. 我当时正在考虑为国家，州，城市和景点建立桌子. spots表将包含spot_name，cityId，stateId，countryId和一些具有经度和纬度范围的字段. 这样，我就可以通过它们的地理区域来识别相同的地点.
So, my question is how to design the schema so that i can store all the locations. I was thinking about having tables for countries, states, cities, and spots. the spots table will contain spot_name, cityId, stateId, countryId, and some fields to have longitude and latitude bounds. This way i can identify same spots by their geopositions.
但是同样，由于州/省/县等问题，此解决方案将不起作用. 您能否建议一下如何构建模式，以及如何使用正确的数据为它播种，以使对用户输入的依赖性降到最低.
But again, this solution won't work because of the states/provinces/counties etc. problem. Can you please suggest how to build the schema and go about seeding it with correct data so that dependency on user input is minimum.
推荐答案
您应该使用地理空间数据库，因为这样可以将国家和州等位置存储为空间实体，从而可以正确确定嵌套.
you should use a geospatial database as then you can store your locations like countries and states as spatial entities and so can determine the nesting correctly.
如果您不能使用它，则可以使用法线表中的字符串来模拟地理空间位置，方法是将世界划分为网格，然后递归地细分网格的每个正方形.
If you can't use one you can simulate geospatial positions using strings in a normal table by dividing the world up into a grid, then subdividing each square of the grid recursively.
例如，将世界划分为9个正方形，从左上到右下以1-9编号.在这些大方块中的任何内容都只有一位数字的引用.然后将每个平方除以9，在这个级别上的任何东西都有2位数的参考.所以11是左上角的正方形，99是右下角的正方形.
For example divide the world into 9 squares, numbered 1-9 from top left to bottom right. Anything which is in these large squares has only a single digit reference. Then divide each square into 9 and anything which is at this level has a 2 digit reference. so 11 is the top left square and 99 is the bottom right square.
重复此过程，直到获得所需的精度为止.单个功能的参考号可能长10位数，即5624357899，但是您会知道，该功能将出现在任何较大的功能(位数较少，以5624357之类的相同字符串开头)的内部.因此，您所在的国家/地区将用较少的数字，因为它们较大，但是您的国家/地区单个位置会更多，因为它们更小且位置更准确.
Repeat this process until you have the precision that you need. a single feature might have a reference 10 digits long 5624357899 but you would know that this would be inside any larger feature which is fewer digits which starts with the same string like 5624357. So your countries would have fewer digits because they are larger, but your individual locations would have more because they are smaller and more accurately located.
这只会给您路线的大致位置(对较长的细长特征不利)，但可能足够合适
This will only give you a course approximation of location (and will be bad for long thin features) but might be suitable enough
第一个网格将如下所示:
The first grid will look like this:
______________________________
| | | |
| 1 | 2 | 3 |
| | | |
|_________|_________|_________|
| | | |
| 4 | 5 | 6 |
| | | |
|_________|_________|_________|
| | | |
| 7 | 8 | 9 |
| | | |
|_________|_________|_________|
第二轮如下所示(为简单起见，仅完成了第一个方框):
The second round looks like this (only first square completed for simplicity):
______________________________
|11|12 |13| | |
|---------| 2 | 3 |
|14|15 |16| | |
|---------| | |
|17|18 |19| | |
|_________|_________|_________|
| | | |
| 4 | 5 | 6 |
| | | |
|_________|_________|_________|
| | | |
| 7 | 8 | 9 |
| | | |
|_________|_________|_________|
重复此过程，直到对您的目的有足够好的近似为止.
you repeat this process until you have fine enough approximation for your purposes.
这篇关于如何在数据库中存储世界各地的所有地理位置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
