使用 MySQL 空间索引设计一个标准化数据库以获取 5 个最近邻
我将使用带有空间索引的 MySQL 数据库，然后我将查询给定点（纬度/经度）的 5 个最近邻居，这些点将是多米尼加共和国的兴趣点。一旦我得到 5 个最近的点，我将需要位置名称、位置类型、位置地址以及每个点的（纬度、经度）。
设计数据库的正确方法应该是什么？我正在考虑做以下事情： 表：名称 列：ID、名称
表：类型 列：ID、类型
表：街道 列：ID、街道
表：街道号码 列：ID、街道号码
表：行政区 列：ID，行政区
表：城市 列：ID、城市
表：地理点 列：纬度、经度、GeometryPoint
我遗漏了什么吗？任何建议。有谁知道我可以在哪里读到它？这是一个学校项目，应该将其与移动应用程序一起使用，根据用户对实际位置的选择类型，向用户显示 5 个最近的兴趣点（加油站、餐馆、药房等）。
另外我可以在哪里获取数据？我的意思是有没有一种方法可以获取我国家的所有街道名称、行政区、城市并将其放入我的数据库中。我认为我可以自己创造兴趣点。
I will be using MySQL with spatial index for a database, then i will query for 5 nearest neighbors from a given point(lat/long), these points will be interest points in Dominican Republic. Once that i get the 5 nearest points i will need the Name of the Location, Type of the Location, Address of the location and (lat,long) for each point.
What should be the right way to design my database? i was thinking of doing the following:
Table: Name
Columns: ID, Name
Table: Type
Columns: ID, Type
Table: Street
Columns: ID, Street
Table: Street Number
Columns: ID, Street Number
Table: Borough
Columns: ID, Borough
Table: City
Columns: ID, City
Table: Geopoint
Columns: Latitude, Longitude, GeometryPoint
Am i missing anything? Any Suggestion. Does anyone knows some place where i can read about it? This is a school project, should be using this along with mobile app to show the users 5 nearest point of interest(gas station, restaurants, Pharmacy, etc) depending of their type of choice to their actual location.
Also where i can fetch the data? I mean is there a way where i can get all the streets name, boroughs, city of my country and put it in my database. The points of interest i can create myself i think.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，该结构在标准化方面看起来不错。您可以通过谷歌搜索“数据库规范化规则”来阅读有关此主题的更多信息。但您应该意识到，通过分离每个属性（街道表、街道号码表等），您将获得复杂的查询。要获得类似“POI XY, Streetname 2, Somecity, lat=42.4, long=13.2”的结果，您必须连接至少四个表。如果性能不是问题，请继续；-)
您可以从 openstreemap.org 获取数据（如果可用）或从 geonames.org 获取一些 poi
。
马丁
first of all the structure looks good in terms of normalization. You can read some more about this topic by just googling for "database normalization rules". But you should be aware of the fact that you will get complex queries by separating each property (Street-table, street-number-table etc.). To get a result like "POI XY, Streetname 2, Somecity, lat=42.4, long=13.2" you would have to join at least four tables. If performance is not an issue, go ahead ;-)
You can fetch data (if available) from openstreemap.org or some poi's from geonames.org
Regards,
Martin
我知道这是一项家庭作业，但我建议不要使用 MySQL 空间 - 它没有实现大部分空间功能，而且它实现的只是边界框实现。
我推荐 PostGIS、spatialLite 或 MongoDB 空间。
I know this is a homework assignment but I would reccomend against using MySQL spatial - it has not implemented most of the spatial functions and those it does implement are just boundbox implementations.
I would reccomend either PostGIS, spatialLite or MongoDB spatial.