Назад
Регистрация Войти
Войти через VK Войти через FB Войти через Google Войти через Яндекс




Поиск по сайту




Наш Telegram бот: @htmlweb_bot

Координаты в MySQL - POINT в MySQL

Чтобы хранить географические координаты (долготу и широту, latitude, longitude) в БД MySQL, есть замечательный тип данных — POINT. Он представляет собой структуру из нескольких полей.

К этому типу можно применять встроенные функции. Именно эти функции определяют, какие данные необходимо хранить: точку или линию, а может даже множество точек или множество линий.
Разберем на примерах. Создадим небольшую таблицу geo в реляционной БД MySQL следующим SQL-запросом:

CREATE TABLE IF NOT EXISTS `geo` (
    `id` id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `place_name` varchar(40) NOT NULL,
    `coordinates` point NOT NULL,
    SPATIAL INDEX(coordinates)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

Для того чтобы вставить строку в таблицу, воспользуемся конструкцией INSERT

INSERT INTO `geo` (`place_name`, `coordinates`) VALUES (‘Apartment’, PointFromText(‘POINT(49.234 2.5273)’));

У этого типа есть один недостаток: данные нельзя просмотреть в обычном phpMyAdmin.

Для того чтобы получить долготу и широту, придется воспользоваться вот таким SQL-запросом:

SELECT X( `coordinates` ) , Y( `coordinates` ),
    ST_Distance_Sphere(coordinates, POINT(' . $res['lat'] . ', ' . $res['lng'] . ')) as distance
    FROM `geo`

Функции X и Y являются синонимами изначальных ST_X() и ST_Y()

При экспорте/импорте нельзя использовать Copy/Paste, нужно сохранять в файл и загружать из файла.

Чтобы вывести три ближайших точки от заданных координат, используйте:

SET @home = ST_GeomFromText('POINT(-118.2819136 34.0261177)');
SELECT place_name, ST_AsText(coordinates) as Coord,
    ST_DISTANCE(@home, coordinates) AS dist
    FROM geo
    ORDER BY dist
    LIMIT 3

Чтобы определить точку, используйте либо POINT(-118.2845938, 34.0252385), либо ST_GeomFromText('POINT(-118.2845938 34.0252385)'). Чтобы увидеть значение в удобочитаемой форме, используйте ST_AsText().

Выведем из базы записи, упорядоченные по удалению от нашей точки с координатами: 51.5177, -0.0968

SELECT id, name, X(coordinates) AS "latitude", Y(coordinates) AS "longitude", (
    GLength(
        LineStringFromWKB(
            LineString(
                coordinates,
                GeomFromText('POINT(51.5177 -0.0968)')
            )
        )
    )
)
AS distance
FROM geo
ORDER BY distance;


select *, ST_Distance_Sphere( point ('input_longitude', 'input_latitude'),
    point(longitude, latitude)) * .000621371192
    as `distance_in_miles`
    from `TableName`
    having `distance_in_miles` <= 'input_max_distance'
    order by `distance_in_miles` asc

Результаты возвращаются в милях. Так что если вы хотите в километрах, просто используйте .001, вместо .000621371192(что для миль).

Пространственные функции MySQL

Area() - Возврат полигональной или мультиполигональной области
AsBinary(), AsWKB() - Преобразование из формата внутренней геометрии в WKB
AsText(), AsWKT() - Преобразование из формата внутренней геометрии в WKT
Buffer() - Вернуть геометрию точек на заданном расстоянии от геометрии
Centroid() - Вернуть центроид как точку
Contains() - Содержит ли MBR одной геометрии MBR другой
Crosses() - Пересекает ли одна геометрия другую
Dimension() - Размерность геометрии
Disjoint() - Не пересекаются ли MBR двух геометрий
EndPoint() - Конечная точка LineString
Envelope() - Вернуть MBR геометрии
Equals() - Равны ли MBR двух геометрий
ExteriorRing() - Вернуть внешнее кольцо многоугольника
GeomCollFromText(), GeometryCollectionFromText() - Вернуть сбор геометрии из WKT
GeomCollFromWKB(), GeometryCollectionFromWKB() - Вернуть сбор геометрии из WKB
GeometryCollection() - Создание коллекции геометрии из геометрий
GeometryN() - Вернуть N-ю геометрию из коллекции геометрии
GeometryType() - Вернуть имя типа геометрии
GeomFromText(), GeometryFromText() - Вернуть геометрию из WKT
GeomFromWKB(), GeometryFromWKB() - Вернуть геометрию из WKB
GLength() - Длина возврата LineString
Возвращает длину из LineString или MultiLineString. Длина MultiLineString значения равна сумме длин его элементов. Если аргумент - NULL или пустая геометрия, возвращаемое значение - NULL. 

SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT GLength(GeomFromText(@ls));
+----------------------------+
| GLength(GeomFromText(@ls)) |
+----------------------------+
|         2.8284271247461903 |
+----------------------------+

InteriorRingN() - Вернуть N-е внутреннее кольцо Многоугольника
Intersects() - Пересекаются ли MBR двух геометрий
IsClosed() - Является ли геометрия замкнутой и простой
IsEmpty() - Является ли геометрия пустой
IsSimple() - Простая ли геометрия
LineFromText(), LineStringFromText() - Построить LineString из WKT
LineFromWKB(), LineStringFromWKB() - Построить LineString из WKB
LineString() - Построить LineString из значений Point
MBRContains() - Содержит ли MBR одной геометрии MBR другой
MBRDisjoint() - Не пересекаются ли MBR двух геометрий
MBREqual() - Равны ли MBR двух геометрий
MBRIntersects() - Пересекаются ли MBR двух геометрий
MBROverlaps() - Перекрываются ли MBR двух геометрий
MBRTouches() - Соприкасаются ли MBR двух геометрий
MBRWithin() - Находится ли MBR одной геометрии в MBR другой
MLineFromText(), MultiLineStringFromText() - Создайте MultiLineString из WKT
MLineFromWKB(), MultiLineStringFromWKB() - Создайте MultiLineString из WKB
MPointFromText(), MultiPointFromText() - Создайте MultiPoint из WKT
MPointFromWKB(), MultiPointFromWKB() - Создайте MultiPoint из WKB
MPolyFromText(), MultiPolygonFromText() - Создайте MultiPolygon из WKT
MPolyFromWKB(), MultiPolygonFromWKB() - Создайте MultiPolygon из WKB
MultiLineString() - Построение MultiLineString из значений LineString
MultiPoint() - Создание MultiPoint из значений Point
MultiPolygon() - Построить MultiPolygon из значений Polygon
NumGeometries() - Возвращает количество геометрий в коллекции геометрии
NumInteriorRings() - Вернуть количество внутренних колец в многоугольнике
NumPoints() - Вернуть количество точек в LineString
Overlaps() - Перекрываются ли MBR двух геометрий
Point() - Построить точку по координатам
PointFromText() - Построить точку из WKT
PointFromWKB() - Построить точку из WKB
PointN() - Вернуть N-ю точку из LineString
PolyFromText(), PolygonFromText() - Построить многоугольник из WKT
PolyFromWKB(), PolygonFromWKB() - Построить многоугольник из WKB
Polygon() - Построить многоугольник из аргументов LineString
SRID() - Вернуть идентификатор системы пространственной привязки для геометрии
ST_Area() - Возврат полигональной или мультиполигональной области
ST_AsBinary(), ST_AsWKB() - Преобразование из формата внутренней геометрии в WKB
ST_AsText(), ST_AsWKT() - Преобразование из формата внутренней геометрии в WKT
ST_Buffer() - Вернуть геометрию точек на заданном расстоянии от геометрии
ST_Centroid() - Вернуть центроид как точку
ST_Contains() - Содержит ли одна геометрия другую
ST_Crosses() - Пересекает ли одна геометрия другую
ST_Difference() - Разница между заданными точками возврата для двух геометрий
ST_Dimension() - Размерность геометрии
ST_Disjoint() - Не пересекается ли одна геометрия с другой
ST_Distance() - Расстояние одной геометрии от другой
ST_EndPoint() - Конечная точка LineString
ST_Envelope() - Вернуть MBR геометрии
ST_Equals() - Равна ли одна геометрия другой
ST_ExteriorRing() - Вернуть внешнее кольцо многоугольника
ST_GeomCollFromText(), ST_GeometryCollectionFromText(),ST_GeomCollFromTxt() - Вернуть сбор геометрии из WKT
ST_GeomCollFromWKB(), ST_GeometryCollectionFromWKB() - Вернуть сбор геометрии из WKB
ST_GeometryN() - Вернуть N-ю геометрию из коллекции геометрии
ST_GeometryType() - Вернуть имя типа геометрии
ST_GeomFromText(), ST_GeometryFromText() - Вернуть геометрию из WKT
ST_GeomFromWKB(), ST_GeometryFromWKB() - Вернуть геометрию из WKB
ST_InteriorRingN() - Вернуть N-е внутреннее кольцо Многоугольника
ST_Intersection() - Возвратная точка устанавливает пересечение двух геометрий
ST_Intersects() - Пересекается ли одна геометрия с другой
ST_IsClosed() - Является ли геометрия замкнутой и простой
ST_IsEmpty() - Является ли геометрия пустой

ST_IsSimple() - Простая ли геометрия
Возвращает 1, если значение геометрии g не имеет аномальных геометрических точек, таких как самопересечение или самокасание. ST_IsSimple()возвращает 0, если аргумент непростой, и NULLесли аргумент простой NULL

ST_LineFromText(), ST_LineStringFromText() - Построить LineString из WKT
ST_LineFromWKB(), ST_LineStringFromWKB() - Построить LineString из WKB
ST_NumGeometries() - Возвращает количество фигур в геометрии
ST_NumInteriorRing(), ST_NumInteriorRings() - Вернуть количество внутренних колец в многоугольнике
ST_NumPoints() - Вернуть количество точек в LineString
ST_Overlaps() - Накладывается ли одна геометрия на другую
ST_PointFromText() - Построить точку из WKT
ST_PointFromWKB() - Построить точку из WKB
ST_PointN() - Вернуть N-ю точку из LineString
ST_PolyFromText(), ST_PolygonFromText() - Построить многоугольник из WKT
ST_PolyFromWKB(), ST_PolygonFromWKB() - Построить многоугольник из WKB
ST_SRID() - Вернуть идентификатор системы пространственной привязки для геометрии
ST_StartPoint() - Начальная точка LineString
ST_SymDifference() - Точка возврата задает симметричную разность двух геометрий

ST_Touches() - Касается ли одна геометрия другой
Две геометрии соприкасаются в пространстве, если их внутренности не пересекаются, но граница одной из геометрий пересекает границу или внутреннюю часть другой. Эта функция возвращает 1, если g1 касается g2, иначе 0.

ST_Union() - Объединение множества точек двух геометрий

SET @g1 = ST_GeomFromText('LineString(1 1, 3 3)');
SET @g2 = ST_GeomFromText('LineString(1 3, 3 1)');
SELECT ST_AsText(ST_Union(@g1, @g2));
+--------------------------------------+
| ST_AsText(ST_Union(@g1, @g2))        |
+--------------------------------------+
| MULTILINESTRING((1 1,3 3),(3 1,1 3)) |
+--------------------------------------+

ST_Within() - Находится ли одна геометрия внутри другой
ST_X() - Вернуть координату X точки
ST_Y() - Вернуть координату Y точки
StartPoint() - Начальная точка LineString
Touches() - Касается ли одна геометрия другой
Within() - Находится ли MBR одной геометрии в MBR другой
X() - Вернуть координату X точки
Y() - Вернуть координату Y точки

Если нет поддержки поля типа POINT

Простейший поиск без использования поля типа point:

SELECT *, (ABS(lon-37.48488200)+ABS(lat-55.78553900)) as distance
    FROM `geo`
    ORDER BY `distance`

Более сложный, учитывающий выпуклость земли:

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance`
FROM `geo` HAVING `distance`<=’10’ ORDER BY `distance`

Найти ближайшую дорогу (линию) к точке (отелю), используя пространственные расширения MySQL

CREATE TABLE IF NOT EXISTS `Hotels` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` text,
    `coordinate` point NOT NULL,
    PRIMARY KEY (`id`),
    SPATIAL KEY `coordinate` (`coordinate`),
)

CREATE TABLE IF NOT EXISTS `Roads` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` text,
    `route` linestring NOT NULL,
    PRIMARY KEY (`id`),
    SPATIAL KEY `coordinate` (`route`),
)

Задача: найти N ближайших дорог от точки P. Расстояние определяется наименьшим перпендикулярным расстоянием между отрезком в дороге до точки, как показано выше.

В базе данных можно создать две функции:

  • distance: это даст вам расстояние между двумя точками.
  • DistanceFromLine: Здесь расстояние будет рассчитываться из каждой точки в строке и даст вам кратчайшее расстояние.

Сравните расстояние между точками и линиями и выберите самый короткий.


Вот функция Distance для старой версии MySQL:

delimiter //
CREATE FUNCTION distance (latA double, lonA double, latB double, LonB double)
RETURNS double DETERMINISTIC
BEGIN
    SET @RlatA = radians(latA);
    SET @RlonA = radians(lonA);
    SET @RlatB = radians(latB);
    SET @RlonB = radians(LonB);
    SET @deltaLat = @RlatA - @RlatB;
    SET @deltaLon = @RlonA - @RlonB;
    SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) +
    COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2);
    RETURN 2 * ASIN(SQRT(@d)) * 637101;
END//

Здесь функция DistanceFromLine:

DROP function IF EXISTS `DistanceFromLine`;
delimiter //
CREATE FUNCTION `DistanceFromLine`(
route LINESTRING, point1 POINT
) RETURNS INT DETERMINISTIC
BEGIN
    DECLARE a INT Default 0 ;
    DECLARE minDistance INT Default 0;
    DECLARE currentDistance INT Default 0;
    DECLARE currentpoint point ;
    DECLARE size INT Default 0 ;
    SET size =  NumPoints(route);
    simple_loop: LOOP
        SET a = a+1;
        SET currentpoint = PointN(route,a);
        SET currentDistance = Distance(X(point1), Y(point1),
        X(currentpoint),Y(currentpoint));

        IF a = 1 THEN
            SET minDistance = currentDistance;
        END IF;

        IF currentDistance < minDistance THEN
          SET minDistance = currentDistance;
        END IF;
        IF a=size THEN
          LEAVE simple_loop;
        END IF;
    END LOOP simple_loop;
    RETURN (minDistance);
END//

Альтернативный вариант с использованием ST_Distance_Sphere (MySQL 5.7.6+):

DROP function IF EXISTS `DistanceFromLine`;
delimiter //
CREATE FUNCTION `DistanceFromLine`(
route LINESTRING, point1 POINT
) RETURNS INT DETERMINISTIC
BEGIN
    DECLARE a INT Default 0 ;
    DECLARE minDistance INT Default 0;
    DECLARE currentDistance INT Default 0;
    DECLARE currentpoint point ;
    DECLARE size INT Default 0 ;
    SET size =  ST_NumPoints(route);
    simple_loop: LOOP
        SET a = a+1;
        SET currentpoint = ST_PointN(route,a);
        SET currentDistance = ST_Distance_Sphere(point1,currentpoint);

        IF a = 1 THEN
            SET minDistance = currentDistance;
        END IF;

        IF currentDistance < minDistance THEN
            SET minDistance = currentDistance;
        END IF;
        IF a=size THEN
            LEAVE simple_loop;
        END IF;
    END LOOP simple_loop;
    RETURN (minDistance);
END//


.