Sampling nearest points?

Question

MySQL

I have a MySQL database with the following table:
CREATE TABLE IF NOT EXISTS `my_table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`longitude` double NOT NULL,
`latitude` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
It is necessary to make a request for a selection of all records that are (in terms of longitude and latitude coordinates) within a radius of N meters from point A (whose coordinates are known).
PS Before that, I stored coordinates using the POINT type, but after a long search on the Internet, I realized that MySQL has very limited possibilities for working with this type, unlike, for example, PostgreSQL (PostGIS). Actually, this is why I had to switch to storing longitude and latitude values ​​in different fields.

Answer 1

Use the mysql spatial engine functions. dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html
There are also special indexes and optimized search on them. You just need to turn the lat,lng fields into one GeoPoint type field and create a spatial index on it. And then it's a matter of technique to make a selection within the square by index and cut off to a circle through having. It is better to do just that, because it uses a special index for this. I don’t know what you have truncated there, but we checked the performance when searching by two fields and by the point field, so the latter showed many times greater performance.

Answer 2

Well, if you are interested in a function that translates two coordinates into the distance between them, I will gladly share a piece:

<?php
    private function LatLngDist($p, $q) {
        $R = 6371; // Earth radius in km

        $dLat = (($q[0] - $p[0]) * pi() / 180);
        $dLon = (($q[1] - $p[1]) * pi() / 180);
        $a = sin($dLat / 2) * sin($dLat / 2) +
                cos($p[0] * pi() / 180) * cos($q[0] * pi() / 180) *
                sin($dLon / 2) * sin($dLon / 2);
        $c = 2 * atan2(sqrt($a), sqrt(1 - $a));

        return $R * $c;
    }

Answer 3

If we do not take into account the fact that these are coordinates on the surface of the geoid, but consider them to be coordinates on the plane, then the Pythagorean theorem will do.

In a right triangle, the square of the length of the hypotenuse is equal to the sum of the squares of the lengths of the legs.

SQRT(POW(latitude-latitudeA,2)+POW(longitude-longitudeA,2))<N

Answer 4

The output of points with sorting - "first, those that are closer to the point at the address Moscow, Narodnogo Opolcheniya street."
SELECT *,
(ABS(lon-37.48488200)+ABS(lat-55.78553900)) as crd
FROM `pvz`
#WHERE `ds_full_addr` LIKE '%People%'
ORDER BY `crd` ASC

Answer 5

zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/

Answer 6

If there are a lot of points and stored like this, then you can try to split it into squares and first get the square and its neighbors by coordinates, and then sort through only them, well, you can calculate the square of the distance (it takes a long time to extract the root, but you can do without it)

Answer 7

I made a selection of a square with a side of 2 radii with a query, and more accurately, for each point, I counted the distances in the server part of the application. So it is easier to scale and indexes will be involved.

Answer 8

my similar question: habrahabr.ru/qa/23016/
On optimizing search by entering a point into a square for Mysql
www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Answer 9

MySQL forum , hope it helps