Using java + Mysql to realize nearby function examples

preface

Today, with the extensive development of mobile Internet, app development has become the first choice for many enterprises to enter the mobile Internet. The author has developed many apps and found that many apps have such a function, that is, to get people nearby. How to get people nearby? In fact, it is very simple to record the user's coordinate (longitude and latitude) information to the database at all times, and then search all users in the database with the current coordinate position within a certain range according to the current user's coordinates.

In fact, for the distance between two subjects whose geographical location will not change, it is best to directly static the result. That is, write it directly in the configuration.

For example, find a subway station near your home.

In this case, generally speaking, the subject of "home" will not easily "run around". It doesn't make sense to calculate the distance once every query. It is better to query directly after the distance is persisted directly.

Another case:

Get the subway station near the app user's location

In this case, the geographical location of the user is changed. So you have to calculate the actual distance in real time every time.

Realization idea

Taking the earth as a standard sphere, the arc length of the great circle between two points on the sphere is calculated by using the spherical distance formula.

Spherical distance

Just know the latitude and longitude between two points.

Of course, this calculation has to be put in the database and returned according to the distance. Just bring the above formula into SQL.

Example of nearby subway station

Example of subway station construction

SQL example

Targetlat and targetlng are the geographical location of the user.

This can really achieve the goal. However, this is to sort all data after calculating the distance from the user once.

When the number of subway stations is too large, this operation is not very elegant. Not only is it not elegant enough, but the efficiency is scary.

optimization

In fact, you can filter out a lot of data before calculating the distance.

There is no need to calculate the distance of subway stations in the United States when calculating the distance of subway stations in Shanghai.

In most applications, some unnecessary data can be filtered out first.

For example, if the data is city specific, you can change the SQL to the following:

The above improvement is to eliminate most of the data to be calculated before calculation. There is no need to look for a Changsha subway station in Shanghai first.

Of course, this situation is a little special, because you can know the user's city in advance.

Another improvement is:

Take the user's position as the center of the circle, draw a circle with radius r, and then deduce the longitude and latitude range of the circumscribed quadrilateral of the circle. Before calculating the distance, filter out the data outside the longitude and latitude of the external quadrilateral.

Specify an ideal radius R and filter out the data that cannot meet the conditions first.

Inverse extrapolation of circumscribed quadrilateral range

The tool Maven coordinates used here are as follows:

In this case, the SQL can be changed as follows:

Lng1 and lat2 above are the range of the circumscribed quadrilateral.

References: http://blog.csdn.net/a364572/article/details/50483568

Sample source code

service： https://github.com/hylexus/bl...

Initialization data: https://github.com/hylexus/bl...