PHP Radius搜索 [英] PHP Radius Search

问题描述

我将构建一个应用程序，用户可以在该位置周围的预定半径范围内看到兴趣点.

I'm going to build an app where the users can see points of interest in a predefined radius around their location.

我的第一个想法是将所有POI的纬度和经度存储在数据库中，并通过SQL将用户位置与POI的位置进行比较.

My first idea was to store the latitude and longitude of all POI's in a database and comparing the users location with the POI's location via SQL.

问题是我认为的性能.如果有成千上万个POI和成千上万个用户请求及其位置，那么这在经济上不是很便宜，或者对于今天的服务器来说这没有问题吗?

The problem is the performance I think. If there are thousands of POI's and thousands of user requests with their location, it wouldn't be very economically or is this no problem for todays servers?

我的下一个方法是将地图划分为四个象限，并且仅观察周围的象限.

My next approach was to divide the map in quadrants, and only observing the surrounding quadrants.

tl; dr:

总而言之，我正在寻找:

All in all I'm looking for:

• 一种进行半径搜索的方法
• 最好为其他用户缓存结果
• 注册新的POI时，缓存将更新.

如果您对如何实现这样的想法有任何想法，请告诉我.

If you have any ideas how to realize something like that, please let me know.

谢谢

Fabian

推荐答案

我认为您正在寻找的是 Harversine公式，该公式可让您找到球体(在本例中为地球)两点之间的距离.使用SQL的实现将是这样的:

I think what you are looking for is the Harversine formula, which it allows you to find the distance between two points in a sphere (in this case the Earth). An implementation using SQL would be something like this:

ACOS (
  SIN(RADIANS($latitude)) * 
  SIN(RADIANS(T.latitude))+ 
  COS(RADIANS($latitude)) * 
  COS(RADIANS(T.latitude))* 
  COS(RADIANS($longitude-T.longitud)))*6378.137 AS distance  

将此内容添加到查询的选择中，将返回一个称为距离计算的列(以Km为单位)，通常，用户($ latitude，$ longitude)与(T.latitude，T.longitude)之间的距离是多少，通常是表格的元素.

Adding this to the select of your query will return a column called distance calculating (in Km) how far is the point ($latitude,$longitude), normally the user, from (T.latitude,T.longitude), normally the element of the table.

如果要过滤，并且不显示超出特定距离的元素，则可以设置以下条件:

In case you want to filter, and don't show elements further than a certain distance you can make a condition like:

HAVING distance<$radius  

我想象您正在使用MySQL，如果是这种情况，则必须使用HAVING而不是WHERE在计算列(距离)上建立条件.

I imagine that you are using MySQL, if this is the case you have to use HAVING instead of WHERE to make a condition over a computed column (distance).

完整的查询示例如下:

SELECT T.*, ACOS (
      SIN(RADIANS($latitude)) * 
      SIN(RADIANS(T.latitude))+ 
      COS(RADIANS($latitude)) * 
      COS(RADIANS(T.latitude))* 
      COS(RADIANS($longitude-T.longitud)))*6378.137 AS distance
FROM your_table as T  
HAVING distance < $radius
ORDER BY distance LIMIT $limit

如果您想进一步优化性能，请为查询添加一个限制，以便例如获得10个最接近的位置.

If you want to optimize a bit more the performance add a limit to the query so you will have for example the 10 nearest places.

花点时间考虑空间数据类型以及因为它们是专门为这种工作而制作的.

Take your time to consider Spatial data types aswell since they were specificly made for this kind of work.

请注意，我不建议您将php变量直接插入查询中，这确实是不安全的，我只是作为示例这样做.

Note that I do not recommend you to insert your php variables directly into your query, is really unsecure, I did that only as an example.

希望这对您有所帮助.

这篇关于PHP Radius搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！