1

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

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.

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:

  • a way to do an radius search
  • at best caching the results for other users
  • the cache will be updated when a new POI is being registered.

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

Thank you

Fabian

Fabian
  • 87
  • 1
  • 10

1 Answers1

2

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

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

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).

A complete example of a query would be like this: 

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

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.

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.

Hope this helps you.

Asur
  • 3,727
  • 1
  • 26
  • 34